MySQL user and permission management

You can use create user or grant to create a user, which is also assigned relevant permissions. Revoke is used to delete user permissions and drop user to delete accounts.

$ Mysql-u root-ppassword: mysql> Create Database test; # create database query OK, 1 row affected (0.00 Sec) mysql> show databases; # Check whether the database is successfully created + ------------------ + | database | + -------------------- + | information_schema | MySQL | test | + ------------------ + 3 rows in SET (0.00 Sec) mysql> grant all on test. * To user1 @ '%' identified by '000000' with grant option; # create privilege management user query OK, 0 rows affected (123456 Sec) mysql> Select User, host from MySQL. user; # Check whether the user is successfully created + ---------------- + ----------- + | user | host | + ------------------ + ----------- + | user1 | % | root | 127.0.0.1 | Debian-sys-Maint | localhost | | root | localhost | root | Server | + ------------------ + ----------- + 5 rows in SET (0.00 Sec) mysql> show grants for user1; # view User Permissions + grants + | grants for user1 % | + grants + | grant usage on *. * To 'user1' @ '%' identified by password' * 6bb... 2ca2ad9 '| grant all privileges on 'test '. * To 'user1' @ '%' with grant option | + rows + 2 rows in SET (0.00 Sec)

Grant Syntax:

GRANT privileges (columns)    ON what    TO user IDENTIFIED BY "password"    WITH GRANT OPTION

Permission list:

• Alter: Modify tables and indexes.
• Create: Create a database and a table.
• Delete: Delete existing records in the table.
• Drop: discard (delete) databases and tables.
• Index: Create or discard an index.
• Insert: Insert a new row into the table.
• Reference: unused.
• Select: Retrieves records from a table.
• Update: Modify existing table records.
• File: read or write files on the server.
• Process: View information about the thread executed on the server or kill the thread.
• Reload: Reload the authorization table or clear logs, host caches, or table caches.
• Shutdown: Shut down the server.
• ALL: All permissions, synonym for all privileges.
• Usage: Special "no permission" permission.

The User Account consists of "username" and "host", which indicates the user is allowed to access from where. User1 @ '%' indicates any address, which can be omitted by default. It can also be "user1@192.168.1. %", "user1 @ % .abc.com" and so on. The database format is dB @ table, which can be "test. *" or "*. *". The former indicates all tables of the test database, and the latter indicates all tables of all databases.

Clause "with grant option" indicates that this user can assign permissions to other users.
We use root to create several more users, and then the Administrator user1 of the Test Database assigns them permissions.

mysql> create user user2 identified by '123456', user3 identified by 'abcd';Query OK, 0 rows affected (0.00 sec)mysql> select user, host from mysql.user;+------------------+-----------+| user             | host      |+------------------+-----------+| user1            | %         || user2            | %         || user3            | %         || root             | 127.0.0.1 || debian-sys-maint | localhost || root             | localhost || root             | server    |+------------------+-----------+7 rows in set (0.00 sec)

Now, log out and use user1 to log on to the test database.

Mysql> quit # exit bye $ mysql-u user1-p123456 test # log on to MySQL with a new user> select database (); # confirm the current working database + ------------ + | database () | + ------------ + | test | + ------------ + 1 row in SET (0.00 Sec) mysql> select CURRENT_USER (); # confirm the current work account + -------------- + | CURRENT_USER () | + ---------------- + | user1 @ % | + ---------------- + 1 row in SET (0.00 Sec)

Continue. Create a data table.

Mysql> Create Table Table1 # create a table-> (-> name varchar (50),-> Age integer->); query OK, 0 rows affected (0.02 Sec) mysql> show tables; # Check whether the table is successfully created + ---------------- + | tables_in_test | + ---------------- + | Table1 | + ---------------- + 1 row in SET (0.00 Sec) mysql> describe Table1; # View table structure + ------- + ------------- + ------ + ----- + --------- + ------- + | FIELD | type | null | key | default | extra | + ------- + ------------- + ------ + ----- + --------- + ------- + | Name | varchar (50) | Yes | null | age | int (11) | Yes | null | + ------- + ------------- + ------ + ----- + --------- + ------- + 2 rows in SET (0.00 Sec) mysql> insert into Table1 values ('Tom ', 20); # insert record query OK, 1 row affected (0.00 Sec) mysql> select * From Table1; # query records + ------ + | Name | age | + ------ + | Tom | 20 | + ------ + 1 row in SET (0.00 Sec)

Next we will assign permissions to user2 and user3.

Mysql> grant select on test. * To user2; # assign select permission to user2. Query OK, 0 rows affected (0.00 Sec) mysql> grant select on test. * To user3; # assign select permission to user3. Query OK, 0 rows affected (0.00 Sec) mysql> grant insert, update on test. * To user2; # Add insert and update permissions for user2. Query OK, 0 rows affected (0.00 Sec)

