Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of users and permissions in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL users and permissions

When you connect to the MySQL server and execute the query, your identity is verified and your activity is authorized.

L authentication: verify the identity of the user. This is the first phase of access control. The identity must be successfully verified each time you connect. If the authentication fails, you cannot connect and your client will be disconnected.

L Authorization: verify the permissions of the user. This is the second phase of access control for each request for an active connection that has been successfully authenticated. For each request, MySQL determines the action you want to perform and then checks that you have the appropriate permissions to perform the operation.

You can view information such as user permissions by querying the mysql.user table, for example:

Mysql > SELECT * FROM mysql.user\ G

Mysql > SELECT user, host, password FROM mysql.user WHERE user='root'

When you connect to the MySQL server using the native password authentication plug-in (mysql_native_password, the default authentication mechanism), the specified user name, the host that issued the connection, and the password are matched with the rows in the mysql.user table to determine whether you can connect and perform the operation.

To connect to the local server using the mysql client, specify the user name and password of the account you want to use:

Shell > mysql-u-p

Note that the hostname associated with your user in the mysql.user table refers to the name of the host that initiated the connection (not the server host). To connect to a server that is not installed on your client's local host, provide the hostname of the server you want to connect to:

Shell > mysql-u-p-h

1.1. Create users and set passwords

1) create a user

Provide users and hosts for each user account. For example, use the CREATE USER...IDENTIFIED BY statement

CREATE USER 'jim'@'localhost' IDENTIFIED BY' Abc123'

Avoid possible security risks when creating accounts: do not create accounts without passwords, do not create anonymous accounts, and, where possible, avoid using wildcards when specifying account hostnames.

The account name includes the user name and the name of the client host from which the user must connect to the server. The account name is in the format 'user_name'@'host_name'. The user name can be up to 16 characters long. If the user name and hostname contain special characters, such as dashes, you must enclose them in single quotation marks. If a value is valid without quotation marks, quotation marks are optional. However, quotation marks can be used in any case. Example of allowed hostname format:

Hostname: localhost

Qualified hostname: 'hostname.example.com'

IP number: 192.168.9.78

IP address: 10.0.0.0amp 255.255.255.0

Pattern or wildcard:% or _

Example of user name and hostname:

John@10.20.30.40

John@'10.20.30.%'

John@'%.ourdomain.com'

John@'10.20.30.0/255.255.255.0'

Alternatively, the GRANT statement can create a new account or modify an existing account. For more information, please see "Grant permissions".

2) set password

There are several ways to set the MySQL user password:

-CREATE USER...IDENTIFIED BY

-GRANT...IDENTIFIED BY

-SET PASSWORD

-mysqladmin password

-UPDATE Authorization form (not recommended)

Assign a unique strong password to all user accounts.

Avoid passwords that can be easily guessed.

Use the following SELECT statement to list all accounts without a password:

SELECT Host,User FROM mysql.user WHERE Password =''

Determine the duplicate password:

SELECT User FROM mysql.user GROUP BY password HAVING count (user) > 1

Invalidate the password:

ALTER USER jim@localhost PASSWORD EXPIRE

MySQL uses several algorithms to encrypt passwords stored in the user table:

The mysql_native_password plug-in implements a standard password format: a 41-byte hash.

The mysql_old_password plug-in implements the older format, which is less secure and 16 bytes wide.

The sha256_password plug-in implements the SHA-256 hash algorithm that is widely used in security computing.

The value of the old_passwords system variable specifies the algorithm that the PASSWORD () function uses to create the password, as follows:

Standard algorithm, same as that used in MySQL 4.1.1 and later

1: the old algorithm, which is the same as the algorithm used in MySQL 4.1.1

2: SHA-256 algorithm

Set the default-authentication-plugin option to sha256_password when starting the server, you can use the SHA-256 password for all new users, or you can use CREATE USER and IDENTIFIED WITH

The sha256_password clause specifies the SHA-256 password for a specific user. For further information about the sha256_password plug-in, visit

Http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html

3) manage users

Use the RENAME USER statement to rename a user account, that is, to change the account name of an existing account, to change the user name or hostname portion of the account name, or both

Mysql > RENAME USER 'jim'@'localhost' TO' james'@'localhost'

