How to check user permissions in mysql

Mysql method to check user permissions: 1. Check the permissions of a MySQL user, the syntax is [show grants for username]; 2. Use the database authorization method, the code is [GRANT ON ..].

【Related learning recommendations: mysql tutorial(Video)】

Mysql method to check user table permissions:

(1) Check the permissions of a certain MySQL user:

show grants for username

MariaDB [neutron]> show grants for root;

(2) Use the GRANT command to create a new user, set the user password, and increase user permissions. The format is as follows:

mysql> GRANT <privileges> ON <what>  TO <user> [IDENTIFIED BY "<password>"]  [WITH GRANT OPTION];

For example:

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

• ##neutron.*: Indicates all tables in the neutron database, neutron before authorization The library must be created first. If *.* is used to represent all tables in all libraries

• ## 'neutron'@'localhost'

  : represents the created user name neutron, and the @ following represents allowing access to data client, 'localhost' means the local machine, '%' means all hosts

• [IDENTIFIED BY ""]

  : is to set neutron User password

(3) Database authorization method

GRANT <privileges> ON <what>  TO <user> [IDENTIFIED BY "<password>"]  [WITH GRANT OPTION];

is a comma-separated A list of MySQL user permissions 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 a 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 *.* (或nova.*其它库或表) to '用户名'@'localhost'  identified by ‘密码’；

2) Global management of MySQL user permissions:

file: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload access control lists, refresh logs, etc.
SHUTDOWN: Shut down the MySQL service.
3) Special permissions:

ALL: Allowed to do anything (same as root).
USAGE: Only login is allowed - nothing else is allowed.
In development and practical applications, 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 major security risks to the system. , and is 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 rows in the User table and set the corresponding permissions; 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 his permissions on the corresponding object, you can add the

WITH GRANT OPTION

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

Those users who are no longer in use should be cleared, and users whose permissions have exceeded the limit should be promptly reclaimed. Permissions can be reclaimed by updating the corresponding fields in the User table, or by using the REVOKE operation.

If you want to know more about programming learning, please pay attention to the

php training

column!

The above is the detailed content of How to check user permissions in mysql. For more information, please follow other related articles on the PHP Chinese website!