[MySQL]:User Account Management
We can create User Accounts in two ways.
- By using CREATE USER or GRANT statements. (Recommended Method)
- By manipulating the mysql grant tables with statements like INSERT and DELETE.
Using CREATE USER and GRANT statements:
Creating a User:
By using CREATE USER statement we can just create users and we cannot grant privileges to the user. To use this statement you should have CREATE USER privilege.
For Single user:
Syntax: CREATE USER <username> IDENTIFIED BY <password> ;
For Multiple user:
Syntax: CREATE USER <username> IDENTIFIED BY <password>, <username> IDENTIFIED BY <password>, <username> IDENTIFIED BY <password>……….. ;
Using GRANT statement we can create users and as well as we can grant privileges to the users. For using this statement you should have GRANT privilege and you should have privileges that you are granting for others.
This statement can be used in many ways. Some examples of them are:
- mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’hostname’ IDENTIFIED BY <password> WITH GRANT OPTION;
The above statement grants all privileges on all databases to specified user name and host name.
If the use name doesn’t exists it create a user and grants privileges and if the user exists the password is also changed.
If you do not want to change the password use the below query
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’hostname’ ;
- mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO ‘username’@’hostname’ IDENTIFIED BY <password>;
The above statement grants SELECT, INSERT, UPDATE privileges on all databases to specified user name and host name.
- mysql> GRANT SELECT, INSERT, UPDATE ON DB1.* TO ‘username’@’hostname’;
The above statement grants SELECT, INSERT, UPDATE privileges on DB1 database to specified user name and host name.
- mysql> GRANT SELECT, INSERT, UPDATE ON DB1.TBL1 TO ‘username’@’hostname’;
The above statement grants SELECT, INSERT, UPDATE privileges on DB1.TBL1 table to specified user name and host name.
- mysql> GRANT ALL PRIVILEGES ON *.* TO ‘username’@’hostname’ WITH
MAX QUERIES PER HOUR 50
MAX UPDATES PER HOUR 15
MAX CONNECTIONS PER HOUR 3
MAX USER CONNECTIONS 2;
The above statement grants all privileges on all databases to specified user name and host name and restricts user resources. By the above query the user can only execute 50 queries per hour, 15 update queries per hour, and can connect max 3 times in an hour and the user can have only 2 simultaneous connections.
Renaming a User:
To rename a user you must have global CREATE USER privilege. Renaming a user does not automatically migrate all the objects that the user has created.
For Single user:
Syntax: RENAME USER <old_username> TO <new_username> ;
For Multiple user:
Syntax: RENAME USER <old_username> TO <new_username>, <old_username> TO <new_username>, <old_username> TO <new_username>…….;
Revoking Privileges:
mysql> REVOKE ALL PRIVILEGES FROM <username>;
The above statement revokes all privileges for the specified user.
mysql> REVOKE ALL PRIVILEGES ON DB1.* FROM <username>;
The above statement revokes all privileges on DB1 database for the specified user.
mysql> REVOKE ALL PRIVILEGES ON DB1.TBL1 FROM <username>;
The above statement revokes all privileges on DB1.TBL1 table database for the specified user.
Setting Password:
The below statement is used to set/update user password. In case if the user forgets his password we can use this statement to reset the password.
Syntax: SET PASSWORD FOR ‘<username>’@'<host name>’ = PASSWORD(‘<password>’);
Removing User:
To drop a user you should have global CREATE USER privilege. Dropping a user does not automatically close any opened sessions and it does not automatically delete the objects created by the user.
Syntax: DROP USER username;
IMP: After every DML Command on grant tables we need to issue FLUSH PRIVILEGES Command. Otherwise the permission will not effect until the MySQL Server is restarted.
To execute FLUSH PRIVILEGES you need to have the RELOAD privilege.
Once the MySQL Server is started it will store all the grant tables in the memory. After every GRANT, REVOKE statements it will reload the grant tables. But by using the DML commands it doesn’t reload the tables. This may be the reason DML commands on grant tables are not recommended.
Creating a User:
If you want just to create a user, insert a row in mysql.user table providing values for host and user. For this you need to have a INSERT privilege on mysql.user table.
Syntax: INSERT INTO user (host, user) VALUES (‘<host name>’, ‘<user name>’);
Granting Global Privileges:
To grant all privileges on all the databases insert a row into the mysql.user table.
Syntax: INSERT INTO user (host, user, password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv) VALUES (‘<hostname>’, ‘<username>’, password(‘<password>’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’));
In the above statement if you doesn’t want to give any privileges we can set the flag as ‘N’.
Also we can set below values in the same table.
max_questions –> Defines max queries for the user per hour.
max_updates –> Defines max updates for the user per hour.
max_connections –> Defines max connections for the user per hour.
max_user_connections –> Defines max simultaneous connections by the user .
Granting Database Level Privileges:
If you want to grant privileges on database level we need to insert a row in mysql.db table. For this the user need to have INSERT privilege on mysql.db table.
Before Inserting a row in this table a row in mysql.user table need to be inserted for the respective user with all the privileges as “N”.
Granting Table Level Privileges:
If you want to grant privileges on table level we need to insert a row in mysql.tables_priv table. For this the user need to have INSERT privilege on mysql.tables_priv table.
Before Inserting a row in this table a row in mysql.user table need to be inserted for the respective user with all the privileges as “N”.
Revoking Privileges:
If you want to revoke global privileges for user just set value as ‘N’ in mysql.user table for respective privilege.
If you want to revoke some database level privileges for a user just set value as ‘N’ in mysql.db table for respective privilege or if you want to revoke entire privileges on a particular database just delete the row from mysql.db table for specified user and db.
If you want to revoke some table level privileges for a user update table_priv field on mysql.tables_priv table or if you want to revoke entire privileges on a particular table just delete the row from mysql.tables_priv table for specified user, db and table.
Setting Password:
If you want set/update a password execute the below statement.
Syntax: mysql> UPDATE mysql.user SET password = PASSWORD(‘<password>’);
Removing User:
Deleting a user need to remove rows from three tables.
- Delete a record from mysql.user table.
- Delete records from mysql.db table.
- Delete records from mysql.tables_priv table.
Syntax: mysql> DELETE FROM mysql.user WHERE user = ‘<username>’;
mysql> DELETE FROM mysql.db WHERE user = ‘<username>’;
mysql> DELETE FROM mysql.tables_priv WHERE user = ‘<username>’;
Resetting Administrator Password:
If the administrator forgets the password restart the MySQL Server with –skip-grant-tables option.
By this the MySQL stops loading the grant tables and we can login to the MySQL without any password. There you can update password in mysql.user table and start MySQL Server as usual.
Follow below steps:
[shell]# /etc/init.d/mysqld stop
[shell]# /etc/init.d/mysqld start –skip-grant-tables
[shell]# mysql
mysql> UPDATE mysql.user SET password = PASSWORD(‘new password’) WHERE user = ‘<username>’ AND host = ‘<host name>’;
mysql> \q
[shell]# /etc/init.d/mysqld restart