How can I restrict a MySQL user to a particular tables

Question

How can I restrict a user_account in MySQL database to a particular tables. Ex:

UserName: RestrictedUser
DatabaseName: db_Payroll 
TableName: 
  tb_Employees
  tb_Users
  tb_Payroll_YYMMDD
  tb_Payroll_Processed

I want to restrict "RestrictedUser" to tb_Users and tb_Employees only and the rest of the tables of db_Payroll that will be created for future use is granted to have access.

score 41 · Accepted Answer · edited Oct 18 '20 at 09:06

41

Assuming the user has no current privileges, you can do the following

GRANT SELECT, INSERT, DELETE ON db_Payroll.tb_Users TO RestrictedUser@'%'
GRANT SELECT, INSERT, DELETE ON db_Payroll.tb_Employees TO RestrictedUser@'%'

Depending on exactly which privileges you wish to grant the user, you can change SELECT, INSERT, DELETE to something else, e.g. ALL PRIVILEGES.

Afterwards, remember to flush the privileges so they become effective by running

FLUSH PRIVILEGES;

edited Oct 18 '20 at 09:06

Robin Lavallée

65
1
8

answered Mar 20 '12 at 02:36

kba

19,333
5
62
89

4

You do not need to `FLUSH PRIVILEGES` after using `GRANT` or any other account modification statement, only if you've modified the grant tables directly with DML. See http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html – Jeremy Smyth Jan 23 '13 at 13:29

score 5 · Answer 2 · answered Mar 20 '12 at 02:38

5

You can grant access to individual tables by running:

GRANT ALL ON db_Payroll.tb_Users to RestrictedUser@RestrictedHostName;

And similarly for other tables. Use a list of operations instead of ALL if appropriate.

You cannot grant access to individual tables which do not exist yet without granting access to all tables.

answered Mar 20 '12 at 02:38

"You cannot grant access to individual tables which do not exist yet without granting access to all tables." that is my main problem, db_Payroll is used by payroll application, I am creating a webinterface using a php and i want to secure the connection without writing the connection settings what were used by payroll application. – aintgel Mar 20 '12 at 04:04
9

So decide what your tables will be, and grant access to the appropriate ones. If you're thinking of a design that involves creating tables at runtime, STOP! Don't do that. – Mar 20 '12 at 04:14

score 0 · Answer 3 · answered Feb 09 '21 at 20:13

Assuming the user has no current privileges, if you have a lot of tables and you only want to give the user access to a few of those tables, the simplest work-around I know of is using a technique I personally refer to as QueryCeption™ (Query Within a Query):

SELECT GROUP_CONCAT(CONCAT('grant select on `db_Payroll`.', table_name, ' to `RestrictedUser`@`%`') SEPARATOR ';
') from information_schema.tables where table_schema = 'db_Payroll' and 
table_name not in ('TABLE-YOU-WANT-TO-RESTRICT-1', 'TABLE-YOU-WANT-TO-RESTRICT-2','TABLE-YOU-WANT-TO-RESTRICT-3');

This will output a text field that you can copy and paste into your editor. This particular example will grant SELECT privileges to all tables that are not within the restricted table array for that user.

How can I restrict a MySQL user to a particular tables

3 Answers3

Linked