mysql get all table privileges for a user

user_795374 0
Viewed 209

I have the following query that I run against a postgresql DB whose intent is to return all the tables in a given schema for which the user does NOT have SELECT privileges

select table_name from information_schema.tables 
where table_schema=myschema 
except 
SELECT DISTINCT table_name FROM information_schema.table_privileges 
WHERE has_table_privilege(table_schema || '.' || table_name, 'SELECT');

How would I construct a similar query for use against a mysql database? mysql doesn't have a has_table_privilege() function

mysql sql
1 Answers

This will work in MySQL:

select table_name from information_schema.tables where table_schema='myschema'

A table is not visible to your user, i.e., it doesn't even show up as a row in information_schema.tables, if you don't have SELECT privilege on the table.

But the more general answer is how can one get a list of tables that are visible to some other arbitrary user, assuming one is querying from a superuser session and can see all tables.

For that, you'd have to join to the privileges tables mysql.user, mysql.db, and mysql.tables_priv:

select i.table_schema, i.table_name from information_schema.tables i
left join mysql.user u on u.user='testuser' and u.select_priv = 'Y'
left join mysql.db d on d.user = 'testuser' and d.db = i.table_schema and d.select_priv = 'Y'
left join mysql.tables_priv t on t.user = 'testuser' and t.db = i.table_schema and t.table_name = i.table_name and find_in_set('Select', t.table_priv)
where coalesce(u.user, d.user, t.user) is not null
Bill_Karwin_208600
Related