Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to grant a user permission to select,insert,update,delete on a few tables in the prod database. However, I find it time consuming to write each grant statement out per table. Is it possible to use a wildcard?

There are 300 tables, the user only needs access to 18 of them. The tables the user needs access to are prefixed 'vs_'.

Can I do a grant select,insert,update,delete on prod.vs_\* ? I know prod.\* is possible but wasn't sure on the prefix of a table.

share|improve this question
    
You could just try it and see if it works. –  ypercube May 16 at 16:37
    
Is this for Windows or Linux (please say Linux because Windows always takes more work) ? –  RolandoMySQLDBA May 16 at 18:56

2 Answers 2

No, the wildcard for table names can only be *, and does not permit other characters or patterns.

You could generate the 18 GRANT statements you need:

SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%';

Capture the output of that query, and then run it as a series of statements.

share|improve this answer
    
+1 for getting to this faster !!! –  RolandoMySQLDBA May 16 at 19:46

Your probably need to use the INFORMATION_SCHEMA to help you make the GRANTS on all vs tables.

SAMPLE DATA

I will create a database edwards with 3 tables for each family member

DROP DATABASE IF EXISTS edwards;
CREATE DATABASE edwards;
USE edwards
CREATE TABLE rolando_tb1
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, num INT NOT NULL) ENGINE=MyISAM;
CREATE TABLE rolando_tb2 LIKE rolando_tb1;
CREATE TABLE rolando_tb3 LIKE rolando_tb1;
CREATE TABLE pamela_tb1 LIKE rolando_tb1;
CREATE TABLE pamela_tb2 LIKE rolando_tb1;
CREATE TABLE pamela_tb3 LIKE rolando_tb1;
CREATE TABLE dominique_tb1 LIKE rolando_tb1;
CREATE TABLE dominique_tb2 LIKE rolando_tb1;
CREATE TABLE dominique_tb3 LIKE rolando_tb1;
CREATE TABLE diamond_tb1 LIKE rolando_tb1;
CREATE TABLE diamond_tb2 LIKE rolando_tb1;
CREATE TABLE diamond_tb3 LIKE rolando_tb1;
SHOW TABLES;

Here are the commands executed

mysql> DROP DATABASE IF EXISTS edwards;
Query OK, 12 rows affected (0.08 sec)

mysql> CREATE DATABASE edwards;
Query OK, 1 row affected (0.00 sec)

mysql> USE edwards
Database changed
mysql> CREATE TABLE rolando_tb1
    -> (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, num INT NOT NULL) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE rolando_tb2 LIKE rolando_tb1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE rolando_tb3 LIKE rolando_tb1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE pamela_tb1 LIKE rolando_tb1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE pamela_tb2 LIKE rolando_tb1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE pamela_tb3 LIKE rolando_tb1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE dominique_tb1 LIKE rolando_tb1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE dominique_tb2 LIKE rolando_tb1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE dominique_tb3 LIKE rolando_tb1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE diamond_tb1 LIKE rolando_tb1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE diamond_tb2 LIKE rolando_tb1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE diamond_tb3 LIKE rolando_tb1;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_edwards |
+-------------------+
| diamond_tb1       |
| diamond_tb2       |
| diamond_tb3       |
| dominique_tb1     |
| dominique_tb2     |
| dominique_tb3     |
| pamela_tb1        |
| pamela_tb2        |
| pamela_tb3        |
| rolando_tb1       |
| rolando_tb2       |
| rolando_tb3       |
+-------------------+
12 rows in set (0.00 sec)

mysql>

SAMPLE USER

Let's create a use called pam@localhost with the password pam

mysql> GRANT USAGE ON *.* TO pam@localhost IDENTIFIED BY 'pam';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR pam@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for pam@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pam'@'localhost' IDENTIFIED BY PASSWORD '*F925CA006C127B610C43AB06E16F92EF8712F90B' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Using the INFORMATION_SCHEMA (@BillKarwin gets +1 for being faster and concise), let's scult the grants for the three tables that start with pamela_

Using this query

SELECT CONCAT('GRANT SELECT,INSERT ON ',db,'.',tb,' TO pam@localhost;') grant_command
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables
WHERE table_schema='edwards' AND table_name LIKE 'pamela\_%') A;

Here is the output

mysql> SELECT CONCAT('GRANT SELECT,INSERT ON ',db,'.',tb,' TO pam@localhost;') grant_command
    -> FROM (SELECT table_schema db,table_name tb FROM information_schema.tables
    -> WHERE table_schema='edwards' AND table_name LIKE 'pamela\_%') A;
+-------------------------------------------------------------+
| grant_command                                               |
+-------------------------------------------------------------+
| GRANT SELECT,INSERT ON edwards.pamela_tb1 TO pam@localhost; |
| GRANT SELECT,INSERT ON edwards.pamela_tb2 TO pam@localhost; |
| GRANT SELECT,INSERT ON edwards.pamela_tb3 TO pam@localhost; |
+-------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

I'll copy and paste them. Then I'll show the grants

mysql> GRANT SELECT,INSERT ON edwards.pamela_tb1 TO pam@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON edwards.pamela_tb2 TO pam@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON edwards.pamela_tb3 TO pam@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR pam@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for pam@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pam'@'localhost' IDENTIFIED BY PASSWORD '*F925CA006C127B610C43AB06E16F92EF8712F90B' |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb1` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb3` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb2` TO 'pam'@'localhost'                                        |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

LOGIN AND TEST USER

C:\windows\system32>mysql -upam -ppam
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------------------------------+
| Grants for pam@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pam'@'localhost' IDENTIFIED BY PASSWORD '*F925CA006C127B610C43AB06E16F92EF8712F90B' |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb1` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb3` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb2` TO 'pam'@'localhost'                                        |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> use edwards
Database changed
mysql> select * from pamela_tb2;
Empty set (0.01 sec)

mysql> select * from rolando_tb1;
ERROR 1142 (42000): SELECT command denied to user 'pam'@'localhost' for table 'rolando_tb1'
mysql> select * from pamela_tb3;
Empty set (0.01 sec)

mysql> insert into pamela_db1 (num) values (11),(22),(33);
ERROR 1142 (42000): INSERT command denied to user 'pam'@'localhost' for table 'pamela_db1'
mysql> insert into pamela_tb1 (num) values (11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into rolando_tb1 (num) values (11),(22),(33);
ERROR 1142 (42000): INSERT command denied to user 'pam'@'localhost' for table 'rolando_tb1'
mysql> select * from pamela_tb1;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |  33 |
+----+-----+
3 rows in set (0.00 sec)

mysql> delete from pamela_tb1 where id = 2;
ERROR 1142 (42000): DELETE command denied to user 'pam'@'localhost' for table 'pamela_tb1'
mysql>

Looks like it works for pam having SELECT and INSERT

In your case, @BillKarwin already said what to do in his answer.

If you like my answer, accept Bill's answer since here came up with the concept before me.

share|improve this answer
    
+1 for thoroughness! –  Bill Karwin May 16 at 23:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.