Home / server / Questions / 369264
In Process
Luis Fernando Alen
Asked: 2012-03-14 08:03:18 +0800 CST

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

  • 772

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.

mysql permissions

1 Answers

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

    • 2