Okay, let's exit and switch to user2.

$ Mysql-u user2-p123456mysql> use test; # switching the Working Database reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup with-adatabase changedmysql> select database (); # verify the current working database + ------------ + | database () | + ------------ + | test | + ------------ + 1 row in SET (0.00 Sec) mysql> Select User (); # verify the current account + --------------- + | user () | + ----------------- + | us Er2 @ localhost | + --------------- + 1 row in SET (0.00 Sec) mysql> show grants for user2; # view the current user permissions. Apparently, the insert and update statements added later are added. + Grants + | grants for user2 % | + grants + | grant usage on *. * To 'user2' @ '%' identified by password' * 6bb837 .... 2c9 '| grant select, insert, update on 'test '. * To 'user2' @ '%' | + rows + 2 rows in SET (0.00 Sec)

Perform Operation tests.

Mysql> insert into Table1 values ("Jack", 21); # query OK after successful insert, 1 row affected (0.00 Sec) mysql> Update Table1 set age = 22 where name = 'jack'; # query OK after successful update, 1 row affected (0.00 Sec) rows matched: 1 changed: 1 Warnings: 0 mysql> select * From Table1; # select operation succeeded + ------ + | Name | age | + ------ + | Tom | 20 | Jack | 22 | + ------ + 2 rows in SET (0.00 sec) mysql> Delete from Table1 where age = 22; # delete operation without permission error 1142 (42000): delete command denied to user 'user2' @ 'localhost' for table 'table1'

Switch back to user1 to manage the account and remove the update permission of user2.

$ Mysql-u user1-p123456 testmysql> revoke update on test. * From user2; # Remove update permission query OK, 0 rows affected (0.00 Sec)

Switch back to user2.

$ Mysql-u user2-p123456 testmysql> show grants for user2; # The update permission is removed + privileges + | grants for user2 @%| + privileges + | grant usage on *. * To 'user2' @ '%' identified by password' * 6B... 2ad9' | grant select, insert on 'test '. * To 'user2' @ '%' | + tables + 2 rows in SET (0.00 Sec) mysql> Update Table1 set age = 23 Where name = 'jack '; # Do not have the update permission error 1142 (42000): update command denied to user 'user2' @ 'localhost' for table 'table1'

Now, we have basically completed creating users and assigning permissions. Next, we will go back to root to modify the user password and delete the user.

$ Mysql-u root-p123456mysql> set password for user3 = PASSWORD ('abcabc'); # modify the user3 password query OK, 0 rows affected (0.00 Sec) mysql> flush privileges; # refresh the permission table (this operation is usually required only after the related management data table is directly modified) query OK, 0 rows affected (0.00 Sec) mysql> revoke all on *. * From user2; # Remove the permissions of user2 on all databases query OK, 0 rows affected (0.00 Sec) mysql> drop user user2; # Delete user2 account query OK, 0 rows affected (0.00 Sec) mysql> Select User, host from MySQL. user; # verify the deletion result + ------------------ + ----------- + | user | host | + ------------------ + ----------- + | user1 |%| user3 |%| root | 127.0.0.1 | Debian-sys-Maint | localhost | root | Server | + ------------------ + ----------- + 6 rows in SET (0.00 Sec)

User user2 cannot be used again.

$ mysql -u user2 -p123456 testERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)

Try user3.

$ Mysql-u user3-pabc test # connection failed! Oh, by the way, we changed the password. Error 1045 (28000): Access denied for user 'user3' @ 'localhost' (using password: Yes) $ mysql-u user3-pabcabc test # mysql> select * From Table1; # select operation succeeded + ------ + | Name | age | + ------ + | Tom | 20 | Jack | 22 | + ------ + 2 rows in SET (0.00 sec)

To change your password, Run "Set Password = PASSWORD ('new _ password.
------- Abstract --------------------------------------

Create a user:

GRANT insert, update ON testdb.* TO user1@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;CREATE USER user2 IDENTIFIED BY 'password';

Assign permissions:

GRANT select ON testdb.* TO user2;

View permissions:

HOW GRANTS FOR user1;

Change Password:

SET PASSWORD FOR user1 = PASSWORD('newpwd');SET PASSWORD = PASSWORD('newpwd');

Remove permission:

REVOKE all ON *.* FROM user1; 

Delete A User:

DROP USER user1;

Database List:

SHOW DATABASES;

Data Table list:

SHOW TABLES;

Current Database:

SELECT DATABASE();

Current User:

SELECT USER();

Data Table Structure:

DESCRIBE table1;

Refresh permission:

FLUSH PRIVILEGES;

This article from: http://my.oschina.net/huxuanhui/blog/23784