[MySQL] View user rights and grant usage

Excerpt from: http://apps.hi.baidu.com/share/detail/15071849

View User Permissions

Show grants for your users

Like what:
Show grants for [email protected] ' localhost ';

Grant usage

GRANT USAGE on *. discuz ' @ ' localhost ' identified by PASSWORD ' *c242ddd213be9c6f8da28d49245bf69fc79a86eb ';
GRANT all Privileges "discuz". * to ' discuz "@ ' localhost ';

I'll explain what I mean by the above two sentences.
Set up a user name Discuz password that can only be logged on locally to be encrypted.
And then the second sentence means, give this discuz user all permissions to the Discuz database

Using the grant

The grant command is used to establish a new user, specify a user password, and increase user permissions. The format is as follows:

Mysql> GRANT <privileges> on <what>
<user> [Identified by "<password>"]
, [with GRANT OPTION];

As you can see, there are many things to fill in this command. Let's take a look at each of them and finally give some examples to get a sense of how they work together.

<privileges> is a comma-delimited list of permissions that you want to give. The permissions you can specify can be divided into three types:

Database/data Table/Data column permissions: Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
Insert: Adds a table record.
Select: Displays/searches the table's records.
Update: Modifies a record that already exists in the table.

Global Administrative permissions:

File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.

Special permissions:

All: Allow to do anything (as root).
USAGE: Only allow login-nothing else is allowed.

These permissions relate to the features of MySQL, some of which we have not yet seen, and most of which are familiar to you.

<what> defines the areas in which these permissions are scoped. *. * means that permissions are valid for all databases and data tables. Dbname.* means that all data tables in the database named DbName are valid. Dbname.tblname means that only data tables named Tblname named DbName are valid. You can even use the list of data columns in parentheses after the given permissions to specify that the permissions are valid only for those columns (as we'll see in the following example).

<user> Specifies the users who can apply these permissions. In MySQL, a user is specified by the user name that it logs on and the host name/IP address of the computer that the user is using. Both values can use the% wildcard character (for example [email protected]% will allow user name Kevin to log on from any machine to enjoy the permissions you specify).

<password> Specifies the password used by the user to connect to the MySQL service. It is enclosed in square brackets, stating that identified by "<password>" is optional in the grant command. The password specified here supersedes the user's original password. If you do not specify a password for a new user, the password is not required when he connects.

The optional with GRANT option section of this command specifies that the user can use the Grant/revoke command to give other users the permissions that he has. Be careful with this feature--although the problem may not be so obvious! For example, two of users who have this feature may share their permissions with each other, which may not be what you wanted to see.

Let's take a look at two examples. Create a user named Dbmanager, who can connect to MySQL from server.host.net using a password managedb, and can only access the entire contents of a database named db (and can assign this permission to other users), which can use the following Grant command:

Mysql> GRANT all on db.*
-to [email protected]
-Identified by "Managedb"
With GRANT OPTION;

Now change this user's password to funkychicken, the command format is as follows:
Mysql> GRANT USAGE on *. *
-to [email protected]
-Identified by "Funkychicken";

Please note that we do not give any additional permissions (the usage permission only allows the user to log on), but the permissions that the user already exists 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. He can update the user's name and email address in the database, but does not need to consult other database information. That means he has read-only permissions to the DB database (for example, Select), but he can perform an update operation on the name and email columns of the users table. The command is as follows:

Mysql> GRANT Select on db.*
-to [email protected]%.host.net
-Identified by "Jessrules";
Mysql> GRANT Update (name,email) on DB. Users
-to [email protected]%.host.net;

Note that in the first command we used the% (wildcard) notation when specifying the hostname that Jessica can use to connect. In addition, we did not give him the ability to pass his permission to other users because we did not bring with GRANT OPTION at the end of the order. The second command demonstrates how to assign permissions to a particular data column by using a comma-delimited list of columns in parentheses after the given permission.

[MySQL] View user rights and grant usage