Greetings: 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 - [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ "[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/[EMAIL PROTECTED]