MYSQL User Management

1. Permissions Table

MySQL is a multi-user database, MySQL users can be divided into two major categories:

(1) Super Admin user (root) with full privileges

(2) Ordinary user, created by root, ordinary users only have the permissions assigned by root

1.1 the location of the permission table

Database: MySQL

Data tables related to permissions: User,db,host,tables_priv,columns_priv,procs_priv, etc.

1.2 User Table

The User table stores:

(1) User information: Hots (user's host), User (username), password (password)

(2) User's permission information: _priv

(3) Security-related information: ssl_,x509, recording user login verification actions, etc.

(4) Information related to resource allocation: Max_,

Max_questions represents the maximum number of queries that a user can make within one hours.

Max_updates represents the maximum number of updates a user can make within one hours, as well as increasing data and deleting data.

Max_connections: Indicates the maximum number of connections a user can make

1.2.1 User contents of the table

(1) Host column

localhost means the user or Super administrator of this machine

% represents the root user on any host

Description: _priv permissions are global permissions and are not limited by the scope of the database

1.3 DB Table

(1) User-related fields: Hots (the host where the user resides), User (username),

(2) Permissions-related fields: The _priv,db field specifies the valid range of _priv permissions.

1.4 Host Table

(1) User-related fields: Hots (the host on which the user resides)

(2) Permissions-related fields: The _priv,db field specifies the valid range of _priv permissions.

Description

(1) record the user on the host to the database permissions, focus on the host, but not the user, for example, assuming select_priv=y, the host of all the database users have SELECT permissions.

(2) The priority of the host table is greater than the DB table, if the DB table stipulates that the user does not have permissions, but the host table specifies that the user has permission to the server, then the DB user also has permissions.

1.5 Tables_priv Table

Sets the user's permissions on a table that records the user's information, as well as the permissions information for a table Table_priv (SELECT, Lnsert,alter, and so on), and the permission information Column_priv for a column on the table.

1.6 Column_priv table

Records the permissions that a user has on a column of a table.

1.7procs_priv Table

Specifies user permissions on stored procedures and stored functions, main fields: Proc_priv

2. Create a database user

2.1 Create a normal user

2.1.1 CREATE USER

CREATE USER ' User name ' [@ ' host name ']

Example: CREATE USER ' user1 ';

Verify that the creation is successful:

mysql> SELECT user from Mysql.user;

+-------+

| user |

+-------+

| User1 |

| Root |

| Root |

| |

| PMA |

| Root |

+-------+

6 rows in Set (0.00 sec) indicates that the newly created user has entered into the user table

Description: A user created with create user has no permissions, and the value of the User table's permission field is n

( 1 ) to create a user with a host name

CREATE USER ' User name ' [@ ' host name '] [indentified by ' user password ']

CREATE USER ' user2 ' @ ' localhost ';

Mysql> select User,host from Mysql.user;

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

| user | Host |

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

| User1 | %         |

| Root | 127.0.0.1 |

| Root | :: 1 |

| | localhost |

| PMA | localhost |

| Root | localhost |

| User2 | localhost |

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

7 Rows in Set (0.00 sec)

Description:% of host field is not limited by any host

( 2 ) Create a user with a password

CREATE user ' user3 ' @ ' localhost ' [indentified by ' user password '];

Example:

CREATE USER ' user3 ' @ ' localhost ' identified by ' 123333 ';

Verify:

Mysql> SELECT user,password,host from Mysql.user;

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

| user | password | Host |

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

|                                           Root | | localhost |

|                                           Root | | 127.0.0.1 |

|                                           Root | | :: 1 |

|                                           | | localhost |

|                                           PMA | | localhost |

|                                           User1 | | %         |

|                                           User2 | | localhost |

| User3 | *0166e21e66009700f528ca21179af9ad81120da2 | localhost |

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

8 rows in Set (0.00 sec)

Description: The password is displayed in the form of a hash code

2.1.2 Use GRANT to create a user, and to grant permissions

Grant is used to authorize a user, but it can also be used to create a user, grant will automatically create the user if the user does not exist, and then grant the user authorization.

(1) Add Permissions

Grant Permissions on Database . Table To ' User name ' @ ' Login Host ' [indentified by ' user password '];

Permissions: SELECT, Update,delete,insert (table data), create,alert,drop (table structure), references (foreign key), create temporary tables (create temp table), index ( Operation index), create View,show view, create Routine,alert routine,execute (stored procedure), All,all privileges (All rights)

Database: Database name or * (all databases)

Table: Table name or * (all tables under a database), *. * represents all tables for all databases

Host: Host name or% (any other host)

Example: Grant Selec,insert,update,delete on * * to ' jifei ' @ '% ';

GRANT SELECT on *. user4 ' @ ' localhost ' identified by ' 123333 ';

Mysql> SELECT user,password,host from Mysql.user;

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

| user | password | Host |

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

|                                             Root | | localhost |

|                                             Root | | 127.0.0.1 |

|                                             Root | | :: 1 |

|                                            | | localhost |

|                                            PMA | | localhost |

|                                              User1 | | %      |

|                                             User2 | | localhost |

| User3 | *0166e21e66009700f528ca21179af9ad81120da2 | localhost |

| User4 | *0166e21e66009700f528ca21179af9ad81120da2 | localhost |

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

9 Rows in Set (0.00 sec)

(2) grant the user permission to specify a database, specify a table, and specify a column:

GRANT UPDATE (cid,cname) on Mysqlpart2.custom to ' user3 ' @ ' localhost ';

After the authorization is successful, you can view the authorization information in the following table:

Database: mysql» table: Tables_priv "Table Privileges"

Database: mysql» table: Columns_priv "Column Privileges"

(3) User Rights Table

Location: Database: information_schema» table: User_privileges

Description of the table:

GRANTEE: Authorized by

Privilege_type: Permission Name

User table: Database: mysql» table: User "Users and global privileges"

Description: In the user table, the value of Privilege_type for the "_priv" range user_privileges table corresponds to one by one.

( 4 ) The hierarchical relationship of permissions

The hierarchical relation of ① permission refers to the scope of application of permission.

The highest level of ② permissions is the global level, which is the ability to operate on any data table in any database.

③ database level: operation can only be performed on a database.

④ Table level: Permissions Information Location: Database: mysql» table: Tables_priv "Table Privileges"

⑤ Column Level: Permissions information Location: Database: mysql» table: Columns_priv "column Privileges"

⑥ Sub-Program level: Permissions Information Location: Database: mysql» table: Procs_priv "Procedure Privileges"

( 5 ) Revoke Permissions

REVOKE Permissions on Database . Table From ' User name ' @ ' Login Host ;

Description: The difference between empowering and revoking permissions is that revoke is changing to the From

Example: Revoke all on * * from ' jifei ' @ '% ';

REVOKE UPDATE (cid,cname) on Mysqlpart2.custom from ' user3 ' @ ' localhost ';

(6) View Permissions

SHOW grants;// own

SHOW GRANTS for User name @ host name;

Cases:

SHOW GRANTS for [email protected];//specify user-specified host

mysql> SHOW GRANTS for [email protected];

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

| Grants for [email protected] |

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

| GRANT SELECT on *. user3 ' @ ' localhost ' identified by PASSWORD ' *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD ' |

| GRANT UPDATE (CNAME, CID) on ' Mysqlpart2 '. ' Custom ' to ' user3 ' @ ' localhost ' |

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

2 rows in Set (0.00 sec)

Note: All the words that follow the show keyword are plural, and all the words that follow the Create keyword are singular

through Mysql.columns_priv table to view permissions:

SELECT * from Mysql.columns_priv WHERE user= ' user3 ' and host= ' localhost ';

Mysql> SELECT * from Mysql.columns_priv WHERE user= ' user3 ' and host= ' localhost ' \g

1. Row ***************************

Host:localhost

Db:mysqlpart2

User:user3

Table_name:custom

Column_name:cid

timestamp:0000-00-00 00:00:00

Column_priv:update

2. Row ***************************

Host:localhost

Db:mysqlpart2

User:user3

Table_name:custom

Column_name:cname

timestamp:0000-00-00 00:00:00

Column_priv:update

2 rows in Set (0.00 sec)

2.1.3 on the direct to User table to insert records to create a user

You can create users by inserting records directly into the user table, but because there are a lot of fields in the user table and all fields are not allowed to be empty, it is necessary to assign a value to each column, so it is not recommended to create a user in this way.

2.1.4 CREATE USER with the GRANT two ways to create user differences

(1) Create user advantage: Simple syntax

(2) Create user insufficient: User does not have permission

(3) Benefits of GRANT creation User: User created has permissions

(4) The lack of GRANT creation users: syntax is cumbersome than create user

3. Delete the MYSQL user

Delete from Mysql.user where user= ' User name ' and host= ' Host name ';

Example: DELETE from Mysql.user WHERE user= ' user3 ' and host= ' localhost ';

Use:flush privileges to refresh permissions after deletion

Description

After you delete a user using Deletet, you must use flush privileges to refresh the permissions, or you will not be able to continue creating users with the same user name as the deleted user, even if you do not see the deleted user in the user table, and you cannot create a new one without refreshing the permissions.

Cases:

Delete User user3:

Mysql> DELETE from Mysql.user WHERE user= ' user3 ' and host= ' localhost ';

Query OK, 1 row Affected (0.00 sec)

To view the user table, User3 has been deleted successfully:

Mysql> Select User from Mysql.user;

+-------+

| user |

+-------+

| User1 |

| Root |

| Root |

| |

| PMA |

| Root |

| User2 |

| User4 |

+-------+

8 rows in Set (0.00 sec)

Create user User3 failure:

mysql> CREATE USER ' user3 ' @ ' localhost ' identified by ' PWD ';

ERROR 1396 (HY000): Operation CREATE USER failed for ' user3 ' @ ' localhost '

Refresh permissions:

FLUSH privileges;

Create user again, success:

mysql> CREATE USER ' user3 ' @ ' localhost ' identified by ' PWD ';

Query OK, 0 rows Affected (0.00 sec)

4. Modify the user password:

UPDATE mysql.user SET password=password (' new password ') WHERE user= ' User name

[and host= ' host name '];

UPDATE mysql.user SET password=password (' 111111 ') WHERE user= ' root ';

Attention:

(1) If the Where condition is not added, all user's password will be changed to ' new password '

(2) After the password modification is completed, the permission refresh operation is required to take effect, flush privileges;

Cases:

UPDATE mysql.user SET Password=password (' 111 ') WHERE user= ' user1 ';

( 1 ) permissions to modify passwords

The root user can modify his or her password, or he can modify another user's password

Other users can only modify their own passwords

( 2 ) PASSWORD function

Used to encrypt plaintext of the password, the resulting password is the hash value of the original password.

Cases:

mysql> SELECT PASSWORD (' 111 ');

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

| PASSWORD (' 111 ') |

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

| *832eb84cb764129d05d498ed9ca7e5ce9b8f83eb |

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

1 row in Set (0.07 sec)

( 3 ) ROOT user, Normal user to modify your own password

SET Password=password (' new password ');

( 4 ) ROOT user to change password for other users:

SET PASSWORD for ' User name ' @ ' Host name ' = password (' new password ');

example, the following two ways to change the password result in the same way:

SET PASSWORD for ' user1 ' @ '% ' =password (' 111 ');

UPDATE mysql.user SET Password=password (' 111 ') WHERE user= ' user1 ';

MYSQL User Management