Use the DROP USER statement to delete a user account; revoke all permissions on an existing account, and then delete the account while deleting all records for the account from any authorization table in which the account exists

Mysql > DROP USER 'jim'@'localhost'

4) about the password authentication plug-in

a. Client-side plaintext verification plug-in

Some authentication methods, such as PAM (Pluggable Authentication Module, pluggable Authentication Module) authentication, require the client to send a plain text password to the server so that the server can handle the normal form of password. The mysql_clear_password plug-in supports this behavior.

There is a built-in plaintext validation plug-in mysql_clear_password in the MySQL client library. The plug-in can be used to send plain text passwords to the server, which are usually hashed.

Enable in the following ways:

Method 1: LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN environment variable, which is specified when running MySQL client applications (such as mysql and mysqladmin)-- enable-cleartext-plugin

Method 2: the MYSQL_ENABLE_CLEARTEXT_PLUGIN option of the mysql_options () C API function

b. Loadable verification plug-in

Angular test_plugin_server: implements native and old password authentication, and this plug-in uses auth_test_plugin.so files. The test verification plug-in (test_plugin_server) uses native or old password authentication for authentication and is suitable for testing and development.

Angular auth_socket: only MySQL users logged in from a UNIX account with the same name through the UNIX socket are allowed, and this plug-in uses the auth_socket.so file. The socket Peer credential (auth_socket) plug-in allows users to connect through UNIX socket files only if their Linux sername matches their MySQL account.

·authentication_pam: allows login using an external authentication mechanism, and this plug-in uses the authentication_pam.so file. The PAM Authentication plug-in (authentication_pam) is an enterprise version plug-in that allows you to log in using an external authentication mechanism. MySQL does not store your password, but uses the UNIX PAM (Pluggable Authentication Module) mechanism to transfer the username and password provided by the client for authentication by the operating system.

To load the loadable validation plug-in, use the plugin-load option on the command line or in the my.cnf file when the server starts, as shown in the following example:

[mysqld]

Plugin-load=authentication_pam.so

Alternatively, you can develop your own validation plug-in. Test verification plug-ins are suitable for developers to create their own plug-ins; the source code is distributed with the MySQL source code.

C. PAM authentication plug-in

After a brief introduction to a variety of plug-ins, let's focus on the PAM authentication plug-in, which is an enterprise plug-in that validates MySQL accounts to the operating system. PAM defines the service for configuration verification. These services are stored in / etc/pam.d, and the plug-in also authenticates against operating system users and groups, external authentication (such as LDAP)

To create a MySQL user that uses PAM authentication:

Mysql > CREATE USER user@host IDENTIFIED WITH authentication_pam AS 'pam_service, os_group=mysql_user'

PAM looks in / etc/pam.d for the service it authenticates. For example, to create a PAM service mysql-pam, you can use the following when creating the file / etc/pam.d/mysql-pam:

#% PAM-1.0

Auth include password-auth

Account include password-auth

In addition to MySQL authentication, PAM integrates with other authentication methods, including LDAP and Active Directory, so you can use PAM to validate many services (including MySQL) to a single storage in the network. To create a MySQL user that maps directly to an operating system user, use the following statement:

Mysql > CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam'

When bob logs in, MySQL passes the user name and password received from the client to PAM, which authenticates to the operating system. The client must send the password in clear text. Enable the client-side plaintext authentication plug-in to do this:

Shell > mysql-- enable-cleartext-plugin-ubob-p

Enter password: bob's_OS_password

To enable group-based login using the PAM authentication plug-in, create an anonymous PAM-enabled proxy account that does not match any user, but specifies a set of mappings from operating system groups to MySQL users:

Mysql > CREATE USER'@ 'IDENTIFIED WITH authentication_pam AS' mysql-pam, sales=m_sales, finance=m_finance'

The above example assumes that you have sales and finance operating system groups as well as m_sales and m_finance MySQL users. Then, the anonymous proxy account must be granted PROXY permission to log in as m_sales and m_finance MySQL:

GRANT PROXY ON m_sales@localhost TO'@'

GRANT PROXY ON m_finance@localhost TO'@'

Users who are members of the sales and finance groups can now have all the permissions granted to these accounts by providing their operating system credentials at the mysql command line prompt and logging in as m_sales or m_finance MySQL. For example, if peter is a member of the sales group, you can log in in the following ways:

