Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL statement for privileges management in MySQL
####This is a cheat sheet for mysql syntax, which is mostly about privilege management in mysql.
---------------------------------------------------------------------------------------------------------------------
### List all the user in mysql(with access to the mysql database)
mysql> select user, host, password from mysql.user;
### Create a user with password
mysql> create user 'shady'@'localhost' identified by 'password';
### Create a user without password
mysql> create user 'eric'@'localhost';
-----------------------------------------------------------------------------------------------------------------------
### Change the password for currently logged in account
#### Find out which account you are currently logged in
mysql> select current_user();
mysql> set password = password('new_password');
/*Notice that the password() function in mysql
The PASSWORD() function that computes password hash values and in
the structure of the user table where passwords are stored.
*/
### Change the password for specific user(with corresponding privileges)
mysql> set password for 'eric'@'localhost' = password('new_password');
--------------------------------------------------------------------------------------------------------------------------
#### Grant privilege for the new created user.
### Show the privileges of specific user
mysql> show grants for 'daniel'@'localhost';
### You may see something like:
+---------------------------------------------------------------------------------------------------------------+
| Grants for daniel@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'daniel'@'localhost' IDENTIFIED BY PASSWORD '*3C06A471CB6048FCCCF5DB904D8F5BE49F1C7585' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### Show privileges for the current account that you logged in
mysql> show grants;
### Grant all privileges on one database to specific user
mysql> grant all on databaseName.* to 'daniel'@'localhost';
### Grant specific privileges(such as select, update, insert, delete) on database to specific user
mysql> grant select on databaseName.* to 'daniel'@'localhost';
### Grant specific privileges on table for specific user
mysql> grant select(track_id, time) on music.track to 'daniel'@'localhost';
### Use the with grant option, to make the user have the privilege to pass the privileges that he/she had granted
mysql> grant select on databaseName.* to 'daniel'@'localhost' with grant option;
/* This gives the the user 'daniel'@'localhost' the privilege to grant the select privilege to other user */
---------------------------------------------------------------------------------------------------------------------
### revoke privileges from specific user
#### First, to see which privileges the user has
mysql> show grants for 'daniel'@'localhost'
#### Rebvoke the select privileges from the user
mysql> revoke select on databaseName.* from 'daniel'@'localhost';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment