In MySQL user account information?s are stored in mysql database tables. In this section we will describe you about Create User, Drop User, Grant Syntax, Rename User, Revoke Syntax and Set Password Syntax.

Account Management Statements

In MySQL user account information?s are stored in mysql database tables. In this section we will describe you about Create User, Drop User, Grant Syntax, Rename User, Revoke Syntax and Set Password Syntax. 
CREATE USER Syntax
The general syntax of CREATE USER statement is :
  CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'].....
In MySQL version 5.0.2 include the statement CREATE USER. It is used to create a new MySQL account. But for using this statement you need the global CREATE USER privilege or the INSERT privilege for mysql database. The CREATE USER statement creates a new record for each account in user table of mysql database. But if account is already exists then it occurs the error message. By the IDENTIFIED BY clause we can give the password to account. If you want to specify a password in plain text the does not include the PASSWORD keyword. But when you specify the PASSWORD keyword then password returned as the hashed value by the PASSWORD() function. Each account can be named by using the same format as for GRANT statement like ?root?@?localhost?. But when you define only the username as part of the account name then a hostname part of ?%? is used. Example :

mysql> CREATE USER chandan IDENTIFIED BY 'chandan'; Query OK, 0 rows affected (0.04 sec) mysql> select user from user; +---------+ | user | +---------+ | | | chandan | | | | root | +---------+ 4 rows in set (0.00 sec)

DROP USER Syntax
The general syntax of DROP USER statement is :
  DROP USER user [, user] ...
DROP USER statement is used to remove one or more than MySQL account. But for using this statement you need the global CREATE USER privilege or DELETE privilege. Example :

mysql> DROP USER chandan; Query OK, 0 rows affected (0.69 sec) mysql> select user from user; +------+ | user | +------+ | | | | | root | +------+ 3 rows in set (0.11 sec)

This statement is used to delete only that MySQL accounts which have no privileges and it removes each account only from user table. For removing a MySQL account completely you have to perform the following steps :

• Firstly use SHOW GRANTS statements for determining the account has what type of privileges. 


• Then use REVOKE statement for revoking the privileges that displayed by SHOW GRANTS statement. 


• Use DROP USER statement for removing the account.

DROP USER statement cannot automatically close any open user session. But, if any open session user is dropped then this statement does not effect until the session is closed. After closing the session the user is dropped. 
GRANT Syntax
The general syntax of GRANT statement is:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] {tbl_name | * | *.* | db_name.*} TO user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option] ...]
object_type = TABLE | FUNCTION | PROCEDURE
with_option = GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count
By using GRANT statement we can enable the system administrator for creating MySQL user accounts and for granting the right to from accounts. But for using the GRANT statement you need the GRANT OPTION privilege and you also required the privileges which you are granting. The REVOKE statement is used to relate and enable the administrator for removing the account privileges. But when grant tables hold the privilege rows which contain the mixed case database or the table name and the lower_case_table_name system variable is set to non-zero value then REVOKE statement cannot used for revoking these privileges. 
Privileges can be granted at several levels: 

• Global level 
  Global level privileges are applied to all databases on a given server. These type of privileges are stored in the user table of mysql database. Ex ? GRANT ALL ON *.* and REVOKE ALL ON *.*;
    


• Database level 
  Database level privileges are applied to all objects in a given database. These type of privileges are stored in the db and host tables of the mysql databases. Ex ? GRANT ALL ON database_ name.* and REVOKE ALL ON database_name.*
    


• Table level 
  Table level privileges are applied to all columns on a given table. These type of privileges are stored in the table_priv table of the mysql database. EX ? GRANT ALL ON database_name.table_name and REVOKE ALL ON database_name.table_name.
   


• Column level 
  Column level privileges are applied to single column on a given table. These type of privileges are stored in columns_priv table of mysql database. And at the time of using REVOKE statement you have to specify the same column name that were granted.
   


• Routine level 
  Routine level privileges like CREATE ROUTINE, EXECUTE, ALTER ROUTING and GRANT privileges are applied to stored routines. These type of privileges can be granted at global and database level. Except CREATE ROUTINE, rest of these privileges can be granted at routine level for particular routines and they are stored in the procs_priv table of mysql database.

The object_type clause was included in the version of MySQL5.0.6. This clause can be defined as TABLE, FUNCTION or PROCEDURE when the following object is a table, function or procedure.
priv_type can be specified as any of the following : 

Privilege	Meaning
ALL [PRIVILEGES]	Sets all simple privileges except GRANT OPTION
ALTER	Enables use of ALTER TABLE
ALTER ROUTINE	Enables stored routines to be altered or dropped
CREATE	Enables use of CREATE TABLE
CREATE ROUTINE	Enables creation of stored routines
CREATE TEMPORARY TABLES	Enables use of CREATE TEMPORARY TABLE
CREATE USER	Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW	Enables use of CREATE VIEW
DELETE	Enables use of DELETE
DROP	Enables use of DROP TABLE
EXECUTE	Enables the user to run stored routines
FILE	Enables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX	Enables use of CREATE INDEX and DROP INDEX
INSERT	Enables use of INSERT
LOCK TABLES	Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS	Enables use of SHOW FULL PROCESSLIST
REFERENCES	Not implemented
RELOAD	Enables use of FLUSH
REPLICATION CLIENT	Enables the user to ask where slave or master servers are
REPLICATION SLAVE	Needed for replication slaves (to read binary log events from the master)
SELECT	Enables use of SELECT
SHOW DATABASES	SHOW DATABASES shows all databases
SHOW VIEW	Enables use of SHOW CREATE VIEW
SHUTDOWN	Enables use of mysqladmin shutdown
SUPER	Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, themysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE	Enables use of UPDATE
USAGE	Synonym for ?no privileges?
GRANT OPTION	Enables privileges to be granted

The privileges like PROCESS, FILE, REPLICATION CLIENT, RELOAD, REPLICATION SLAVE, SHUTDOWN, SHOW DATABASES, SUPER privileges are administrative privileges which can only be granted globally. And other privileges can also be granted global or more specific levels. Example for granting the global privilege :

mysql> CREATE USER raj@localhost IDENTIFIED BY 'raj'; Query OK, 0 rows affected (0.00 sec) mysql> select user from user; +---------+ | user | +---------+ | | | chandan | | | | raj | | root | +---------+ 5 rows in set (0.00 sec) mysql> GRANT ALL ON *.* TO raj@localhost; Query OK, 0 rows affected (0.00 sec)

In usernames Mysql does not support wildcards. Anonymous users are defined by inserting in user table of mysql database with User=?? or creating a user account with an empty name with the GRANT statement. By executing the following query of any anonymous users :

mysql> SELECT HOST, USER FROM USER WHERE USER=''; +-----------+------+ | HOST | USER | +-----------+------+ | % | | | localhost | | +-----------+------+ 2 rows in set (0.00 sec)

By using following statement you can delete the local anonymous user account :

mysql> DELETE FROM USER WHERE HOST='localhost' AND User=''; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.06 sec)

The WITH GRANT OPTIONS clause is used to provide the ability to user for providing to other users any privileges. But you have to careful about who is providing you the GRANT OPTION privilege because two users that have the different privileges can be able to join privileges. By the GRANT OPTION you can only assign only those privilege which yourself you have.
The MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, and MAX_CONNECTIONS_PER_HOUR count options is used to limit the total number of queries, updates and logins, a user can perform these between specified one hour period. But if count is 0 then there is no limitation for that user. The MAX_USER_CONNECTIONS count option is used to limit the number of simultaneous connections, which the account can make.
RENAME USER Syntax
The general syntax of RENAME USER statement is:
  RENAME USER old_user TO new_user [, old_user TO new_user] ...
The RENAME USER statement is used to rename the existing MySQL user accounts but for using this statement you need the global CREATE USER privilege or the UPDATE privilege. But if old account does not exists or the new account exists then it occurs the error. Example :

mysql> RENAME USER chandan TO chand; Query OK, 0 rows affected (0.80 sec) mysql> SELECT User FROM User; +-------+ | User | +-------+ | | | chand | | raj | | root | +-------+ 4 rows in set (0.16 sec)

REVOKE Syntax
The general syntax of REVOKE statement is :
  REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] {tbl_name | * | *.* | db_name.*} FROM user [, user] ...
  REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
The REVOKE statement is used to enable the system administrator for revoking the privileges from MySQL accounts but for using this statement you need the GRANT OPTION privilege and you also need the privileges that you are revoking. All level of privileges and allowable priv_type values we have discussed above.
But when grant tables hold the privilege rows which contain the mixed case database or the table name and the lower_case_table_name system variable is set to non-zero value then REVOKE statement cannot used for revoking these privileges. It will be necessary to manipulate the grant tables directly.
By using following statement you can revoke all privileges for the name user. Example :

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM chand; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR chand \G; *************************** 1. row *************************** Grants for chand@%: GRANT USAGE ON *.* TO 'chand'@'%' IDENTIFIED BY PASSWORD '*A 59F8074680E742CC90A8595EFD7D1404FC8ED2F' 1 row in set (0.00 sec)

SET PASSWORD Syntax
The general syntax of SET PASSWORD statement is:
  SET PASSWORD [FOR user] = PASSWORD('some password')
The SET PASSWORD statement is used to assign a password to existing user. If you are not using FOR clause then its set the password for the current user. Any client using non anonymous account and it is connected with the server can change the password for that account. But if you are using FOR clause then it sets the password for a specified account on current server host but for this you must have the UPDATE privilege. Example :

mysql> SELECT User, Password FROM User; +-------+-------------------------------------------+ | User | Password | +-------+-------------------------------------------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | | | | raj | *7A5773507B1A6F85B4954BC90D6FB55416B0DCF8 | | chand | *DD13F1F66054912AB8F82CA33BBDEE9E442582DB | +-------+-------------------------------------------+ 4 rows in set (0.00 sec) mysql> SET PASSWORD FOR chand=PASSWORD('chand2'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT User, Password FROM User; +-------+-------------------------------------------+ | User | Password | +-------+-------------------------------------------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | | | | raj | *7A5773507B1A6F85B4954BC90D6FB55416B0DCF8 | | chand | *8CCD023D22DCD5607CD453A60598EF23B29DCA6B | +-------+-------------------------------------------+ 4 rows in set (0.00 sec)