MySQL User and permission management

MySQL Users and permission management in order to prevent the crawlers of unscrupulous websites from crawling articles, we hereby mark and repost the articles with the source. LaplaceDemon/SJQ

MySQL permission system overview

MySQL permission information is stored in several system tables called grant tables, namely mysql. User, mysql. db, mysql. Host, mysql. table_priv, and mysql. column_priv.

The volume of permission information is small and frequently accessed. Therefore, every time MySQL is started, all permission information is loaded into the memory and saved in several specific structures. Run the "flush privileges" command every time you manually modify the permission table.

Use the GRANT command, REVOKE command, creater user command, and drop user command to modify USER-related permissions. when modifying grant tables, the permission information in the memory is also modified.

Try to use commands such as GRANT, REVOKE, creater user, and drop user.

User Management

Creater user command to create a USER

Before creating a USER, you must have the global create user permission or the INSERT permission for the mysql database. For each account, create user creates a record in the mysql. user table without permission. If the account does not exist, an error occurs. Use the self-selected identified by clause to set the user password.

The user created by this command has no special permissions and only the initial USAGE permission.

Create user user [idenhtity by [PASSWORD 'password'] [, user [idenhtity by [PASSWORD 'password']

Drop user command to delete USER

The idle account should be deleted. The drop user command cannot automatically close any opened USER conversations. if a USER has opened a dialog, the command does not take effect until the USER dialog is closed. Once the dialog is disabled and the user is canceled, the user will fail to log on again.

Drop user user [, user]...

RENAME USER

If the old account does not exist or the new account already exists, an error occurs.

Rename user old_user TO new_user [, old_user TO new_user]...

Permission management

To authorize a user to use the GRANT command, remove the permissions of a user to use the REVOKE command. Another brute force method is to directly update the grant tables system table.

When authorizing a user, you must specify the user name and host. Format: 'username' @ 'hostname '. If only the user name is specified during authorization, MySQL automatically considers it as authorizing 'username' @ '%. To remove the permissions of a user, you also need to specify the host.

View permissions of a user

Method 1:

Show grants for 'username' @ 'hostname'

Method 2:

Query the permission information of grant tables.

GRANT and REVOKE commands

GRANT and REVOKE commands are used to manage access permissions, and can also be used to create and delete users. However, in MySQL5.0.2, greate user and drop user commands can be used for easier implementation.

The GRANT and REVOKE commands provide multi-level control over who can operate the server and its content, from who can shut down the server to who can modify information in specific table fields.

Description

SELECT table, columns allow users to SELECT rows (records) from the table)

INSERT table, columns allow users to INSERT new rows in the table

UPDATE table, columns allow users to modify values in rows in the existing table

DELETE table allows you to DELETE rows in an existing table

The INDEX table allows you to create and drag a specific table INDEX.

Alter table allows you to change the structure of an existing table. For example, you can add a column, rename a column or table, and modify the data type of a column.

CREATE database. tables allow users to CREATE new databases or tables. If a specific database or table is specified in GRANT, they can only create the database or table, that is, they must first delete (Drop) it.

DROP database, table allows users to drag (delete) database or table

Administrator permission Description

Create temporary tables allows administrators to use the TEMPORARY keyword in the create table statement.

FILE allows you to read data from a FILE to a table or from a table to a FILE.

Lock tables allow the use of the lock tables statement

PROCESS allows the administrator to view the server processes of all users

RELOAD allows the administrator to RELOAD the authorization table, clear the authorization, host, log and table

Replication client allows the use of show status on the REPLICATION host (Master) and Slave (Slave)

Replication slave allows replication slave servers to connect to the master server

Show databases allows you to use the show databases statement to view the list of all DATABASES. Without this permission, users can only see the databases they can see.

SHUTDOWN allows the administrator to shut down the MySQL server

SUPER allows the administrator to close threads belonging to any user

Special permission Description

ALL (or all previleges) grant ALL permissions

USAGE does not grant permissions. This creates a user and allows him to log on, but does not allow other operations, such as update/select.

Authorization level

MySQL has five levels of permissions:

Global Level)

Database Level)

Table Level)

Column Level)

Subroutine Level)

Permission effective

When the MySQL database server is started and the GRANT and REVOKE statements are used, the server automatically reads the grant table. You can also manually modify them.

When you update them manually, the MySQL server will not notice that they have been modified. You must indicate to the server that the permission has been modified. There are three ways to implement this task. Run the following command at the MySQL command prompt (which must be entered as an administrator:

Flush privileges;

Or use it in a shell environment

Mysqladmin flush-privileges

Or

Mysqladmin reload

After that, when the database is connected again, the system checks the global permissions. when the next command is executed, the system checks the database-level permissions; the table-level and column-level permissions will be checked at the user's next request.

Set account password

You can use the mysqladmin command to specify the password:

Mysqladmin-u user_name-h host_name password "newpwd"

Mysqladmin resetting server host_name, username password, new password is "newpwd ".

Set password:

Only specific users (users who can update the mysql database, such as the root user) can log on to change the passwords of other users.

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

If anonymous users are not connected, you can omit the for clause and modify your password:

Set password = password ('biscuit ');

At the global level, use the grant usage statement (*. *) to specify the password of an account, without affecting the account's current permissions:

Grant usage on *. * TO 'Jeffrey '@' % 'identified by 'biscuit ';

When creating a new account, you must provide a specific value for the password column:

Mysql-u root mysql

Insert into user (Host, User, Password) VALUES ('%', 'Jeffrey ', PASSWORD ('biscuit ''));

Flush privileges;

To change the password of an existing account, apply the UPDATE statement to set the password column value.

Mysql-u root mysql

UPDATE user SET Password = PASSWORD ('bagel') WHERE Host = '%' AND User = 'Francis ';

Flush privileges;

When setting the PASSWORD, use set password, INSERT, UPDATE to SET the PASSWORD, and use the PASSWORD () function to encrypt the PASSWORD.

If you use the GRANT.. identified by statement or the mysqladmin password command to set the PASSWORD, these commands will automatically encrypt the password without using the PASSWORD () function.

Pay attention to security

At the management level, the access permission of the mysql. user table cannot be granted to any non-management account.

In order to prevent crawlers from crawling articles on unscrupulous websites, we hereby mark and repost the source of the article. LaplaceDemon/SJQ