Grant Privileges to MySQL User accounts (MySql)

  • Posted on: 6 January 2017
  • By: jmu

It is very easy to create a new user in mysql and give access to database. By using GRANT, you can assign what privileges to any user not only new but also existing users. Just make sure you are using the right username and hostname. Recently, I am working on setting up master and slave replication. But I don't want all my master tables and databases backup on my slave database. Therefore, I listed some important grant statements here that you can use. Also, I provided some hints below as well.

Grant

1. Create a new user: 

CREATE USER 'test_user'@'hostname' IDENTIFIED BY 'mypass';

 

2. Show granted Privileges

SHOW GRANTS;

 

3. Grant a database to an user (All Privileges)

GRANT ALL ON db1.* TO 'test_user'@'hostname';

 

4. Grant a database to an user (Part Privileges: SELECT)

GRANT SELECT ON db2.* TO 'test_user'@'hostname';

 

5. Grant a table to an user (All Privileges)

GRANT ALL ON db1.tbl TO 'test_user'@'hostname';

 

6. Grant a column to an user (Part Privileges: SELECT)

GRANT SELECT (col1) ON db1.tbl TO 'test_user'@'hostname';

 

7. Grant multiple databases to an user (All Privileges) -- WE CANNOT GRANT MORE THAN ONE DATABASE TO AN USER BY USING ONLY ONE GRANT STATEMENT

GRANT ALL ON db1.* TO 'test_user'@'hostname';
GRANT ALL ON db2.* TO 'test_user'@'hostname';

 

8. Grant multiple tables to an user (All Privileges) -- SAME AS #6

GRANT ALL ON db1.tb1 TO 'test_user'@'hostname';
GRANT ALL ON db2.tb1 TO 'test_user'@'hostname';

 

9. Grant multiple columns to an user (Part Privileges: SELECT for col1, INSERT for col1 and col2)

GRANT SELECT (col1), INSERT (col1,col2) ON db1.tbl TO 'test_user'@'hostname';

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.