MySQL Table and Column Level Permissions

Granting Table Level Permissions

You can create a user with table level permissions in MySQL by performing the following.

1.  Connect to mysql as a user with the Create_user_priv and Grant_priv.  You can determine which users have these privileges by running the following query.  Your user will already need the SELECT privilege on mysql.user to run the query.

SELECT User, Host, Super_priv, Create_user_priv, Grant_priv from mysql.user WHERE Create_user_priv = 'Y' AND Grant_Priv = 'Y'; 

2.  Run the following query to generate the GRANT statements for your restricted user.  Replace 'mydatabase', 'myuser', and 'myhost', with the specific information for your database.  

Note that the '' surrounding myuser and mypassword are two single quotes, not a double quote. The characters surrounding myhost and ,TABLE_NAME, are backticks, the key located under the escape key on your keyboard.

SELECT CONCAT('GRANT SELECT, SHOW VIEW ON mydatabase.`', TABLE_NAME, '` to ''myuser''@`myhost`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydatabase';

For example, if you wanted to connect the user 'chartio_read_only' to your 'Reports' database using the chartio_connect client you would run the following.

SELECT CONCAT('GRANT SELECT, SHOW VIEW ON Reports.`', TABLE_NAME, '` to ''chartio_read_only''@`localhost`;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'Reports';

If you wanted to connect the user 'chartio_direct_connect' to your 'Analytics' database using a direct connection from Chartio's servers you would run the following.

SELECT CONCAT('GRANT SELECT, SHOW VIEW ON Analytics.`', TABLE_NAME, '` to ''chartio_direct_connect''@`52.6.1.1`;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'Analytics';

3.  The query should results similar to the following.

GRANT SELECT, SHOW VIEW ON mydatabase.`Activity` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Marketing` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Operations` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Payments` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Plans` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Services` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Subscriptions` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Users` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Visitors` to 'myuser'@`myhost`;

4.  Now select the statements for only the tables  you would like to grant access to and run those queries.  For example, if I only wanted to grant access to the Users and Visitors table I would run.

GRANT SELECT, SHOW VIEW ON mydatabase.`Users` to 'myuser'@`myhost`;
GRANT SELECT, SHOW VIEW ON mydatabase.`Visitors` to 'myuser'@`myhost`;

5.  Give the user a secure password.

SET PASSWORD FOR 'chartio_read_only'@`localhost` = PASSWORD('top$secret');

or

SET PASSWORD FOR 'chartio_direct_connect'@`52.6.1.1` = PASSWORD('top$secret');

6.  Now you can safely access your database with this user and be assured it only has permissions to the specified tables.

Granting Column Level Permissions

The procedure for granting column level permissions on a specific table is very similar to granting table level permissions.

1.  Generate the GRANT statements for column level permissions using the following query:

SELECTCONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON mydatabase.`', TABLE_NAME, '` to ''myuser''@`myhost`;') 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'mydatabase' AND TABLE_NAME = 'mytable';

For example, if you wanted to connect the user 'chartio_read_only' to specific columns in the 'Users' table of the 'Reports' database using the chartio_connect client you would run the following.

SELECTCONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON Reports.`', TABLE_NAME, '` to ''chartio_read_only''@`localhost`;') 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'Reports' AND TABLE_NAME = 'Users';

2.  The query should results similar to the following.

GRANT SELECT (`User_ID`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Campaign_ID`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Created_Date`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Company`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`City`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`State`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Zip`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Phone_Number`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Credit_Card`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;

3.  Now select only the statements for the columns you would like to grant access to and run those queries.  For example, if I only wanted to grant access to the 'User_ID' and 'Company' columns I would run.

GRANT SELECT (`User_ID`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;
GRANT SELECT (`Company`), SHOW VIEW ON Reports.`Users` to 'chartio_read_only'@`localhost`;

4.  Give the user a secure password.

SET PASSWORD FOR 'chartio_read_only'@`localhost` = PASSWORD('top$secret');

For more information consult the MySQL documentation.