MySQL User Management

User management is the process of controlling which users are allowed to connect to the MySQL server and what permissions they have on each database.

Start the mysql server

# /etc/init.d/mysql start      or  # service mysql start   or # mysqld_safe –user=”mysql” &

Login into mysql database

[root@maxisdb1 mysql]# mysql -proot -u root

mysql>\q

mysql> show engines;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| test               |

+--------------------+

3 rows in set (0.00 sec)

mysql>use mysql;

mysql> show tables;

mysql> select user,host from user;

Mysql default port number is 3306

Mysql default storage engine is “MyIsam”(Up to 5.5)   “Innodb”(From 5.5)

SQL Commands:

Data Definition Language (DDL)

CREATE,ALTER,DROP and TRUNCATE

Data Manipulation Language (DML)

INSERT, UPDATE, and DELETE.

Data query Language (DML)

SELECT

Transaction Control Language (TCL)

COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

GRANT and REVOKE.

The GRANT statement grants privileges to MySQL user accounts.

Creating a User  with Full Privileges to all databases:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;

Creating a User  with Full Privileges to a single database:

mysql> GRANT ALL PRIVILEGES ON drkdb.* TO 'drk'@'localhost' IDENTIFIED BY 'drk' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'drk'@'%' IDENTIFIED BY 'drk' WITH GRANT OPTION;

Using the WITH GRANT OPTION allows the account to add user accounts to the specified databases.

Creating a User Account with Full Privileges on All DATABASES:

Creating Specific Privileges to a User on one database:

GRANT SELECT, INSERT, DELETE, UPDATE, EXECUTE ON test.* TO 'username'@'192.168.0.3';

To check the privileges for an account, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'drk'@'localhost';

Removing Users:

USE mysql;

DELETE FROM user WHERE user = 'username' AND host = 'hostname';

As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables:

An example of how to Create a User in MySQL using the INSERT statement

INSERT INTO user (Host,User,Password) VALUES('localhost','my_user',PASSWORD('my_pass'));

If you want to add some privileges with the same command the query will look like this:

An example of how to create a user with INSERT and SELECT privileges by editing the 'mysql.user' table

INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)

VALUES('localhost','my_user',PASSWORD('my_pass'),'Y','Y');

mysql> INSERT INTO user VALUES('localhost','drk',PASSWORD('drk'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES('%','drk',PASSWORD('drk'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

The REVOKE statement enables system administrators to revoke privileges from MySQL accounts.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM drk@localhost;

REVOKE INSERT ON *.* FROM 'drk'@'183.82.96.216';

update the password

mysql>update user set passowrd=PASSWORD('drk') where user='drk';

#flush privilges