Long time reader, first time poster. Here we go:
==Specific question:
Does there exist in MySQL a privilege configuration which will allow an account
to issue `SHOW MASTER STATUS` and `SHOW SLAVE STATUS`, but will disallow the
running of any other commands (such as `KILL`)?
==Efforts thus far:
I had noticed the 'Process_priv' field in mysql.user, so I created an account to
which I delegated only that privilege. Upon testing, however, I found that in
addition to being able to view processes, I was also able to kill processes.
This is an unacceptable level of access for my particular exercise (but which
is evidently expected behaviour for that privilege). [1][2] I am hopeful that
someone out there knows of a clever combination of privileges which will yield
the desired behaviour.
==Overall goal:
We are about to deploy into production a pair of database servers with
bi-directional replication. One of the machines has been in production for
some time in an unreplicated manner, but the objective of the new architecture
we're rolling out is to have a hot standby system in the event the primary
fails. We would also like to be able to swap the primary/secondary roles at
will so that we can take each system down individually for patching without
impacting the availability of the service; ideally this would be done via a
`cron` or `at` job. The goal is not to effect a load balancing configuration
(which is unnecessary at this point), but "merely" to achieve near instantaneous
failover ability. Failing over manually works fine, but I dislike having to
wake up 04:00 to do things like swap around database servers. :-)
To this end, I plan to develop a script to automate the failover process
so that the switch can be made safely without manual intervention by a MySQL
DBA. In order to determine whether it is "safe", I planned to have the
(hypothetical) script compare the results of `SHOW MASTER STATUS` and `SHOW
SLAVE STATUS` to ensure that the systems *are* in fact properly synched up
before the failover is executed. [3] In order to accomplish this without
embedding passwords in random scripts, I'd like to be able to create a
passwordless MySQL account which would be able to run these SHOW commands, but
which (in the interest of security) will have no other privileges, since this
account will not have the benefit of a password. [4] The ability to kill
processes in the database violates this fundamental requirement, so simply
delegating 'Process_priv' is not an attractive option, though so far it seems
to be the only one.
==Environment:
Solaris 8/SPARC (64-bit)
MySQL 3.23.57
gcc 3.2
GNU make 3.79
Standard compilation flags:
CC=gcc
CXX=gcc
CFLAGS="-O3 -fno-omit-frame-pointer -mcpu=v8 -Wa,-xarch=v8plusa"
CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors \
-fno-exceptions -fno-rtti -mcpu=v8 -Wa,-xarch=v8plusa"
./configure --prefix=/usr/local/mysql-3.23.57 \
--datadir=/var/opt/mysql \
--localstatedir=/var/opt/mysql \
--enable-assembler \
--enable-local-infile \
--with-innodb \
--with-mysqld-user=mysql\
--with-gnu-ld=/usr/local/bin/ld
Note: I compile MySQL just once then push it out to all of our MySQL database
servers, hence the InnoDB support, though on the replicated systems in question
we 'skip-innodb'. I don't expect that any of these details actually have any
bearing whatsoever on the question being discussed here today, but I thought
I'd provide this information in the interest of full disclosure.
==Final plea:
Any ideas? I would imagine that people who have already deployed replicated
MySQL databases have some sort of general monitoring mechanism which they use
to ensure that replication is working properly amongst their servers at any
given time. Would any of you care to divulge such trade secrets?
==Footnotes:
[1] After observing this behaviour, I dusted off my copy of the ORA _MySQL
Reference Manual_ and looked up this privilege. In section 4.2.2, the reader
is warned not to freely delegate this privilege because it will allow someone
to spy on the activity of other users. It is not until section 4.2.7 that the
reader is told that 'Process_priv' includes `kill` ability. Should there be a
second edition of this book, I'd suggest also noting the `kill` ramification of
'Process_priv' upfront in section 4.2.2. I personally consider the ability to
arbitrarily terminate processes in the database to be much more of a security
concern than simply being able to observe what queries other users may be
running.
[2] Some time back I gave up on using http://www.mysql.com/doc/en/index.html
as a reference because I grew weary of time and again thinking that I had
found the answer to my question of the moment, only to discover that the
described command or switch is only available on MySQL 4.x. The "About"
section of the online manual states "Functional changes are always indicated
with reference to the version, so this manual is also suitable if you are using
an older version of the MySQL software"; however, I've never noticed any such
indications on the manual pages as returned by the search engine. Perhaps I've
just never looked closely enough and someone will be kind enough to point out
precisely where on the web pages these notations appear.
[3] Specifically, the web application servers comprising the front end of the
overall system are to be reconfigured to point to an extra IP address which
will be passed back and forth between the two database servers by means of
upping/downing a logical network interface on each machine as appropriate.
[4] We already take a similar approach to shutting down our databases. We set
root passwords on our MySQL installations, then create a passwordless account
with only the 'Shutdown_priv' privilege assigned to it. The rc script runs
`mysqladmin` as this user to shut MySQL down cleanly when the systems are
rebooted.
TIA,
-C
--
#!#!#!#!#!#!#!# !#!#!#!#!#!#!#! #!#!#!#!#!#!#!# !#!#!#!#!#!#!#! #!#!#!#!
Chris Callahan - UCD Information Technology - cr********@ucda vis.edu
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~
"[T]he most important point being debated here today is whether
governments have the right to intrude on the privacy of their
citizens in such a way that citizens ultimately cannot have a
private conversation. I should be able to whisper in your ear,
even if your ear is a thousand kilometers away." --Philip Zimmerman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
