Example Analysis of accounts and permissions in mysql 01/19 Update SLTechnology News&Howtos

Example Analysis of accounts and permissions in mysql

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Editor to share with you a sample analysis of accounts and permissions in mysql. I hope you will gain something after reading this article. Let's discuss it together.

When a client connects to the MySQL server, it must provide valid authentication, such as a user name and password. When a user performs any database operation, the server verifies that the user has the appropriate permissions, such as SELECT permissions for querying tables and DROP permissions for deleting objects.

In order to facilitate the management of user rights, MySQL 8.0 provides the function of roles. A Role is a collection of permissions.

In this article we discuss the management of accounts and permissions in MySQL.

5.1 manage users

5.1.1 create user

MySQL uses the CREATE USER statement to create a user. The basic syntax is as follows:

CREATE USER [IF NOT EXISTS] account_nameIDENTIFIED BY 'password'

Where account_name is the account name; the account name is divided into two parts: user name (user_name) and hostname (host_name), using% connection. IDENTIFIED BY is used to specify the user's password. IF NOT EXISTS is used to avoid error messages when creating an account with the same name.

The following statement creates a new user dev01 that can be logged in locally (localhost):

Mysql > CREATE USER dev01@localhost IDENTIFIED BY 'Dev01@mysql';Query OK, 0 rows affected (0.21 sec)

The account in MySQL is determined by both the user name and the host name. Dev01 on the host office.example.com and dev01 on the host home.example.com are two accounts. If you do not specify a hostname, the user can log in from any host:

User_nameuser_name@%

% is a wildcard character that represents any string; in addition, _ represents any single character.

If the user name or hostname contains special characters, such as spaces or -, you need to use quotation marks to refer to these two parts respectively:

'user-name'@'host-name'

