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

Editor to share with you how to view user permissions on mysql. I hope you will gain a lot after reading this article. Let's discuss it together.

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

Mysql's method to view the permissions of the user table:

(1) View the permissions of a user in MySQL:

Show grants for user name

MariaDB [neutron] > show grants for root

(2) use 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]

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.*: represents all the tables in the neutron database. The neutron library must be created before authorization. If you use *. * to represent all the tables of all libraries

'neutron'@'localhost': represents the created user name neutron, followed by @ indicates the client that is allowed to access the data,' localhost' represents the local machine, and'% 'represents all hosts

[IDENTIFIED BY ""]: set the neutron user password

(3) the authorization mode of the database

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

Is a comma-separated list of MySQL user rights 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: deletes the record of the table.

Drop: delete a data table or database.

INDEX: create or delete an index.

Insert: add the record of the table.

Select: show / search the records of the table.

Update: modifies records that already exist in the table.

Mysql > grant select,insert,delete,create,drop on *. * (or other nova.* libraries or tables) to 'username' @ 'localhost' identified by' password'

2) globally manage MySQL user rights:

File: read and write files on the MySQL server.

PROCESS: displays or kills service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off the MySQL service.

3) Special permissions:

ALL: allow to do anything (like root).

USAGE: only login is allowed-nothing else is allowed.

In the development and practical application, users should not only use root users to connect to the database. Although it is very convenient to use root users 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 the right to delete data. The user management of MySql is realized through the User table. There are two common ways to add 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 users with certain permissions through the GRANT command. The common uses of GRANT are 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"

To give this user the administrative ability to give him permissions on the corresponding object, add the WITH GRANT OPTION option after GRANT.

For users who are added by inserting the User table, the Password field is updated and encrypted using the PASSWORD function to prevent unscrupulous people from reading the password.

Users who are no longer in use should be cleared, and those whose permissions are out of bounds should be reclaimed in time, which can be done by updating the corresponding fields in the User table or by using REVOKE operation.

After reading this article, I believe you have a certain understanding of the method of mysql to view user rights, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.