Shulou(Shulou.com)06/01 Report--

1. Brief description: there are usually multiple users sharing in the database. In order to ensure the security of the database, SQL language provides data control statement DCL (Data Control Language) to manage the database uniformly. In order to realize the security of the database, it must be based on the principle of minimization of authority distribution, so as to minimize the risk. This ensures that authorized users can view or modify data, and that authorized users can access legitimate information.

2. Permissions can be assigned.

The principle of authority allocation follows: where you come from, where you go, and what you do.

Where does it come from: you can specify a user name and allow the ID to connect.

Where to: database name, table name

What to do: commands that can be used

The above three points use the user,db,host table of the Mysql database.

Assignable permissions table:

MySQL permission distribution: the question of which permissions are set for what tables and which permissions can be set for columns

Permissions for possible settings of permission distribution

Table permissions' Select', 'Insert',' Update', 'Delete',' Create', 'Drop',' Grant', 'References',' Index', 'Alter'

Field permission 'Select',' Insert', 'Update','References'

Procedure permission 'Execute',' Alter Routine', 'Grant','create'

MySQL permission experience principle:

Permission control is mainly for security reasons, so you need to follow the following empirical principles:

1. Grant only the minimum permissions that can meet the needs to prevent users from doing bad things. For example, users only need to query, then only give select permission, do not give users update, insert or delete permissions.

2. Limit the login host of a user when creating a user, which is generally limited to a specified IP or private network IP segment. @ '192.168.1.28' hostname

3. Delete users without passwords when initializing the database. When the database is installed, some users are automatically created who do not have a password by default.

4. Set a password that meets the password complexity for each user. CRM is like a CRM14floor&& pair of cold frosts on the ground.

5. Clean up unwanted users regularly. Reclaim permissions or delete users. Account audit

3. Application of permission assignment

3.1 user permissions View

Mysql > select * from mysql.user where user= "root"\ G; view root user details

Mysql > show grants; to view current user permissions

Mysql > show grants for "root" @ "localhost"; 3.3.View a user right

3.2 user creation

Create a xiaoxiong that allows users to log in locally and allow permissions to be granted to other users with a password set to xiaoxiong

Mysql > grant all privileges on. To xiaoxiong@'localhost' identified by 'xiaoxiong' with grant option

Parameters:

All privileges: indicates all permissions on: used to specify which tables and libraries are in effect, and the wildcard * identifies all tables and libraries.

Xiaoxiong@'localhost': user name @ login address, localhost for local login, or 127.0.0.1 for local br/ > on: used to specify which tables and libraries are in effect, and the wildcard * identifies all tables and libraries.

Xiaoxiong@'localhost': user name @ login address. Localhost means local login, or 127.0.0.1 also means local login.

With grant option: allows users to assign their own permissions to others

Grant can add permissions to users repeatedly.

The permissions need to be refreshed after the user is created, and the user will take effect.

Mysql > flush privileges

[root@node1 ~] # mysql-uxiaoxiong-pxiaoxiong verify login is successful

Mysql > revoke all privileges on. From "xiaoxiong" @ "localhost"; recall permission

Delete a user:

Mysql > drop user xiaoxiong@'localhost'

Modify the user name:

Mysql > rename user "xiaoxiong" @ "localhost" to "XiaoXiong01" @ "%"

Change the password:

Method 1. When root has no password,

Mysqladmin-uroot password' password'

Recharge password:

Mysql > set password for "xiaoxiong" @ "localhost" = password ('xiaoxiong01')

Mysql > set password=password ('password'); modify the current user password

Mysql > FLUSH PRIVILEGES

Whether it is created or modified, the memory needs to be flushed, otherwise it will not take effect.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.