MySQL User Account Management:
- A MySQL account is defined in terms of a user name and the client host or host from which the user can connect to the server.
- The account also has a password.
- MySQL user names can be up to 16 characters long.
Note: Operating system user names are completely unrelated to MySQL user names. MySQL passwords have nothing to do with passwords for logging in to your operating system.
Warning: The limit on MySQL user name length is hard-coded in the MySQL servers and clients, and trying to circumvent or avoid it by modifying the definitions of the tables in the mysql database does not work.
MySQL account information is stored in the tables of the mysql database
Creating User accounts:
We can create MySQL user accounts in 3 ways:
- By using CREATE USER:
- By using GRANT
- By manipulating the MySQL grant tables directly with statement INSERT.
Note: The preferred method is to use account-creation statements because they are more concise and less error-prone than manipulating the grant tables directly.
Method 1:
CREATE USER [IDENTIFIED BY [PASSWORD] ' password'user[IDENTIFIED BY [PASSWORD] ']]...
The CREATE USER statement was added in MySQL 5.0.2. This statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists.
Example:
mysql> create user ‘monitor’; mysql> SET PASSWORD for ‘monitor’ = PASSWORD(‘monitor123’); mysql> flush privileges;
Altering a user:
mysql> update mysql.user set password=password('monitor123') where user='monitor' and host='localhost';
or
mysql> flush privileges;
Delete a user: To use it, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database.
mysql> delete from mysql.user where user = 'monitor'; or mysql> flush privileges;
Drop a user: The DROP USER statement removes one or more MySQL accounts. To use it, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database.
DROP USER user [, user]...
DROP USER monitor;
The statement removes privilege rows for the account from all grant tables.
To remove a MySQL account completely (including all of its privileges), we should use the following procedure.
– Use SHOW GRANTS to determine what privileges the account has.
– Use REVOKE to revoke the privileges displayed by SHOW GRANTS .
– This removes rows for the account from all the grant tables except the user table, and revokes any global privileges listed in the user tables.
– Delete the account by using DROP USER to remove the user table row.
Note: DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user’s session is closed. Once the session is closed, the user is dropped, and that user’s next attempt to log in will fail.
DROP USER does not automatically delete or invalidate any database objects that the user created. This applies to tables, views, stored routines, and triggers.
Method 2: We can create the user with the command GRANT statement.
Syntax for the Grant statement:
http://dev.mysql.com/doc/refman/5.1/en/grant.html
The GRANT statement enables system administrators to create MySQL user accounts
and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION
privilege, and you must have the privileges that you are granting.
Example: GRANT ALL ON *.* TO ‘monitor’@’localhost’ identified by ‘monitor123’;
By using GRANT statement, at a time we can create user , and can grant privileges
And can set the password to that particular account.
We can grant GLOBAL Privileges, DATABASE Privileges, TABLE privileges, COLUMN
privileges.
Assigning Account Passwords:
To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause:
mysql > CREATE USER ‘username’@’localhost’ IDENTIFIED by ‘password’;
To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement:
Guidelines for Password Security:
-MySQL stores passwords for user accounts in the mysql.user table. Access to this
table should never grant privileges to any other non-administrative accounts.
– Passwords can appear as plain text in SQL statements such as CREATE USER,
GRANT, and SET PASSWORD. To guard against unwarranted exposure to log files, they should be located in a directory that restricts access to only the server and the database administrator.
– Replication slaves store the password for the replication master in the master.info file. Access to this file should be restricted to the database adminstrator.
– Database backups that include tables or log files containing passwords should be protected using a restricted access mode..