How does mysql view user permissions-1024programmer

How to view user privileges in mysql: 1. To view the privileges of a MySQL user, the syntax is [show grants for user name]; 2. To use the authorization method of the database, the code is [GRANT ON ..].

【Related learning recommendation: mysql tutorial(video)】

How to check the permissions of the user table in mysql:

(1) Check the permissions of a MySQL user:

show grants for username

MariaDB [neutron]> show grants for root;

(2) Use the GRANT command to create new users, set user passwords, and increase user rights. The format is as follows:

mysql> GRANT  ON  TO  [IDENTIFIED BY ""] [  WITH GRANT OPTION];

Example:

GRANT ALL PRIVILEGES ON neutron.* TO 'neutron'@&  #39;localhost'IDENTIFIED BY'NEUTRON_DBPASS';
 GRANT ALL PRIVILEGES ON neutron.* TO 'neutron'@'%' IDENTIFIED BY 'NEUTRON_DBPASS';

neutron.* : Indicates all tables in the neutron database, and the neutron library must be created before authorization. If *.* means all tables of all libraries
& # 39; neutron & # 39; @ & # 39; localhost & # 39; : Indicates the created user name neutron, followed by @ indicates the client that is allowed to access the data, ‘localhost’ indicates the local machine, ‘%’ indicates all hosts
[IDENTIFIED BY ""]: set neutron user password

（3）Database

GRANT  ON  TO  [IDENTIFIED BY ""] [WITH GRANT OPTION  ];

is a comma-separated list of MySQL user privileges you want to grant.

The permissions you can specify can be divided into three types:

1) Database/data table/data column permissions:

Alter: Modify existing data tables (such as adding/deleting columns) and indexes.
Create: Create a new database or data table.
Delete: Delete the records of the table.
Drop: Delete the data table or database.
INDEX: Create or delete an index.
Insert: Add records to the table.
Select: Display/search the records of the table.
Update: Modify existing records in the table.

mysql>grant select,insert,delete,create,drop on *.* (or nova.* other libraries or  Table) to & # 39; username & # 39; @ & # 39; localhost & # 39; identified by 'password';

2) global management MySQL user authority:

file: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload the access control list, refresh the log, etc.
SHUTDOWN: Shut down the MySQL service.

3) Special permissions:

ALL: allowed to do anything (same as root).
USAGE: Only login allowed — nothing else allowed.

In development and practical application, users should not only use the root user to connect to the database. Although it is convenient to use the root user for testing, it will bring problems to the system Major safety hazards are also not conducive to the improvement of management technology.

For example, a user who only inserts data should not be given permission to delete data. MySql user management is implemented through the User table. There are two common methods for adding new users. One is to insert the corresponding data row in the User table and set the corresponding permissions at the same time; the other is to create a user with certain permissions through the GRANT command. user. The common usage of GRANT is as follows:

grant all on mydb.* to NewUserName@HostName identified by “password”;
 grant usage on *.* to NewUserName@HostName identified by “password”;
 grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
 grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;

If you want to give this user the ability to manage permissions on the corresponding object, you can add after GRANT WITH GRANT OPTION option.

For users added by inserting into the User table, the Password field should be updated and encrypted with the PASSWORD function to prevent unscrupulous people from peeking at the password.

For those users who are no longer in use, they should be cleared, and users whose permissions are beyond the limit should be revoked in time. Reclaiming permissions can be done by updating the corresponding fields of the User table, or by using REVOKE.

Want to learn more programming learning, please pay attention to the php training column!

The above is how mysql checks the details of user permissions. For more information, please pay attention to other related articles on 1024programmer.com!