MySQL User and Rights management

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. Laplacedemon/sjq

MySQL permissions system table

MySQL's permissions-related information is primarily stored in Mysql.user,mysql.db,mysql.host,mysql.table_priv and Mysql.column_priv. These tables are called Grant tables.

User ManagementCreater user command to create users

The create user must first have the global create user permission for the MySQL database or the Insert permission. For each account, create user creates a record in the Mysql.user table with no permissions. If the account does not exist, an error occurs. Use the optional identified by clause to set the user password.

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

CREATE user User [identified by] [PASSWORD ' PASSWORD ']][, USER [identified by [ PASSWORD ' PASSWORD ']

Drop user command to delete users

Inactive accounts should be deleted. The DROP user cannot automatically close any open user conversations, and if the user has an open conversation and cancels the users at this point, 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.

DROP USER User[,user] ...

RENAME User renaming users

An error occurs when the old account does not exist or the new account already exists.

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

Rights Management

To authorize a user to use the grant command, use the REVOKE command to remove a user's permissions. There is also a method of violence that directly updates the grant tables system table.

When authorizing a user, you need to specify the user name and host. Format:' username ' @ ' hostname '.

When authorizing, if only the user name is specified, MySQL automatically considers the ' username ' @ '% ' authorization. To remove a user's permissions, you also need to specify a host.

To view a user's permissions

Method One:

 for ' username ' @'hostname'

Method Two:

Query permission information for grant tables.

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 MySQL5.0.2 can be easily implemented with the Greate 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.

Permissions for normal users are applied to the description

Select table, column allows the user to choose Rows from a table (record)

Insert table, column allows users to insert new rows into a table

Update table, column allows the user to modify values in existing table rows

Delete table allows the user to delete rows from an existing table

Index Table allows users to create and drag specific table indexes

The ALTER TABLE allows the user to change the structure of an existing table. For example, you can add columns, rename columns or tables, modify the data type of a column

Create database, which allows users to make 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 user to drag (delete) a database or table

Administrator rights Permission Description

Create temporary tables allows the administrator to use the TEMPORARY keyword in the CREATE TABLE statement

File allows data to be read from files into a table or read into a file from a table

Lock tables allows the use of the lock tables statement

Process allows administrators to view server processes that belong to all users

Reload allows administrators to reload authorization tables, clear authorizations, hosts, logs, and tables

REPLICATION Client allows you to use show STATUS on replication host (master) and slave (Slave)

REPLICATION slave allow replication to connect to the primary server from the server

Show databases allows you to view all the database lists using the show databases statement. Without this permission, users can only see the database they can see

Shutdown allow administrators to shut down MySQL server

Super allows administrators to close threads belonging to any user

Special permission 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

The permissions in MySQL are divided into 5 levels:

Global level

Database hierarchy (Databases level)

Surface layer (Table level)

Column level (rank)

Sub-Program hierarchy (Routine level)

Permissions take effect

Permissions information is small and frequently accessed, so every time you start MySQL, all of the permissions are loaded into memory and stored in a few specific structures. With the grant command, the REVOKE command, and the creater user command, the DROP User command modifies the user-related permissions, while modifying the grant tables also modifies the in-memory permissions information.

Each time you manually modify the related permissions table, you need to execute the "FLUSH privileges" command. So try to use Grant,revoke,creater User,drop user and other commands.

When the MySQL database server starts, and when you use the GRANT and REVOKE statements, the server automatically reads the grant table. At the same time, you can modify them manually.

When they are manually updated, the MySQL server will not notice that they have been modified. You must indicate to the server that permissions have been modified and there are 3 ways to do this. You can type the command at the MySQL command prompt (which must be entered as an administrator):

privileges;

Or use in a shell environment

?

1	mysqladmin flush-privileges

Or

?

1	mysqladmin reload

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

Set Account password

You can specify a password with the mysqladmin command:

- user_name - host_name Password "newpwd"

Mysqladmin Reset the server host_name, and the user name is user_name user's password, the new password is "Newpwd".

The Set password command sets the user password:

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

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

If you do not connect as an anonymous user, you can modify your password by omitting the FOR clause:

Setpassword ('biscuit');

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

GRANT  on *. *  to ' Jeffrey '@ '%' by'biscuit';

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

- u root MySQL INSERT  into User (Host,UserVALUES ('%','Jeffrey', PASSWORD ('biscuit'privileges;

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

Mysql-u root MySQLUPDATE User SETPassword=PASSWORD ('Bagel')WHEREHost= '%'  and User ='Francis'; FLUSHPrivileges;

When setting a password, use set Password,insert,update to set the password and use the PASSWORD () function to encrypt the password.

If you use Grant: The identified by statement or mysqladmin Password command sets the password, which automatically encrypts the password without the need to use the password () function.

Safety

At the administrative level, access to the Mysql.user table must not be granted to any non-managed accounts.

In order to prevent unscrupulous site crawler crawling articles, hereby identified, reproduced please indicate the source of the article. Laplacedemon/sjq