In addition to single quotes, you can also use backquotes (`) or double quotes (").

The account information in MySQL is stored in the user table of the system database mysql:

With the exception of dev01@localhost, the other four users are initialized system users.

In addition to the basic syntax, you can specify more options when you create a user:

Resource_option: {MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count}

Resource_option is used to restrict the user's use of system resources:

MAX_QUERIES_PER_HOUR, the number of queries allowed to execute per hour. The default is 0, which means there is no limit

MAX_UPDATES_PER_HOUR, the number of updates allowed per hour. The default is 0, which means there is no limit

MAX_CONNECTIONS_PER_HOUR, the number of connections allowed per hour. The default is 0, which means there is no limit

MAX_USER_CONNECTIONS, the number of concurrent connections for this user. The default is 0, which means there is no limit; in this case, the number of concurrent connections for users is determined by the system variable max_user_connections.

The following statement creates a new account dev02 that allows login from any host. At the same time, the user is limited to 1000 queries and 100 updates per hour:

Mysql > CREATE USER 'dev02'@'%'-> WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 100 * query OK, 0 rows affected (0.01 sec)

Note that the-> in the second line is the client prompt, not the input. Query the system user table to display the above settings:

Mysql > select host, user, max_questions, max_updates from mysql.user +-+ | host | user | max_questions | max_updates | +- -+-+ |% | dev02 | 1000 | localhost | dev01 | 0 | 0 | localhost | mysql.infoschema | 0 | localhost | mysql.session | 0 | 0 | localhost | mysql.sys | | 0 | 0 | localhost | root | 0 | 0 | 0 | +-+ 6 rows in set (0.00 sec) |

The following are password management options:

Password_option: {PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY] | PASSWORD HISTORY {DEFAULT | N} | PASSWORD REUSE INTERVAL {DEFAULT | N DAY} | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]}

Password management options can be used to set password expiration policies, reuse policies, and authentication when changing passwords:

PASSWORD EXPIRE, set the password to expire immediately; PASSWORD EXPIRE DEFAULT, using the global password expiration policy, determined by the system variable default_password_lifetime; PASSWORD EXPIRE NEVER, the password never expires; PASSWORD EXPIRE INTERVAL N DAY password expires every N days

PASSWORD HISTORY DEFAULT, using the global password reuse policy, determined by the system variable password_history; PASSWORD HISTORY N, the new password cannot be repeated with the last N passwords

PASSWORD REUSE INTERVAL DEFAULT, using a global password reuse policy (specified at intervals), determined by the system variable password_reuse_interval; PASSWORD REUSE INTERVAL N DAY, the new password cannot be duplicated with the password within the last N days

PASSWORD REQUIRE CURRENT, the user needs to enter the current password when changing the password; PASSWORD REQUIRE CURRENT OPTIONAL, the user does not need to enter the current password when changing the password; PASSWORD REQUIRE CURRENT DEFAULT, uses the global policy, which is determined by the system variable password_require_current.

The password options for the account can also be viewed through the mysql.user table:

Here are the account locking options:

Lock_option: {ACCOUNT LOCK | ACCOUNT UNLOCK}

This option is used to specify whether to lock the account and the locked account cannot be used; the default is ACCOUNT UNLOCK and the account is not locked.

5.1.2 modify user

The ALTER USER statement can modify the properties of a user, and the options for modifying a user are the same as creating a user.

The first is to change the user's password. The following statement is used to change the password of user dev01:

Mysql > ALTER USER dev01@localhost IDENTIFIED BY 'Dev01@2019';Query OK, 0 rows affected (0.25 sec)

MySQL provides a RENAME USER statement to modify the user name:

Mysql > RENAME USER dev02 TO dev03;Query OK, 0 rows affected (0.26 sec)

The user dev02 is renamed to dev03.

The RENAME USER statement automatically grants the permissions of the old user to the new user, but does not automatically resolve object dependencies on the old user. For example, an error will occur when a stored procedure is defined by an old user name and runs with definer permissions.

Another common user modification is to lock and unlock an account:

Mysql > ALTER USER dev01@localhost ACCOUNT LOCK;Query OK, 0 rows affected (0.13 sec)

User dev01 is locked and cannot be used to connect at this time:

"C:\ Program Files\ MySQL\ MySQL Server 8.0\ bin\ mysql.exe"-u dev01-pEnter password: * ERROR 3118 (HY000): Access denied for user 'dev01'@'localhost'. Account is locked.

The system variable Locked_connects is used to record the number of login attempts to lock the account:

Finally, we unlock the dev01:

Mysql > ALTER USER dev01@localhost ACCOUNT UNLOCK;Query OK, 0 rows affected (0.10 sec)

5.1.3 deleting a user

The DROP USER statement is used to delete a user. The following statement removes the use of dev03:

Mysql > DROP USER dev03;Query OK, 0 rows affected (0.14 sec)

If the deleted user is already connected to the MySQL server, the user can continue with the operation; however, a new connection cannot be established.

5.2 Administrative permissions

The newly created user has only USAGE permissions by default and can only connect to the database without any operation rights. Use the SHOW GRANTS command to view the user's permissions:

Mysql > SHOW GRANTS FOR dev01@localhost +-- + | Grants for dev01@localhost | +-- + | GRANT USAGE ON *. * TO `dev01` @ `localhost` | +- -- + 1 row in set (0.00 sec)

Use the GRANT statement to grant permissions to users.

5.2.1 Grant permissions

The basic syntax of the GRANT statement is as follows:

GRANT privilege,... ON privilege_level TO account_name

The GRANT statement supports the granting of multiple permissions at a time, separated by commas.

Privilege_level specifies the role level of the permission, including:

Global permissions that apply to all databases in the MySQL server. Global permissions are indicated by *. * for example, the following statement grants the dev01@localhost user permission to query all tables in all databases:

Mysql > GRANT SELECT-> ON *. *-> TO dev01@localhost;Query OK, 0 rows affected (0.01 sec) mysql > SHOW GRANTS FOR dev01@localhost +-+ | Grants for dev01@localhost | +-+ | GRANT SELECT ON *. * TO `dev01` @ `localhost` | + -- + 1 row in set (0.00 sec)

Global permissions are stored in the mysql.user table.

Database permissions that act on all objects in the specified database. Database permissions are represented by db_name.*, for example, the following statement grants the dev01@localhost user permission to query all tables in the database world:

Mysql > GRANT ALL-> ON world.*-> TO dev01@localhost;Query OK, 0 rows affected (0.01sec) mysql > SHOW GRANTS FOR dev01@localhost +-- + | Grants for dev01@localhost | +-- -+ | GRANT SELECT ON *. * TO `dev01` @ `localhost` | | GRANT ALL PRIVILEGES ON `world`.* TO `dev01` @ `localhost` | +-+ 2 rows in set (0.00 sec)

Database permissions are stored in the mysql.db table.

Table permissions that apply to all columns of the specified table. Database permissions are expressed in db_name.table_name; if db_name is not specified, the default database is used; if there is no default database, an error is returned. For example, the following statement grants dev01@localhost user database world permission to add, delete, modify and query the country table:

Mysql > GRANT SELECT, INSERT, UPDATE, DELETE-> ON world.country-> TO dev01@localhost;Query OK, 0 rows affected (0.01 sec) mysql > SHOW GRANTS FOR dev01@localhost +-+ | Grants for dev01@localhost | + -+ | GRANT SELECT ON *. * TO `dev01` @ `localhost` | | GRANT ALL PRIVILEGES ON `world`.* TO `dev01` @ `localhost` | | GRANT SELECT INSERT, UPDATE, DELETE ON `world`.`localhost` TO `dev01` @ `localhost` | +-+ 3 rows in set (0.00 sec)

Table permissions are stored in the mysql.tables_priv table.

Column permissions that apply to the specified column of the specified table. Each column permission needs to specify a specific column name. For example, the following statement grants dev01@localhost users query permissions on the code and name fields in the world.country table, as well as permission to modify the population field:

Mysql > GRANT SELECT (code, name), UPDATE (population)-> ON world.country-> TO dev01@localhost;Query OK, 0 rows affected (0.01 sec) mysql > SHOW GRANTS FOR dev01@localhost +- -+ | Grants for dev01@localhost | +- -- + | GRANT SELECT ON *. * TO `dev01` @ `localhost` | | GRANT ALL PRIVILEGES ON `world`.* TO `dev01` @ `localhost` | | GRANT SELECT SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`) DELETE ON `world`.`roomy`TO `dev01` @ `localhost` | +- -+ 3 rows in set (0.00 sec)

