Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I restrict a MySQL user to a particular tables

Tags:

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.

like image 576
aintgel Avatar asked Mar 20 '12 02:03

aintgel


Video Answer


1 Answers

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; 
like image 50
kba Avatar answered Oct 19 '22 11:10

kba