MySQL User Rights Assignment

Mysql> grant permissions 1, permissions 2,... Permission n on the database name. Table name to User name @ user address identified by ' connection password ';

Permissions 1, Permissions 2,... Permission n represents 14 permissions, such as Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file. When permissions 1, permissions 2,... Permission n is replaced by all privileges or all to give the user full permission.

When the database name. The table name is replaced by *. *, which indicates that the user is given permission to manipulate all tables on the server.

The user address can be localhost, or it can be an IP address, a machine name, and a domain name. You can also use '% ' to indicate connections from any address.

' Connection password ' cannot be empty, otherwise the creation failed.

Add new users

Format: Grant permissions on database. * To User name @ login host identified by "password"

For example, add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] identified by "Password1";

If you want the user to be able to log on to MySQL on any machine, change localhost to "%".

If you do not want to User1 have a password, you can make another command to remove the password.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

The MySQL permissions system revolves around two concepts:

1: Authentication, determine whether the user is allowed to connect to the database server

2: Authorization, determine if the user has sufficient permissions to execute the query request, and so on.

Obviously from the above, if the certification is not successful, which authorization is certainly not possible. Here we will focus on two tables, the user table and the DB table, respectively, in the MySQL database.

The user table is unique to some extent because it is the only table that works in the authentication and authorization stages of a permission request and is the only one that has the permissions on the MySQL server. During the authentication phase, it is only responsible for authorizing the user to access the MySQL server, determining the maximum number of connections per hour and the maximum number of concurrent users; During the authorization phase, user determines whether the users who are allowed to access the server are given global permissions to the operational database, determining the maximum number of queries and updates per hour for the user.

The DB table is used to give each user permissions on each database. Specifically, you can view the fields of the db.

User and Rights Management commands:

Create User: For creating a new account (starting with the 5.0 version of this command), do not assign any permissions when creating this user, you need to assign the appropriate permissions to the change user after creation through the grant command.

eg:create user [email protected] identified by ' 123456 ';
Grant Select on mydb.* to [email protected];

Drop User: Delete a Subscriber account (note that only accounts without any permissions can be deleted before the 4.1.1 version, and any accounts may be deleted after 5.0.2)

Eg:drop user Guest;

Rename User: Can be implemented to rename a Subscriber account.

Grant: Used to administer access, that is, to authorize user accounts. Of course it can also create a new user account.

Eg:grant Select, INSERT, UPDATE, delete on new_db.* to [email protected] '% ' identified by ' 88888888 ';

Grant permissions on the database. Table to user @ Access mode identified by password

Grant Select on mydb.* to [e-mail protected] identified by ' 123456 ';

BTW: If you need a blank password or a password-free account, you must first use the Create User command, and then

Grant to assign permissions. If you do the following:

Grant all privileges the mydb.* to [email protected] '% '; The database user table is not created first visitor

User, a 1133 error will occur "can ' t find any matching row in the user table". Grant can only create

An account with a password.

MySQL User Rights Assignment