Column permissions are stored in the mysql.columns_priv table.

Store routine permissions that act on storage routines (functions and procedures). Storage routine permissions can be specified based on global, database, or individual routines. The following statement grants the dev01@localhost user permission to create storage routines in the database world.country:

Mysql > GRANT CREATE ROUTINE-> ON world.*-> TO dev01@localhost;Query OK, 0 rows affected (0.02 sec) mysql > SHOW GRANTS FOR dev01@localhost +- -+ | Grants for dev01@localhost | +- - -+ | GRANT SELECT ON *. * TO `dev01` @ `localhost` | | GRANT SELECT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON `world`.* TO `dev01` @ `localhost` | GRANT SELECT, SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`) DELETE ON `world`.`roomy`TO `dev01` @ `localhost` | +- -+ 3 rows in set (0.00 sec)

The permissions of the storage routine are stored in the mysql.procs_priv table.

Proxy user rights that allow users to act as agents for other users. The proxy user has all the permissions of the proxied user. The following statement sets the dev01@localhost user as the agent for root:

Mysql > GRANT PROXY-> ON root-> TO dev01@localhost;Query OK, 0 rows affected (0.01sec) mysql > SHOW GRANTS FOR dev01@localhost +- -+ | Grants for dev01@localhost | +- - -+ | GRANT SELECT ON *. * TO `dev01` @ `localhost` | | GRANT SELECT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON `world`.* TO `dev01` @ `localhost` | GRANT SELECT, SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`) DELETE ON `world`.`roomy`TO `dev01` @ `localhost` | | GRANT PROXY ON 'root'@'%' TO' dev01'@'localhost' | +- - -+ 4 rows in set (0.00 sec)

Proxy user rights are stored in the mysql.proxies_priv table.

5.2.2 revoke permissions

The REVOKE statement does the opposite of the GRANT statement, revoking the permissions granted to the user.

REVOKE privilegee,.. ON privilege_levelFROM account_name

The parameters for revoking permissions are similar to when permissions are granted, with the following statement revoking all permissions for user dev01@localhost:

Mysql > REVOKE ALL, GRANT OPTION-> FROM dev01@localhost;Query OK, 0 rows affected (0.01sec) mysql > SHOW GRANTS FOR dev01@localhost +-+ | Grants for dev01@localhost | +-+ | GRANT USAGE ON *. * TO `dev01` @ `localhost` | | GRANT PROXY ON 'root'@'%' TO' dev01'@'localhost' | +-+ 2 rows in set (0.00 sec)

The rights of the proxy user need to be revoked separately:

Mysql > REVOKE PROXY-> ON root-> FROM dev01@localhost;Query OK, 0 rows affected (0.01sec) mysql > SHOW GRANTS FOR dev01@localhost +-- + | Grants for dev01@localhost | +-- + | GRANT USAGE ON *. * TO `dev01` @ `localhost` | +- -- + 1 row in set (0.00 sec)

User dev01@localhost restores its original permissions.

For permissions at the global level, the effect of REVOKE takes effect the next time the user logs in; for permissions at the database level, the effect of REVOKE takes effect after the execution of the USE command; for permissions at the table or field level, the effect of REVOKE takes effect immediately.

5.3 Administrative roles

As there are more and more users, the management of permissions becomes more and more complex; in fact, many users need the same or similar permissions. To this end, MySQL 8.0 introduces a new feature: Role. A role is a collection of permissions.

Similar to accounts, roles can grant permissions; however, roles cannot be used to log in to the database. The steps to authorize a user through a role are as follows:

Create a role

Grant permissions to roles

Assign a role to the user.

5.3.1 create roles

Suppose our application needs to use a world database. Developers need full access to the database, testers need read and write access to tables, and business analysts need access to query data.

First, create three roles using the CREATE ROLE statement:

Mysql > CREATE ROLE devp_role, read_role, write_role;Query OK, 0 rows affected (0.02 sec)

The role name is similar to the account name, but can also contain two parts, role_name and host_name, using @ connection.

At this point, if you query the user table:

As you can see, the role is actually a user, but there is no password.

5.3.2 role Authorization

Authorization for roles is similar to user authorization by using the GRANT statement. We assign permissions to the above three roles:

Mysql > GRANT ALL ON world.* TO devp_role;Query OK, 0 rows affected (0.01 sec) mysql > GRANT SELECT ON world.* TO read_role;Query OK, 0 rows affected (0.01 sec) mysql > GRANT INSERT, UPDATE, DELETE ON world.* TO write_role;Query OK, 0 rows affected (0.01 sec)

The permissions for viewing roles are similar to those for querying users:

5.3.2 assign roles to users

Next we create a few users and assign roles to each of them.

Mysql > CREATE USER devp1 IDENTIFIED BY 'Devp1@2019';Query OK, 0 rows affected (0.01 sec) mysql > CREATE USER read1 IDENTIFIED BY' Read1@2019';Query OK, 0 rows affected (0.01 sec) mysql > CREATE USER test1 IDENTIFIED BY 'Test1@2019';Query OK, 0 rows affected (0.04 sec)

Assigning roles to users is similar to granting permissions, using the Grant statement:

Mysql > GRANT devp_role TO devp1;Query OK, 0 rows affected (0.01 sec) mysql > GRANT read_role TO read1;Query OK, 0 rows affected (0.01 sec) mysql > GRANT read_role, write_role TO test1;Query OK, 0 rows affected (0.01 sec)

Query the user's permissions again:

If you want to know the specific permissions that the user gets through the role, you can use the USING option:

Alternatively, you can replicate permissions by granting one user to another:

Mysql > GRANT read1 TO test1;Query OK, 0 rows affected (0.09 sec)

A user is a role with login privileges, and a role is a user who cannot log in.

5.3.4 set the default role

Connect to the database using devp1:

"C:\ Program Files\ MySQL\ MySQL Server 8.0\ bin\ mysql.exe"-u devp1-pEnter password: * Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 14Server version: 8.0.17 MySQL Community Server-GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > use world;ERROR 1044 (42000): Access denied for user 'devp1'@'%' to database' world'

We have granted the devp_role role to the user devp1, which has all permissions on the database world; the reason for the error is that the role is not automatically activated. Use the CURRENT_ROLE () function to view the currently launched roles:

Mysql > SELECT current_role (); +-+ | current_role () | +-+ | NONE | +-+ 1 row in set (0.00 sec)

The results show that there are no roles. The SET DEFAULT ROLE command sets the user's default active role:

Mysql > SET DEFAULT ROLE ALL-> TO devp1;Query OK, 0 rows affected (0.01 sec)

When you use devp1 to connect to the database again, all of the user's roles will be activated:

"C:\ Program Files\ MySQL\ MySQL Server 8.0\ bin\ mysql.exe"-u devp1-pEnter password: * Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 15Server version: 8.0.17 MySQL Community Server-GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > SELECT current_role (); +-+ | current_role () | +-+ | `role` @ `%` | +-+ 1 row in set (0.00 sec) mysql > use world;Database changedmysql > select * from city limit 1 +-+ | ID | Name | CountryCode | District | Population | +-+ | 1 | Kabul | AFG | | Kabol | 1780000 | +-+ 1 row in set (0.00 sec) |

Another way is to use the SET ROLE command to set the active role of the current session:

SET ROLE NONE;SET ROLE ALL;SET ROLE DEFAULT

The above statement indicates that no roles are set, all roles are set, and default roles are set.

5.3.5 revoke the permissions of a role

Revoking the permissions of a role is similar to revoking the permissions of a user, and revoking the permissions of a role also affects the user with that role.

The following statement revokes the DELETE permissions for the role write_role:

Mysql > REVOKE DELETE-> ON world.*-> FROM write_role;Query OK, 0 rows affected (0.14 sec)

At this point, the corresponding permissions on the user test1 are also revoked.

5.3.6 Delete roles

The DROP ROLE statement removes roles:

DROP ROLE role_name,...

Deleting a role revokes the role assigned to the user. The following statement removes the roles read_role and write_role:

Mysql > DROP ROLE read_role, write_role;Query OK, 0 rows affected (0.10 sec) after reading this article, I believe you have some understanding of "sample Analysis of accounts and permissions in mysql". If you want to know more about it, please 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.