Mysql database administration tutorial – User management

Users in MySQL can be created in two ways. First way is by using CREATE or GRANT statements. Second one is to update or add to ‘user’, ‘grant’ tables of MySQL database (MySQL database is default databases that comes with installation and it holds important data realated to users, privilages and othe databases)

Using CREATE USER and GRANT statements:

 CREATE user1 IDENTIFIED BY password1,user2 IDENTIFIED BY password2,user3 IDENTIFIED BY password3
GRANT ALL PRIVILIGES on <database.table> to 'username'@'hostname' IDENTIFIED BY password

Priviliges can be limited by specifying in GRANT statement. for eg GRANT SELECT, INSERT, UPDATE ON database.* TO ‘username’@’hostname’ IDENTIFIED BY password;

Maximum number of queries , updates , connections can also be decided usind Grant statement. for eg:- if you feel that a user is abusing resources and you want to limit user connections and no of connections perhour, updates, queries, You can limit it by the following statement.

GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'hostname' 
WITH
MAX QUERIES PER HOUR 200
MAX UPDATES PER HOUR 75
MAX CONNECTIONS PER HOUR 10
MAX USER CONNECTIONS 5;

Revoking privileges

User privileges can be revoked by REVOKE statement.
For eg:-

REVOKE ALL PRIVILEGES ON databse.table FROM useername;
//or
REVOKE UPDATE,DELETE ON databse.table FROM useername;

similarily there are Statements to rename user and set a password for user.
for eg: –

RENAME USER oldusername TO newusername;
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('password');

 

Creating user by inserting values in mysql database

User can be created using simple insert commands

INSERT INTO user (host, user) VALUES ('hostname', 'username');

If you want limit or provide any privileges look for colomn in the user table and update ‘Y’ to provide and ‘N’ to revoke privileges. Also there are columns max_questions,max_updates,max_connections and max_user_connections to restrict resources to a user. ¬†We can use simple UPDATE , DELETE DML statements on user to modify user data in mysql.user table