Shell > mysql-- enable-cleartext-plugin-upeter-p

Enter password: peter's_OS_password

Welcome to the MySQL monitor. Commands end with; or\ g.

...

Mysql > SELECT CURRENT_USER ()

+-+

| | CURRENT_USER () |

+-+

| | m_sales@localhost |

+-+

1 row in set (0.01 sec)

1.2. Permission list

Privilege

Meaning and Grantable Levels

ALL [PRIVILEGES]

Grant all privileges at specified access level except GRANT OPTION

ALTER

Enable use of ALTER TABLE. Levels: Global,database,table.

ALTER ROUTINE

Enable stored routines to be altered or dropped. Levels: Global,database,procedure.

CREATE

Enable database and table creation. Levels: Global,database,table.

CREATE ROUTINE

Enable stored routine creation. Levels: Global,database.

CREATE TABLESPACE

Enable tablespaces and log file groups to be created,altered,or dropped. Level: Global.

CREATE TEMPORARY TABLES

Enable use of CREATE TEMPORARY TABLE. Levels: Global,database.

CREATE USER

Enable use of CREATE USER,DROP USER,RENAME USER,and REVOKE ALL PRIVILEGES. Level: Global.

CREATE VIEW

Enable views to be created or altered. Levels: Global,database,table.

DELETE

Enable use of DELETE. Level: Global,database,table.

DROP

Enable databases,tables,and views to be dropped. Levels: Global,database,table.

EVENT

Enable use of events for the Event Scheduler. Levels: Global,database.

EXECUTE

Enable the user to execute stored routines. Levels: Global,database,table.

FILE

Enable the user to cause the server to read or write files. Level: Global.

GRANT OPTION

Enable privileges to be granted to or removed from other accounts. Levels: Global,database,table,procedure,proxy.

INDEX

Enable indexes to be created or dropped. Levels: Global,database,table.

INSERT

Enable use of INSERT. Levels: Global,database,table,column.

LOCK TABLES

Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global,database.

PROCESS

Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.

PROXY

Enable user proxying. Level: From user to user.

REFERENCES

Enable foreign key creation. Levels: Global,database,table,column.

RELOAD

Enable use of FLUSH operations. Level: Global.

REPLICATION CLIENT

Enable the user to ask where master or slave servers are. Level: Global.

REPLICATION SLAVE

Enable replication slaves to read binary log events from the master. Level: Global.

SELECT

Enable use of SELECT. Levels: Global,database,table,column.

SHOW DATABASES

Enable SHOW DATABASES to show all databases. Level: Global.

SHOW VIEW

Enable use of SHOW CREATE VIEW. Levels: Global,database,table.

SHUTDOWN

Enable use of mysqladmin shutdown. Level: Global.

SUPER

Enable use of other administrative operations such as CHANGE MASTER TO,KILL,PURGE BINARY LOGS,SET GLOBAL,and mysqladmin debug command. Level: Global.

TRIGGER

Enable trigger operations. Levels: Global,database,table.

UPDATE

Enable use of UPDATE. Levels: Global,database,table,column.

USAGE

Synonym for "no privileges"

1.3. Grant authority

1) GRANT statement

The GRANT statement creates a new account or modifies an existing account. GRANT syntax:

GRANT SELECT ON world_innodb.* TO 'kari'@'localhost' IDENTIFIED BY' Abc123'

The clause of the statement:

-permissions to be granted

-permission level:

-Global: *. *

-Database:. *

-Table:.

-stored procedure:.

-account to be granted permissions

-optional password

2) example of allowed hostname format

Hostname: localhost

Qualified hostname: 'hostname.example.com'

IP number: 192.168.9.78

IP address: 10.0.0.0amp 255.255.255.0

Pattern or wildcard:% or _

Example of user name and hostname:

John@10.20.30.40

John@'10.20.30.%'

John@'%.ourdomain.com'

John@'10.20.30.0/255.255.255.0'

3) Grant administrative rights

The following global permissions apply to administrative users:

L FILE: allows the user to instruct the MySQL server to read and write files in the server host file system

L PROCESS: allows users to use SHOW PROCESSLIST statements to view all statements being executed by the client

