Grant MySQL table and column 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. 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 specific information for your database.
Note that the quotations surrounding myuser and mypassword are two single quotes, not double. The characters surrounding myhost and ,TABLE_NAME, are backticks (the key is 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 Atlassian Analytics’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 result 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. Select the statements for only the tables you would like to grant access to and run those queries. For example, if we only wanted to grant access to the Users and Visitors table we 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');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 result in something 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. Select only the statements for the columns you would like to grant access to and run those queries. For example, if we only wanted to grant access to the ‘User_ID’ and ‘Company’ columns we 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.