Created
July 20, 2015 08:07
-
-
Save shady-robot/fe0faf47f17dec845fad to your computer and use it in GitHub Desktop.
SQL statement for privileges management in MySQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
####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