2

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.

1 Answer 1

2

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 !!!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .