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

How to achieve user account management and rights management in MySQL, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The authority system of mysql is roughly divided into five levels:

Global level

Global permissions apply to all databases in a given server. These permissions are stored in the mysql.user table. GRANT ALL ON *. * and REVOKE ALL ON *. * only grant and revoke global permissions.

Database level

Database permissions apply to all targets in a given database. These permissions are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* only grant and revoke database permissions.

Surface level

Table permissions apply to all columns in a given table. These permissions are stored in the mysql.talbes_ private table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name only grant and revoke table permissions.

Column level

Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_ private table. When using REVOKE, you must specify the same column as the authorized column.

Subroutine level

CREATE ROUTINE, ALTER ROUTINE, EXECUTE and GRANT permissions apply to stored subroutines. These permissions can be granted at the global and database levels. Moreover, in addition to CREATE ROUTINE, these permissions can be granted at the subroutine level and stored in the mysql.procs_ private table.

This permission information is stored in the following system table:

Mysql.user

Mysql.db

Mysql.host

Mysql.table_priv

Mysql.column_priv

When the user connects in, mysqld validates the user's permissions through the above tables!

I. access to the right list

In the two processes of permission access, the system will use the three most important permission tables, user, host and db, in the "mysql" database (created when installing MySQL and the database name is "mysql").

Of the three tables, the most important table represents the user table, followed by the db table, and the host table is not used in most cases.

The columns in user are mainly divided into four parts: user column, permission column, security column, and resource control column.

User columns and permissions columns are commonly used, in which permissions columns are divided into general permissions and administrative permissions. Ordinary permissions are used for database operations, such as select_priv, super_priv, and so on.

When a user connects, the access process of the permission table has the following two processes:

First, from the host, user and password fields in the user table, determine whether the connected IP, user name, and password exist in the table, and if so, pass authentication, otherwise reject the connection.

If authenticated, the database permissions are obtained in the following order: user-> db-> tables_priv-> columns_priv.

In these permission tables, the scope of permissions decreases in turn, and the global permissions cover the local permissions. The first stage above is easy to understand. Let's explain the second stage in detail with an example.

To facilitate testing, you need to modify the variable sql_mode, otherwise an error will be reported, as follows

MySQL [(none)] > grant select on *. * to xxx@localhost;ERROR 1133 (42000): Can't find any matching row in the user tableMySQL [(none)] > SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 2 warnings (0.07 sec) MySQL [(none)] > grant select on *. * to xxx@localhost NO_AUTO_CREATE_USER is included in the default value of Query OK, 0 rows affected, 2 warnings (0.10 sec) / / sql_mode (prevents GRANT from automatically creating new users unless a password is also specified) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

1. Create a user

Xxx@localhost and grant select permissions to all tables on all databases

First check the permission status displayed in the user table

MySQL [mysql] > select * from user where user= "xxx" and host='localhost'\ G * * 1. Row * * Host: localhostUser: xxxSelect_priv: YInsert_priv: NUpdate_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db _ priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: NCreate_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native _ passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 17:34:49password_lifetime: NULLaccount_locked: n

Then check the permission status of the db table

MySQL [mysql] > select * from db where user= "xxx" and host='localhost'\ Gerrand empty set (0.03 sec)

You can find Select_priv: y in the user table, and the others are all N

There is no record in DB table

That is, users who have the same permissions on all databases do not need to log to the db table, but just need to change the select_priv in the user table to "Y". In other words, each permission in the user table represents permissions for all databases.

two。 Change the permissions on xxx@localhost to select permissions only for all tables on the db1 database.

MySQL [mysql] > create database db1;Query OK, 1 row affected (0.01 sec) MySQL [mysql] > re ^ C MySQL [mysql] > revoke select on *. * from xxx@localhost;Query OK, 0 rows affected, 1 warning (0.06 sec) MySQL [mysql] > grant select on db1.* to xxx@localhost;Query OK, 0 rows affected, 1 warning (0.09 sec) MySQL [mysql] > select * from user where user='xxx'\ G * * 1. Row * * Host: localhostUser: xxxSelect_priv: NInsert_priv: NUpdate_priv: NCreate_priv: NDrop_priv: NReload_priv: NMySQL [mysql] > select * from db where user='xxx'\ G * * 1. Row * * Host: localhostDb: db1User: xxxSelect_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate_view _ priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N

At this point, it is found that the select_priv in the user table becomes "N", while the db table adds a record with db as xxx. That is, when only part of the database is granted certain permissions, the corresponding permission column in the user table remains "N" and the specific database permissions are written to the db table. The permission mechanisms of table and column are similar to db.

3. Tables_priv record table permissions

MySQL [db1] > create table T1 (id int (10), name char (10)); Query OK, 0 rows affected (0.83 sec) MySQL [db1] > grant select on db1.t1 to mmm@localhost;Query OK, 0 rows affected, 2 warnings (0.06 sec) MySQL [mysql] > select * from user where user='mmm'\ G * * 1. Row * * Host: localhostUser: mmmSelect_priv: NInsert_priv: NUpdate_priv: NCreate_priv: NDrop_priv: NReload_priv: N...MySQL [mysql] > select * from db where user='mmm'\ G Empty set (0.00 sec) MySQL [mysql] > select * from tables_priv where user='mmm'\ G * * 1. Row * * Host: localhostDb: db1User: mmmTable_name: t1Grantor: root@localhostTimestamp: 0000-00-0000: 00:00Table_priv: SelectColumn_priv: 1 row in set (0.00 sec) ERROR: No query specifiedMySQL [mysql] > select * from columns_priv where user='mmm'\ Gempty set (0.00 sec)

You can see that a record has been added to the tables_priv table, but there is no record in the three user db columns_priv tables

As can be seen from the above example, when a user passes permission authentication and assigns permissions, the permissions will be assigned in the order of = = user-> db-> tables_priv-> columns_priv==, that is, check the global permissions table user first. If the corresponding permission in user is "Y", then the user's permissions to all databases are "Y", and db, tables_priv and columns_priv will no longer be checked. If it is "N", check the specific database corresponding to this user in the db table, and get the permission of "Y" in db; if the corresponding permission in db is "N", then check the permissions in tables_priv and columns_priv in turn, and if all are "N", it is judged that there is no permission.

two。 Account management

Authorized grant

Grant can be used not only to authorize, but also to create users.

Syntax for authorization:

Grant permission list on library name. Table name to user @ host identified by 'password'

Create user p1 with permissions that can execute all permissions on all databases and can only connect locally

MySQL [mysql] > grant all privileges on *. * to p1@localhost Query OK, 0 rows affected 2 warnings (0.03 sec) MySQL [mysql] > select * from user where user='p1'\ Graph * 1. Row * * Host: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv : YFile_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject Max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 18:11:01password_lifetime: NULLaccount_locked: N1 row in set (0.00 sec)

Except for grant_priv permissions, all permissions are "Y" in the user table.

Increase grant permissions for p1

MySQL [mysql] > grant all privileges on *. * to p1@localhost with grant option Query OK, 0 rows affected 1 warning (0.03 sec) MySQL [mysql] > select * from user where user='p1'\ gateway * 1. Row * * Host: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv : YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject Max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: password_expired: Npassword_last_changed: 2018-12-03 18:11:01password_lifetime: NULLaccount_locked: N1 row in set (0.00 sec)

Set password to give grant permission

MySQL [mysql] > grant all privileges on *. * to p1@localhost identified by '123' with grant option Query OK, 0 rows affected 2 warnings (0.01 sec) MySQL [mysql] > select * from user where user='p1'\ gateway * 1. Row * * Host: localhostUser: p1Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YReload_priv: YShutdown_priv: YProcess_priv : YFile_priv: YGrant_priv: YReferences_priv: YIndex_priv: YAlter_priv: YShow_db_priv: YSuper_priv: YCreate_tmp_table_priv: YLock_tables_priv: YExecute_priv: YRepl_slave_priv: YRepl_client_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YCreate_user_priv: YEvent_priv: YTrigger_priv: YCreate_tablespace_priv: Yssl_type: ssl_cipher: x509_issuer: x509_subject : max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired: Npassword_last_changed: 2018-12-03 18:14:40password_lifetime: NULLaccount_locked: N1 row in set (0.00 sec)

In the database after version 5.7, the password field is changed to authentication_string

Create a new user p2, which can connect from any IP with the permission to perform select, update, insert and delete operations on all tables in the db1 database. The initial password is "123".

MySQL [mysql] > grant select,insert,update,delete on db1.* to 'p2clients%' identified by '123 investors' query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql] > select * from user where user='p2'\ G * * 1. Row * * Host:% User: p2Select_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: N...Create_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject : max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired: Npassword_last_changed: 2018-12-03 18:20:44password_lifetime: NULLaccount_locked: N1 row in set (0.00 sec) ERROR: No query specifiedMySQL [mysql] > select * from db where user='p2'\ G * * 1. Row * * Host:% Db: db1User: p2Select_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: NDrop_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NCreate_tmp_table_priv: NLock_tables_priv: NCreate _ view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NExecute_priv: NEvent_priv: NTrigger_priv: N1 row in set (0.00 sec)

The permissions in the user table are all "N", and the record permissions added in the db table are all "Y", so that only the appropriate permissions are granted to the user, not too many permissions.

The IP in this example is limited to all IP can be connected, so it is set to "%", the mysql database is controlled by the host field of the user table, and host can be the following type of assignment.

Note: the value of host in the user table of the mysql database is "%" or empty, indicating that all external IP can be connected, but does not include the local server localhost, so if you want to include the local server, you must grant permissions to localhost separately.

Grant super, process, file permissions to user p3percent

MySQL [mysql] > grant super,process,file on db1.* to'p3 subscription% error 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGESMySQL [mysql] > grant super,process,file on *. * to'p3 subscription% alternative query OK, 0 rows affected (0.03 sec)

These permissions belong to administrative permissions, so you cannot specify a database. On must be followed by *. *, otherwise an error will be prompted, as shown above.

So what are these permissions for?

Process with this permission, users can execute SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but only the processes of this user can be queried.

Only with file permission can you execute select.. into outfile and load data infile... Operation, but do not grant file, process, super permissions to accounts other than the administrator, which has serious security risks.

Super this permission allows the user to terminate any query; modify the SET statement of global variables; use CHANGE MASTER,PURGE MASTER LOGS

The other one is special.

Usage permission

Connect (login) permission, establish a user, will automatically grant its usage permission (default grant).

This permission can only be used to log in to the database and cannot perform any operation, and the usage permission cannot be reclaimed, that is, the ``REVOKE user cannot delete the user.

View account permissions

After the account is created, you can view the permissions by using the following command:

Show grants for user@host;MySQL [mysql] > show grants for p2 percent%' +-- + | Grants for p2percent% | + -+ | GRANT USAGE ON *. * TO 'p2percent percent' | | GRANT SELECT INSERT, UPDATE, DELETE ON `db1`. * TO 'p2clients%%' | +-+ 2 rows in set (0.00 sec)

Change account permissions

Create a user account p4 with select permission for all db1 tables

MySQL [mysql] > grant select on db1.* to p4 employees% query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql] > show grants for p4 employees%' +-- + | Grants for p4 percent% | +-+ | GRANT USAGE ON *. * TO 'p4percent percent%' | | GRANT SELECT ON `db1`. * TO 'p4percent percent percent' | +- -- + 2 rows in set (0.00 sec)

Increase delete permissions

MySQL [mysql] > grant delete on db1.* to p4 employees% query OK, 0 rows affected (0.01 sec) MySQL [mysql] > show grants for p4 employees%' +-+ | Grants for p4 percent% | +-+ | GRANT USAGE ON *. * TO'p4 percent percent'| GRANT SELECT DELETE ON `db1`. * TO 'p4subscription%' | +-+ 2 rows in set (0.00 sec)

Merge with existing select permissions

Delete delete permission

The revoke statement can reclaim the permissions that have been granted

MySQL [mysql] > show grants for p4 percent%' +-+ | Grants for p4 percent% | +-+ | GRANT USAGE ON *. * TO'p4 percent percent'| GRANT SELECT DELETE ON `db1`. * TO'p4 percent percent'| +-+ 2 rows in set (0.00 sec) MySQL [mysql] > revoke delete on db1.* from p4 percent percent' Query OK, 0 rows affected (0.01 sec) MySQL [mysql] > show grants for p4 percent%' +-- + | Grants for p4 percent% | +-+ | GRANT USAGE ON *. * TO 'p4percent percent%' | | GRANT SELECT ON `db1`. * TO 'p4percent percent percent' | +- -- + 2 rows in set (0.00 sec)

Can usage revoke?

MySQL [mysql] > revoke select on db1.* from p4 employees% query OK, 0 rows affected (0.02 sec) MySQL [mysql] > show grants for p4 employees%' +-- + | Grants for p4 percent% | +-+ | GRANT USAGE ON *. * TO'p4 percent percent'| +-- + 1 row in set (0.00 sec) MySQL [mysql] > revoke usage on db1.* from p4 percent%' ERROR 1141 (42000): There is no such grant defined for user 'p4' on host'%'

Usage permissions cannot be reclaimed, that is, revoke users cannot delete users.

To completely delete an account, you can use drop user

Drop user p4 percent%'

Account resource limit

When creating a MySQL account, there is another type of option called account resource limit, which limits the actual resource limit of each account. The "resources" here mainly include:

Max_queries_per_hour count: the number of queries executed per hour by a single account

Max_upodates_per_hour count: the number of updates per hour performed by a single account

Max_connections_per_hour count: the number of times a single account connects to the server per hour

Max_user_connections count: the number of times a single account connects to the server concurrently

Note:

Add users or permissions, and use mysql > flush privileges; to refresh permissions

After reading the above, have you mastered how to achieve user account management and rights management in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.