Managing Permissions

Permission Operations

SHOW GRANTS is supported in versions in 3.0.2 or later. Other functions are available in versions 2.4.1.4 or later.

CREATE USER

Syntax:

CREATE USER username IDENTIFIED BY 'auth#string'

Example: Creating an account (username: Jenny; password: xxxxxx)

CREATE USER Jenny IDENTIFIED BY 'xxxxxx';

Each username and password must meet the corresponding requirements.

DROP USER

Syntax:

DROP USER username

Example: Removing user Jenny

DROP USER Jenny;

SET PASSWORD

Syntax:

SET PASSWORD FOR 'username'@'%' = 'auth_string'

To be compatible with the MySQL syntax, the username must be in the format of 'username'@' %'.

Example: Changing the password of Jenny to xxxxxx

SET PASSWORD FOR 'Jenny'@'%' = 'xxxxxx'

GRANT

GRANT    
   priv_type[, priv_type] ...     
   ON priv_level     
   TO user [auth_option] 
   priv_level: {    
       | *.*    
       | db_name.*    
       | db_name.tbl_name      
       | tbl_name}
   ​auth_option: {        
       IDENTIFIED BY 'auth#string'
}

If a GRANT statement provides no accounts and does not specify IDENTIFIED BY, a message No account found will be returned. If IDENTIFIED BY is specified, an account will be created accordingly and permissions will be granted to it.

GRANT ALL [PRIVILEGES] can be used to assign table-, user-, and database-level permissions.

Example 1: Create a user-level account with all permissions. The username is Mike.

Method 1: Create an account and then grant permissions to it.

CREATE USER Mike IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON *.* to Mike;

Method 2: Execute one SQL statement to create an account and grant it permissions.

GRANT SELECT, INSERT ON *.* to Mike IDENTIFIED BY 'password';

Example 2: Create a database-level account with all permissions. Create account david in database testdb and grant the SELECT permissions of database testdb to the account.

Method 1: Create an account and then grant permissions to it.

CREATE USER david IDENTIFIED BY 'password';
GRANT SELECT ON testdb.* to david;

Method 2: Execute one SQL statement to create an account and grant it permissions.

GRANT SELECT ON testdb.* to david IDENTIFIED BY 'password';

Example 3: Create a table-level account with all permissions. Create account hanson in database testdb and grant all permissions of table testdb.employees to the account.

GRANT  ALL PRIVILEGES ON testdb.employees to hanson IDENTIFIED BY 'password';

REVOKE

Syntax:

REVOKE    
    priv_type [, priv_type] ...   
    ON priv_level FROM user​;

Example: Deleting CREATE, DROP, and INDEX permissions of user hanson on table testdb.emp.

REVOKE CREATE,DROP,INDEX ON testdb.emp FROM hanson;

REVOKE can delete actions at each permission level of an account. The permission level is specified by priv_level.

SHOW GRANTS

Syntax:

SHOW GRANTS FOR user;

Example 1: Querying user permissions with any of the following statements:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Example 2: Querying other permissions. This operation can be performed only when the current user can grant user-level permissions.

mysql> show grants for david;
+-----------------------------+
|Grants for david            |
+-----------------------------+
|GRANT USAGE ON *.* TO david |
+-----------------------------+
1 row in set (0.00 sec)