mysql禁止用户访问一张表,如何将MySQL用户限制为特定表

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.

解决方案

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 to they become effective.

FLUSH PRIVILEGES;