mysql-User, Rights Management

1. Creating User: Create user ' username ' @ ' host ' [identified by ' Password '];

You can also insert data into mysql.user with insert SQL.

User permissions created with create user are small and cannot create databases, tables, and so on.

2, modify the user name: Rename user ' username 1 ' @ ' host ' to ' username 2 ' @ ' host ';

You can also modify the data in the Mysql.user table with the update SQL statement.

3, delete users: Drop user ' username ' @ ' host ';

You can also delete data from the Mysql.user table by using the Delete SQL statement.

4. Setting Password: Set password for ' username ' @ ' host ' =password (' password ');

5. Authorization (Create user and authorize, user created in this way has create database, table, etc.):

Grant permission type on library. Table/function/stored procedure to ' username ' @ ' host ' [Identified by ' Testpass '];

Example 1, the MyDB Library of the students table of all permissions to authorize the ' testuser ' @ ' 192.168.%.% ', and set the secret to Testpass:

Grant all on mydb.students to ' testuser ' @ ' 192.168.%.% ' identified by ' testpass ';

Example 2, "All libraries of all tables/functions/stored Procedures" to authorize all permissions to ' testuser ' @ ' 192.168.%.% ' and set the secret to Testpass:

Grant all on the testuser ' @ ' 192.168.%.% ' identified by ' testpass ';

Example 3, the MyDB Library of all the table's SELECT permission to the ' readonly ' @ '% ', that is ' readonly ' @ '% ' only query permissions.

Grant SELECT on mydb . * to ' readonly ' @ '% '

6. Permission Type:

(1), Management class permissions: Create User,file,show databases,super,

Reload,shutdown,processlist,replication Slave,

Replication Client,lock tables;

(2), library level, and table-level permissions: Alter,create,create View,drop,execute,

Index,grant,show View

(3), Data manipulation (table level): Select,insert,update,delete

(4), Field level: Select (col1,...), insert (col1,...), update (col1,...)

7. View the permissions of a user:

Show grants for ' username ' @ ' host ';

8. Revoke permissions: Revoke permission type on library. Table/function/stored procedure from ' username ' @ ' host ';

Such as: Will reclaim the user ' testuser ' @ ' localhost ' all permissions.

Revoke all on . from ' testuser ' @ ' localhost ';

9. Tables related to user authorization in MySQL library:

DB: Permissions at the library level.

Tables_priv: Table-level permissions.

Colomns_priv: Column-level permissions.

Procs_priv: The permissions associated with stored procedures and stored functions.

Proxies_priv: Delegate user-related permissions.

10. Flush:

(1), flush privileges: refresh authorization, generally used for the authorization of an account, so that it takes effect immediately.

(2), Flush hosts: Empty host, generally for account information is not very clear when the connection database has failed several times, then when the correct account information connection is rejected, you need to execute the flush host.

mysql-User, Rights Management