L SUPER: allows users to abort other client connections or change the server's runtime configuration; SUPER administrative privileges allow users to perform additional tasks, including setting global variables and terminating client connections.

L ALL: grant all permissions (but not to other users) and grant administrative rights as little as possible, as administrative rights can be abused by malicious or careless users.

Use ALL and ALL PRIVILEGES to grant all permissions (but not to other accounts). Use GRANT ALL... WITH GRANT OPTION grants all permissions (permissions can be granted to other accounts).

L use USAGE to allow connections to the server. This permission creates a record for the account in the user table, but does not have any permissions. You can then use the account to access the server for limited purposes, such as issuing SHOW VARIABLES or SHOW STATUS statements. You cannot use an account to access database content such as tables, but such permissions can be granted later.

Other administrative permissions include CREATE USER, CREATE TEMPORARY TABLES, SHOW DATABASES, LOCK TABLES, RELOAD, and SHUTDOWN. Administrative privileges can be used to break security, access permission data, or perform DDoS attacks on the server. Make sure that only these permissions are granted to the appropriate accounts. For more information about granting MySQL permissions, see the MySQL reference Manual:

Http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html

4) query user permissions

Use SHOW GRANTS statements to display general account permissions, and passwords are stored and displayed in encrypted form.

SHOW GRANTS

SHOW GRANTS FOR CURRENT_USER ()

Mysql > SHOW GRANTS FOR 'kari'@'myhost.example.com'

+-- +

| | Grants for kari@myhost.example.com |

+-- +

| | GRANT FILE ON *. * TO 'kari'@'myhost.example.com' |

| | GRANT SELECT ON `world_ innodb`. * TO 'kari'@'myhost.example.com' |

| | IDENTIFIED BY PASSWORD |

| | * E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' | |

+-- +

SHOW GRANTS shows the statement to recreate permissions for the specified user. This statement displays only the permissions of the account specified in the statement.

If the account has a password, SHOW GRANTS displays an IDENTIFIED BY PASSWORD clause at the end of the GRANT statement that lists the account's global permissions. The word PASSWORD after IDENTIFIED BY indicates that the password value displayed is the encrypted value stored in the user table, not the actual password. Because passwords are stored using one-way encryption, MySQL cannot display unencrypted passwords.

If an account can grant some or all of its permissions to another account, the output displays WITH GRANT OPTION at the end of each GRANT statement to which it applies.

1.4. Permission table

1) permission table

The MySQL installation process creates a permissions table that uses the MyISAM storage engine.

User: contains a record for each account known to the server

Db: database-specific permissions

Tables_priv: table-specific permissions

Columns_priv: column-specific permissions

Procs_priv: stored procedure and function permissions

Each permission table has a host column and a user column to identify the account to which its record applies. During the connection attempt, the server determines whether the client can connect. The server determines whether the client can connect based on the Host, User, and Password columns of the user table. To connect successfully, MySQL must match a record in the user table with the host the client initiated the connection, the user name provided by the client, and the password listed in the matching record.

After connecting, the server determines the access rights for each statement. After the client connects, MySQL checks the access rights of each statement: matching the identity of the account to the Host and User columns of the permissions table.

The permissions in each row of the user table apply globally to the account identified by its Host and User columns.

Permissions in matching records of the db, tables_priv, columns_priv, and procs_priv tables apply at the level identified by the name of a specific permissions table.

For example, permissions in one db record apply to the database specified in that record, but not to other databases.

2) situations that affect permissions

The server reads the authorization table into memory during its startup process and uses a copy in memory to check client access. The server refreshes the in-memory copy of its authorization table in the following cases:

L modifies the user account by issuing account management statements such as CREATE USER, GRANT, REVOKE, or SET PASSWORD.

L explicitly reloads the table by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

Direct changes to the authorization table should be avoided for the following reasons:

The syntax design of the account management statement is clear and simple.

If you make an error in an account management statement, the statement will fail and no settings will be changed.

If you make a mistake when changing the authorization table directly, all users may be locked out of the system.

1.5. Revoke user permissions

Use the REVOKE statement to revoke specific SQL statement permissions:

REVOKE DELETE,INSERT,UPDATE ON world_innodb.*

FROM 'Amon'@'localhost'

