1. Further understanding of DCL

DCL is a data control language used to manage users and permissions. In the enterprise, this part of the work is usually done by DBAs and rarely touched by the average developer.

1.2. What can DCL mainly do
  1. Create a user
  2. Delete user
  3. Change the password
  4. Granting user rights
  5. Revoking user rights

2. Authority system

The MySQL permission system is roughly divided into five levels: global level, database level, surface level, column level and subroutine level.

The hierarchy describe
The global level Applies to all databases on a given server. These permissions are stored in the mysql.user table.
Database level Applies to all targets in a given database. These permissions are stored in the mysql.db and mysql.host tables
The surface level Applies to all columns in a given table. These permissions are stored in the mysql.tabes_priv table
Column level Applies to a column in a given table. These permissions are stored in the mysql.columns_priv table. When using REVOKE, you must specify the same column as the authorized column
Subroutine hierarchy CREATE ROUTINE,ALTER ROUTINE,EXECUTE, and GRANT permissions apply to one-inch subroutines. These permissions can be granted at both the global and database levels. Furthermore, in addition to CREYAE ROUTINE, these permissions can be granted at the subroutine level and stored in the mysql.procs_priv table

The MySQL permission information is stored in the following tables. When users connect to the database, the MySQL verifies user permissions based on these tables.

The name of the table describe
user User rights table, which records accounts, passwords, and global rights information
db Record database permissions
table_priv Permissions that a user has on a table
column_priv A user’s permission on a column of a table
procs_priv User operation permissions on stored procedures and functions

3. User management

MySQL > CREATE USER; CREATE USER; The syntax is as follows:

CREATE USER 'users'[@'Host name'][IDENTIFIED BY'password']
Copy the code

The user name @ host name. The host name can be an IP address or a machine name. The host name % indicates that hosts at any address are allowed to log in to MySQL remotely. When creating a user, do not specify a host name. Default is %.

The syntax for deleting a user or changing a password is as follows:

# delete userDROP USER 'Username'[@'Host name']; # change passwordALTER USER 'Username'@'Host name' IDENTIFIED BY 'New password'
Copy the code

Such as:

  1. Create a user zhangsan to allow login on any computer
create user 'zhangsan'@The '%' identified by '123456';
Copy the code
  1. Create user lisi logon is only allowed on the computer whose IP address is 192.168.6.99
create user 'zhangsan'@'192.168.6.99' identified by '123456';
Copy the code
  1. Create a user wangwu that only allows local logins
create user 'wangwu'@'localhost' identified by '123456';
Copy the code