Mysql – Is it possible to specify MySQL global privileges for a specific table in all databases

MySQLpermissions

We have several databases with the same structure running on our MySQL server. Now we need an user which requires INSERT privileges on all databases, but on a specific table.

Is it possible?

I tried the grant below, but no luck:

grant INSERT on *.mytable to 'myuser'@'%'

As you can see, I'm trying to avoid the creation of one user per database. If the approach above is not possible, is there any other way to achieve this goal?

I know that the opposite is possible (create an user which has privileges on all tables of a given database or all tables in all databases). I can achieve it by doing:

grant on dbname.* to 'myuser'@'%'

grant on *.* to 'myuser'@'%'

The global privileges are described here: http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-global-privileges, but I couldn't find such option there.

Best Answer

You may have to hack it using mysql schema tables to make the grants you need:

First of all, here is the description of mysql.tables_priv

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

You may have to gather all the databases that have mytable and append the string '.mytable'

SELECT CONCAT(table_schema,'.',table_name) my_table
FROM information_schema.tables WHERE table_name = 'mytable';

Then, take that list and create the proper grant for that list of tables for each specific user

SELECT CONCAT('GRANT ALL PRIVILEGES ON ',my_table,' TO ',userhost,';') SQLGrantCommand
FROM
(
    SELECT CONCAT('''',user,'''.''',host,'''') userhost
    FROM mysql.user WHERE user NOT IN ('','root')
) user_list,
(
    SELECT CONCAT(table_schema,'.',table_name) my_table
    FROM information_schema.tables WHERE table_name = 'mytable';
) table_list;

You should take that query and export it to a text file called /root/GlobalTableGrants.sql

mysql -uroot -A --skip-column-names -e"SELECT CONCAT('GRANT ALL PRIVILEGES ON ',my_table,' TO ',userhost,';') SQLGrantCommand FROM (SELECT CONCAT('''',user,'''.''',host,'''') userhost FROM mysql.user WHERE user NOT IN ('','root')) user_list,(SELECT CONCAT(table_schema,'.',table_name) my_table FROM information_schema.tables WHERE table_name = 'mytable') table_list" > /root/GlobalTableGrants.sql

Now just login to mysql run that script

mysql> source /root/GlobalTableGrants.sql

After you run the script, do this

SELECT * FROM mysql.tables_priv WHERE table_name = 'mytable'\G

You should see every occurrence of my_table with table level privs enabled.

Give it a Try !!!

Related Topic