Query to Audit the Permissions/Privileges for a MySQL instances

I found some nice queries on this blog (http://blog.devart.com/how-to-get-a-list-of-permissions-of-mysql-users.html) and I've been using them to do user audits on the MySQL databases I administer.  I decided to UNION all the queries together and also make a few additions (checking if the password is blank, hostname, database version). The result is an easy to read list of all permissions for all MySQL users on an instance. Additionally, I changed the syntax of the queries so that the results could be copied and pasted into a GRANT statement.

Because this super long query is "unioning" the results of the global priv, the database priv, table priv, column priv and routine priv, it might be a little confusing to read at first. The entries that show the password hash are the global entries followed by a "-" where the priv is not applicable. Next you will see database priv (if there any) and a "-" for each non applicable section and so on through tables, column, view, and routines.

If you want to generate a list for all the user on the instance set @username to %. If you only want to look for one user then change @username to that username. When I create users on production that have elevated priv such as for a DBA, I create them a "bang" account and a separate read only account. The DBAs will have a bang account with all priv that looks like '!username'@'10.%' and then a second account with just the ability to browse the database and run SELECT queries that looks like 'username'@'10.%'.  When I run this audit query, if I put 'username' into the value below it will also show me '!username'.


SET @username = '<user name>';
SELECT -- the list of global privileges
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mu.host `host`,
mu.user `user`,
(case when `mu`.`password` ='' then "**Yes**" ELSE 'No' end) as `Empty Password?`,
mu.password as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(mu.Select_priv = 'Y', 'Select, ', ''),
IF(mu.Insert_priv = 'Y', 'Insert, ', ''),
IF(mu.Update_priv = 'Y', 'Update, ', ''),
IF(mu.Delete_priv = 'Y', 'Delete, ', ''),
IF(mu.Create_priv = 'Y', 'Create, ', ''),
IF(mu.Drop_priv = 'Y', 'Drop, ', ''),
IF(mu.Reload_priv = 'Y', 'Reload, ', ''),
IF(mu.Shutdown_priv = 'Y', 'Shutdown, ', ''),
IF(mu.Process_priv = 'Y', 'Process, ', ''),
IF(mu.File_priv = 'Y', 'File, ', ''),
IF(mu.Grant_priv = 'Y', 'Grant, ', ''),
IF(mu.References_priv = 'Y', 'References, ', ''),
IF(mu.Index_priv = 'Y', 'Index, ', ''),
IF(mu.Alter_priv = 'Y', 'Alter, ', ''),
IF(mu.Show_db_priv = 'Y', 'SHOW DATABASES, ', ''),
IF(mu.Super_priv = 'Y', 'Super, ', ''),
IF(mu.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(mu.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(mu.Execute_priv = 'Y', 'Execute, ', ''),
IF(mu.Repl_slave_priv = 'Y', 'REPLICATION SLAVE, ', ''),
IF(mu.Repl_client_priv = 'Y', 'REPLICATION CLIENT, ', ''),
IF(mu.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(mu.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(mu.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(mu.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(mu.Create_user_priv = 'Y', 'CREATE USER, ', ''),
IF(mu.Event_priv = 'Y', 'Event, ', ''),
IF(mu.Trigger_priv = 'Y', 'Trigger, ', '')
)))) AS `Privileges`
FROM
mysql.user mu
WHERE user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for a database
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
md.host `host`,
md.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
md.db `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
TRIM(TRAILING ',' FROM(RTRIM(CONCAT(
IF(md.Select_priv = 'Y', 'Select, ', ''),
IF(md.Insert_priv = 'Y', 'Insert, ', ''),
IF(md.Update_priv = 'Y', 'Update, ', ''),
IF(md.Delete_priv = 'Y', 'Delete, ', ''),
IF(md.Create_priv = 'Y', 'Create, ', ''),
IF(md.Drop_priv = 'Y', 'Drop, ', ''),
IF(md.Grant_priv = 'Y', 'Grant, ', ''),
IF(md.References_priv = 'Y', 'References, ', ''),
IF(md.Index_priv = 'Y', 'Index, ', ''),
IF(md.Alter_priv = 'Y', 'Alter, ', ''),
IF(md.Create_tmp_table_priv = 'Y', 'CREATE TEMPORARY TABLES, ', ''),
IF(md.Lock_tables_priv = 'Y', 'LOCK TABLES, ', ''),
IF(md.Create_view_priv = 'Y', 'CREATE VIEW, ', ''),
IF(md.Show_view_priv = 'Y', 'SHOW VIEW, ', ''),
IF(md.Create_routine_priv = 'Y', 'CREATE ROUTINE, ', ''),
IF(md.Alter_routine_priv = 'Y', 'ALTER ROUTINE, ', ''),
IF(md.Execute_priv = 'Y', 'Execute, ', ''),
IF(md.Event_priv = 'Y', 'Event, ', ''),
IF(md.Trigger_priv = 'Y', 'Trigger, ', '')
))))  AS `Privileges`
FROM
mysql.db md
WHERE user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for tables
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mt.host `host`,
mt.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
user LIKE CONCAT('%', @username)
AND
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for views
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mv.host `host`,
mv.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
CONCAT(mv.Db, '.', mv.Table_name) `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mv
WHERE
user LIKE CONCAT('%', @username)
AND
mv.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for table columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mtc.host `host`,
mtc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
CONCAT(mtc.Db, '.', mtc.Table_name, '.', mtc.Column_name) `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mtc
WHERE
user LIKE CONCAT('%', @username)
AND mtc.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)
UNION
SELECT -- the list of privileges for view columns
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mvc.host `host`,
mvc.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
CONCAT(mvc.Db, '.', mvc.Table_name, '.', mvc.Column_name) `Views Columns`,
'-' as `Procedures`,
'-' as `Functions`,
REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`
FROM
mysql.columns_priv mvc
WHERE
user LIKE CONCAT('%', @username)
AND mvc.Table_name IN
(SELECT
DISTINCT
v.table_name `views`
FROM
information_schema.views AS v
)
UNION
SELECT -- the list of privileges for procedures
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mp.host `host`,
mp.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,
'-' as `Functions`,
REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mp
WHERE
mp.Routine_type = 'PROCEDURE'
AND user LIKE CONCAT('%', @username)
UNION
SELECT -- the list of privileges for functions
@@hostname as server_host,
SUBSTRING(@@version , 1, CHAR_LENGTH(@@version) - 4) as 'database_version',
mf.host `host`,
mf.user `user`,
'-' as `Empty Password?`,
'-' as 'password',
'-' as `Database`,
'-' as `Tables`,
'-' as `Views`,
'-' as `Tables Columns`,
'-' as `Views Columns`,
'-' as `Procedures`,
CONCAT(mf.Db, '.', mf.Routine_name) `Functions`,
REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`
FROM
mysql.procs_priv mf
WHERE
mf.Routine_type = 'FUNCTION'
AND user LIKE CONCAT('%', @username);

Bang Account for MySQL

When I was a Windows admin my team referred to the special user we had for doing admin work as a bang account. This would be a separate account from your normal active directory account. My manager had a bang bang account which allowed him to administer the admins.

When I became a DBA I carried this concept into the creation of users on database systems. I do a lot of Database Engineering work and I'm constantly troubleshooting queries, writing queries, dropping databases, deleting data, etc. Our database accounts are not yet tied into LDAP or PAM or active directory or tied into roles or anything like that yet. They are managed independently of any other systems.

Even with the precautions of colored screens and warnings it is easy to mix up that I'm on a prod systems and not a dev systems and accidentally drop a database that wasn't supposed to be dropped. Because of this I create two accounts on production for DBAs. A bang account that has all priv and a read only account that only has Select, Process, Execute, REPLICATION CLIENT, Trigger. It allows me to browse the database without the risk of me accidentally making changes. If I do need to do admin work or make a change then I log on with the bang account and immediately close the connection when I'm done. I never saved the bang account credentials so that I don't accidentally logon unaware. There have been so many times when I thought I was on a dev system or a test system and issued a DDL change but I was actually on production, since I was logged onto my read only account, no changes were made.

To differentiate a bang account from a read only account I simply prefix them with an exclamation point. Some people have told me this is silliness and creates additional user management overhead. I feel it is worth it. Does anyone else do some thing similar?

I found a MySQL 5.6 bug

I setup a MySQL 5.6 instance and immediately granted table level permissions to a user. I then ran my audit query to confirm that the permissions were added. The query returned zero results. I thought maybe I had done something wrong. I confirmed that the permissions were actually there.

Here is an example of the table level portion of the audit query:

SELECT -- the list of privileges for tables
mt.host `host`,
mt.user `user`,
CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
FROM
mysql.tables_priv mt
WHERE
mt.Table_name IN
(SELECT
DISTINCT
t.table_name `tables`
FROM
information_schema.tables AS t
WHERE
t.table_type IN
('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '') OR
t.table_type <> 'VIEW' AND
t.create_options IS NOT NULL
)

The query has a SUB SELECT that checks to make sure tables with the name of grant actually exist. For some reason doing a sub-select on the information schema does not work on MySQL 5.6. There is a bug already reported for this:

http://bugs.mysql.com/bug.php?id=77191

The work around is to run this:

set optimizer_switch='semijoin=off';

MySQL Tuning methodology?

I really want to have a solid tuning methodology. That is partly why I wrote the post on the 12 Steps to MySQL Query Tuning. Whenever I do internet searches on this topic, I get so many random slide presentations.

For several years I've thought of database tuning as more of an art than a science. I say this because each database installation can be so different. Some database are meant for OLTP while others are meant for OLAP or some are treated like queues. Every application has different usage patterns for how it may or may not stress a database, every database is going to have different data structures, have different hardware resources available, different network constraints, different hosting (virtual, SAN, on-premise), and on an on. You cannot say that what works with one systems is going to work for another system. Not all systems fit easily into a cookie cutter approach where you can use the same settings on each database instance and expect the same results.

While chatting with a Principal MySQL consultant at Pythian, he said he definitely had a tuning methodology and that my belief that database tuning is more of an art with trial an error was not good enough to him.

My tuning approach has been this:

Step 1:
Review the database parameters, compare them against what my "baseline" of recommend parameters is. For MySQL this means review the my.cnf file. If I don't have file system access, I compare what is in the "SHOW GLOBAL VARIABLES". If I find anything obviously out of place then I will see if I can make incremental changes. Sometimes I think a variable needs to be increased and waste time incrementally increasing it when it actually worked better being decreased.

Step 2:
Next I'll review the results of real-time profiling. I'll be looking for low hanging fruit. Are there more threads running on the instance than CPU available? Too many connections being created for this system to handle? If there is replication, is it lagging? Specific queries with poor execution plans? Full table scans, full index scans, poorly written queries? Are the queries ones that were written by humans or generated by an ORM tool? Are tables lacking indexes? Are there specific DELETES or UPDATES causing excessive locking or blocking?

Step 3:
Then I'll review what my trend history tool shows me. That could be Cacti, Zabbix, Ganglia or whatever you use. Is this server experiencing a higher trend than previously? Did I/O significantly increase recently? Was there any change or spike that stands out? If I see something concerning, I'll dig further into that.

Step 4:
If I have access to the  system, I'll look at MySQL wait states, memory usage, events happening, error logs, slow query logs.

Step 5:
If that hasn't proven useful I'll look into the table structures to see if the data model has obvious points of contention. Since I'm usually not an expert in the application that is using this database, I'll go ask the developers, what is the purpose of such and such query, why is it running so frequently, is is really necessary to do such and such? Sometimes the answer is, "We actually don't need to run that query 50,000 times per minute, once every minute would be fine." And then the code or scheduler or whatever is adjusted to fit that new discovery.

I don't have a ton of experience in MySQL command level tools for in depth trace analysis and I haven't had the opportunity or time to go deeper. Up to know, with decent data modeling, good my.cnf parameters, proper memory settings, load balancing with Master-Slave setups, I've been able to achieve "good enough" performance to meet SLAs. If I can get the queries to run less than 100 ms, I'm pretty happy and the business is happy.

Since my contact at Pythian wasn't kind enough to educate me on his own tuning methodology I started poking around for one and came upon this blog entry: http://www.pythian.com/blog/optimizing-your-mysql-tuning-methodology/

It stills seems "artsy" with plenty of trial and error to me.

I like having a list of tips like this but frequently all of these tips have already been applied and I still need a tuning methodology:
http://www.monitis.com/blog/2011/07/12/101-tips-to-mysql-tuning-and-optimization/

I also like this long list from Dual, this is probably the most "procedural based" tuning list I've seen:
http://www.fromdual.com/mysql-performance-tuning-key

What about you? Do you have methodology to share?