Revoke permissions to grant permissions to other users:

REVOKE GRANT OPTION ON world_innodb.*

FROM 'Jan'@'localhost'

Revoke all permissions (including authorization to others):

REVOKE ALL PRIVILEGES,GRANT OPTION

FROM 'Sasha'@'localhost'

Use the SHOW GRANTS statement to determine whether to use the SHOW GRANTS statement before issuing the REVOKE

Revoke the permission, and then reconfirm the result.

1.6. access control

To instruct the server not to read the authorization table and disable access control, use the-- skip-grant-tables option. Each connection was successful:

A) you can provide any user name and any password, and you can connect from any host.

B) this option disables the entire permission system.

C) the connected user actually has all permissions.

D) this option disables account management statements such as CREATE USER, GRANT, REVOKE, and SET PASSWORD.

L block client connections:

-use the-- skip-networking option to block network access and allow access only on local sockets, named pipes, or shared memory.

-use the-- socket option to start the server on a non-standard socket to prevent local applications or users from casually accessing.

If you forget the root password and need to reset it, it is convenient to disable access control because any user can connect with full privileges without providing a password. Obviously, this is very dangerous. To prevent remote clients from connecting through TCP/IP, use the-- skip-networking option. In this way, clients can only connect from localhost using socket files on UNIX or named pipes or shared memory on Windows. To avoid random connections from the local host, use a non-standard socket name at the command prompt.

Account management statements require in-memory copies of the authorization table; these copies are not available when access control is disabled. To change permissions or set passwords, you can modify the authorization table directly. Alternatively, issue a FLUSH PRIVILEGES statement after connecting to the server, which causes the server to read the table and also enables the account management statement.

1.7. Resource restriction

Restrict the use of server resources by setting the global variable MAX_USER_CONNECTIONS to a non-zero value. This limits the number of simultaneous connections to any account, but does not limit what the client can do after connecting.

Limit the following server resources for a single account:

-MAX_QUERIES_PER_HOUR: the number of queries an account can issue per hour

-MAX_UPDATES_PER_HOUR: the number of updates an account can send per hour

-MAX_CONNECTIONS_PER_HOUR: the number of times an account can connect to the server per hour

-MAX_USER_CONNECTIONS: the number of simultaneous connections allowed

To set resource limits for an account, use the GRANT statement and the WITH clause that specifies each resource you want to restrict. The default value for each limit is zero, which means there is no limit. For example, to restrict user francis access to the customer database, issue the following statement:

Mysql > GRANT ALL ON customer.* TO 'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 20

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5

-> MAX_USER_CONNECTIONS 2

Provide resource restrictions in any order in the WITH clause. Setting the MAX_USER_CONNECTIONS limit to 0 sets it to the global default value, indicating that the maximum number of simultaneous connections allowed for this account is the global value of the max_user_connections system variable.

To set the existing limit for any hourly resource to the default Unlimited, specify a value of 0, as shown in the following example:

Mysql > GRANT USAGE ON *. * TO 'quinn'@'localhost'

-> WITH MAX_CONNECTIONS_PER_HOUR 0

1.8. Actual combat of MySQL authority

a. View the permissions of the current user:

Mysql > show grants

b. View the permissions of a user:

Mysql > show grants for 'jack'@'%'

Mysql > show grants for current_user ()

c. Reclaim permission

Mysql > revoke delete on *. * from 'jack'@'localhost'

d. Delete user

Mysql > drop user 'jack'@'localhost'

e. Rename an account

Mysql > rename user 'jack'@'%' to' jim'@'%'

f. Modify the password

I) use the set password command

Mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' 123456')

Ii) use mysqladmin

[root@rhel5] # mysqladmin-uroot-p123456 password 1234abcd

Note:

Format: mysqladmin-u username-p old password password new password

Iii) directly edit the user table with update

Mysql > use mysql

Mysql > update user set PASSWORD = PASSWORD ('1234abcd') where user =' root'

Mysql > flush privileges

Iv) when you lose your root password

[root@rhel5] # mysqld_safe-- skip-grant-tables &

[root@rhel5] # mysql-u root

Mysql > update user set password = PASSWORD ('123456') where user =' root'

Mysql > flush privileges

The above is all the contents of the article "sample Analysis of users and permissions in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.