View User Permissions and grant usage

[MySQL] viewing user permissions and grant usage

View User Permissions

Show grants for your users

For example:
Show grants for root @ 'localhost ';

Grant usage

Grant usage on *. * To 'discuz' @ 'localhost' identified by password' * c242ddd213be9c6f8da28d4245bf69fc79a86eb ';
Grant all privileges on 'discuz'. * To 'discuz' @ 'localhost ';

Let me explain the meaning of the above two sentences according to my understanding.
Create a user name that can only be logged on locally. The discuz password is ***** encrypted.
In the second sentence, grant the discuz user all the permissions to operate the discuz database.

Use Grant

The grant command is used to create a new user, specify the user password, and add user permissions. The format is as follows:

Mysql> grant <privileges> On <What>
-> To <user> [identified by "<password>"]
-> [With grant option];

As you can see, this command contains a lot of content to be filled in. Let's introduce them one by one, and finally give some examples to give you an understanding of their collaborative work.

<Privileges> is a list of permissions you want to assign separated by commas. You can specify three types of permissions:

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 table records.
Drop: delete a data table or database.
Index: Create or delete an index.
Insert: Add Table records.
Select: displays/searches for table records.
Update: Modify existing records in the table.

Global Management permissions:

File: read and write files on the MySQL server.
Process: displays or kills service threads of other users.
Reload: Reload Access Control tables and refresh logs.
Shutdown: Shut down the MySQL service.

Special permissions:

ALL: allow anything (same as root ).
Usage: Only logon is allowed. Other operations are not allowed.

The features of MySQL involved in these permissions, some of which we haven't seen yet, and most of them are familiar to you.

<What> defines the regions where these permissions apply. *. * Indicates that the permission is valid for all databases and data tables. Dbname. * indicates that it is valid for all data tables in a database named dbname. Dbname. tblname indicates that it is only valid for data tables named tblname in dbname. You can even use the list of data columns in parentheses after the granted permissions to specify that the permissions are only valid for these columns (as shown in the following example ).

<User> users who can apply these permissions. In MySQL, a user uses the user name to log on to MySQL and the host name/IP address of the computer used by the user. Both values can use the % wildcard (for example, Kevin @ % will allow the user name Kevin to log on from any machine to enjoy the permissions you specified ).

<Password> specifies the password used by the user to connect to the MySQL service. It is enclosed in square brackets, indicating that identified by "<password>" is optional in the grant command. The password specified here will replace the user's original password. If you do not specify a password for a new user, the password is not required for connection.

In the optional with grant option section of this command, users can use the grant/Revoke command to grant their permissions to other users. Please be careful when using this feature-although this issue may not be so obvious! For example, two users with this function may share their permissions with each other, which may not be what you originally wanted.

Let's look at two examples. Create a user named dbmanager who can use the password managedb to connect to MySQL from server.host.net and only access all the contents of the database named dB (and assign this permission to other users ), you can use the following grant command:

Mysql> grant all on DB .*
-> To dbmanager@server.host.net
-> Identified by "managedb"
-> With grant option;

Change the password of this user to funkychicken. The command format is as follows:
Mysql> grant usage on *.*
-> To dbmanager@server.host.net
-> Identified by "funkychicken ";

Note that we have not granted any additional permissions (the usage permission only allows users to log on), but the existing permissions of users will not be changed.

Now let's create a new user named Jessica who can connect to MySQL from any machine in the host.net domain. It can update the user's name and email address in the database, but does not need to check the information of other databases. That is to say, he has the read-only permission on the database (for example, select), but he can perform the update operation on the name and email columns of the users table. The command is as follows:

Mysql> grant select on DB .*
-> To Jessica @ % .host.net
-> Identified by "jessrules ";
Mysql> grant Update (name, email) on DB. Users
-> To Jessica @ % .host.net;

Note that the % (wildcard) symbol is used in the First Command to specify the host name that Jessica can use to connect. In addition, we do not have the ability to pass his or her permissions to other users, because we did not include the with grant option at the end of the command. The second command demonstrates how to grant permissions to a specific data column by using a list of columns separated by commas (,) in parentheses after the granted permissions.