User rights for MySQL

User Management

Tables in the MySQL database are no different from any other relational tables, and can be modified with typical SQL commands to modify their structure and data. You can use the GRANT and REVOKE commands. With these commands, users can be created and disabled, and user access can be granted and recalled online. Two new commands were added in version 5.0: CREATE user and drop user. This makes it easier to add new users, delete and rename users, and adds a third command rename user to rename existing users.

To create a user by using the Create user command

The create user is used for creating a new MySQL account. To use the Create USER statement, you must have the global Create user permission for the MySQL database or have the INSERT permission. For each account, create user creates a new record in the Mysql.user table with no permissions. If the account already exists, an error occurs. Using the optional identified by clause, ' You can set a password for the account. The user and password are set up in the same way as the GRANT statement. The prototype of its command is as follows:

CREATE User User [identified by [PASSWORD ' PASSWORD '] [, User [identified by [PASSWORD ' PASSWORD ']] ...

Create User Temp  by ' 123456 ';

To delete a user by using the drop user command

If there is one or more accounts that are idle, you should consider deleting them to ensure that they are not used for possible illegal activities. The drop user command makes it easy to remove all information from the permissions table, that is, the account privilege records from all authorization tables. The DROP user command is prototyped as follows:

DROP user user [, user] ...

PS "DROP user cannot automatically close any open user conversations, and if the user has an open conversation and cancels the user at this time, the command will not take effect until the user conversation is closed." Once the conversation is closed and the user is canceled, the user will fail to log on again.

To rename a user by using the Rename user command

The Renameuser statement is used to rename the original MySQL account. The command prototype for the Renameuser statement is as follows:

RENAME USER old_user to New_user [, Old_user to New_user] ...

PS: If the old account does not exist or the new account already exists, an error will occur.

Permissions management Grant and REVOKE commands

The grant and REVOKE commands are used to manage access rights and can be used to create and delete users, but in MySQL5.0.2 it is easier to implement these tasks with the Create user and drop user commands. The GRANT and REVOKE commands provide a degree of control over who can manipulate various aspects of the server and its content, from who can shut down the server to who can modify the information in a particular table field.

If the authorization table has a permission record that contains a mixed-case database or table name, and the lower_case_table_names system variable is set, you cannot use revoke REVOKE permission and must manipulate the authorization table directly. (grant will not create such records when Lower_case_table_names is set, but such records may have been created before the variables were set.) ）

The permissions granted can be divided into multiple tiers:

2. The global hierarchy applies to all databases in a given server, which are stored in the mysql.user table. Grant all on * * and REVOKE all on * * ONLY GRANT and REVOKE global permissions.
4. The database hierarchy applies to all targets in a given database, which are stored in the mysql.db and mysql.host tables. Grant all on db_name. * and revoke all on db_name. * Grant and REVOKE database permissions only.
6. The surface level applies to all columns in a given table, and these permissions are stored in the mysql.tables_priv table. Grant all on db_name.tbl_name and revoke all on Db_name.tbl_name only grant and revoke table permissions.
8. The column hierarchy applies to a single column in a given table, and these permissions are stored in the mysql.columns_priv table. When using revoke, you must specify the same columns as the authorized column.
9. Sub-program levels, CREATE ROUTINE, ALTER ROUTINE, execute and grant permissions apply to stored subroutines, which can be granted as global and database levels, and in addition to CREATE ROUTINE, These permissions can be granted as sub-program levels and stored inMysql.procs_privTable.

   Grant  All  on *.*  to UserIdentified by '123456'  with Grant option;--Grant users named user, password 123456, to use all permissions for all databases, and allow him to grant those permissions to other people. Revoke  All Privileges,Grant  from User;GrantUsage onBooks.*  to UserIdentified by '123456';--create a regular user without any permissionsGrant Select,Insert,Update,Delete,Index,Alter,Create,Drop  onBooks.*  to User;--to grant the user the appropriate permissionsRevoke Alter,Create,Drop  onBooks.*  from User;--Reduce PermissionsRevoke  All  onBooks.*  from User;--Revoke all Permissions

PS: When users use the GRANT and revoke command to change user rights, exit the MySQL system, when users log in to MySQL with the new account name, they may cause a login error without refreshing the user authorization form. This is because after the user has set up the account, only the authorization table is reloaded to make the authorization table previously set in effect. Use the FLUSH privileges command to overload the authorization table. Also, it is important to note that only users with full permissions, such as "root", can execute this command. After the user overloads the authorization table, log out of MySQL with the newly created user name after exiting MySQL.

Frequently asked questions about MySQL database security technology when permission changes take effect

The server automatically reads the grant table when the MySQL server is started and when the grant and REVOKE statements are used. However, since we know where these permissions are stored and how they are saved, you can modify them manually. When they are manually updated, the MySQL server will not notice that they have been modified.

We have to indicate to the server that the permissions have been modified and there are 3 ways to accomplish this task. You can type the following command at the MySQL command prompt (you must be logged in as an administrator):

Flush privileges;

Mysqladmin flush-privileges

Mysqladmin Reload

Thereafter, when the user next connects, the system checks for global-level permissions, and when the next command is executed, the database-level permissions are checked, and the table-level and column-level permissions are checked at the next request of the user.

Set Account password

(1) You can specify a password on the command line with the mysqladmin command:

Mysqladmin-u user_name-h host_name Password "newpwd"

mysqladmin -u root  password "newpass" mysqladmin -u root  password oldpass  "newpass"

The mysqladmin command resets the user's password to host_name and the user name is user_name, and the new password is "Newpwd".

(2) Set the user's password via the Set password command:

Set password for ' Jeffrey ' @ ' localhost ' =password (' biscuit ');

You can change the password of another user only if you are logged in as the root user (the user who can update the MySQL database). If you are not connected as an anonymous user, you can change your password by omitting the FOR clause:

Set Password=password (' Biscuit ');

(3) Use the Grant usage statement (*. *) at the global level to specify the password for an account without affecting the current permissions of the account:

GRANT USAGE on *. Jeffrey ' @ ' localhost ' identified by ' biscuit ';

(4) To establish a password when creating a new account, to provide a specific value for the password column:

Mysql-uroot-p MySQL--making use of MySQL database

INSERT into User (Host, user, Password) VALUES (' localhost ', ' Jeffrey ', PASSWORD (' biscuit '));

FLUSH privileges;

(5) To change the password for an existing account, to apply the UPDATE statement to set the password column value:

Mysql-uroot-p MySQL

UPDATE user SET Password=password (' bagel ') WHERE host= ' localhost ' and user= ' Francis ';

FLUSH privileges;

Ps:

1. When using Set PASSWORD, insert, or update to specify the password for an account, it must be encrypted with the PASSWORD () function. (The only exception is if the password is empty, you do not need to use password ()). Password () is used because the user table saves the password in an encrypted manner, not in plaintext. If you set a password in a way that is not encrypted, the password value used by the connection is encrypted and compared with the password saved in the user table. However, the saved value is clear text, so the comparison fails and the server rejects the connection.
2. If you use Grant ... The identified by statement or mysqladmin Password command sets the password, which automatically encrypts the password. In this case, you do not need to use the password () function to encrypt the password.

Make passwords more secure

(1) At the management level, you must not grant access to any non-managed accounts for the Mysql.user table.

(2) Use the following command mode to connect to the server to hide your password. The command is as follows:

Mysql-uroot-p db_name

Enter password:********

The "*" character specifies where the password is entered and the password entered is not visible. Because it is not visible to other users, it is more secure to enter your password than to specify it on the command line.

(3) If you want to run a script from a non-interactive mode to invoke a client, there is no chance of entering the password from the terminal. The safest way to do this is to have the client program prompt for a password or specify a password in the appropriate protected options file.

User rights for MySQL