Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL user Management

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

Share

Shulou(Shulou.com)06/01 Report--

Reasonable management of DB user rights is the basic work of a DBA. Here we sort out the relevant content of MySQL user management.

CREATE USER 、 DROP USER 、 RENAME USER

Used to create, delete, and modify MySQL users

GRANT

Assign permissions to a given MySQL user

REVOKE revokes the relevant permissions of a given MySQL user

SET PASSWORD configures passwords for MySQL users

SHOW GRANTS displays the permissions of a given MySQL user

The relevant user and permission information is recorded in the permission table in the MySQL database, as follows:

User users who can connect to the database and their global permissions

Db database-level permissions

Table_priv Datasheet level permissions

Columns_priv data column level permissions

Procs_priv permissions related to storage instance generation

MySQL user Management

Create a MySQL user (can only be created by a user who has the right to create a MySQL user. The following example takes the root user as the test user)

Mysql > create user kevin@'%' identified by 'oracle';Query OK, 0 rows affected (0.00 sec) mysql > select user, host, password from mysql.user where user='kevin' +-- + | user | host | password | +-- -+ | kevin |% | * 2447D497B9A6A15F2776055CB2D1E9F86758182F | +-+ 1 row in set (0.00 sec)

Kevin@'%' username + hostname

% character wildcard, representing any character

MySQL restricts the hosts logged in through the MySQL user you want to create by configuring the hostname, which means that any host can log in.

Kevin@10.8.1.% indicates that hosts in paragraph 10.8.1 can log in.

Kevin@'Oracle.com.cn' means that hosts with the hostname oracle.com.cn can be logged in through the kevin user.

Authorization of MySQL account

Syntax:

GRANT privileges (columns)

On what

TO account

[identified by 'password']

[REQUIRE encryption requirements]

[WITH grant or resource management options]

If the account does not exist in the grant statement, create the account first, and then assign the given permissions to it.

The permissions granted to the account by privilege.

The level of what permissions. The highest level is global, and the given permissions are used for all databases and all data tables. At the same time, permissions can also be set to database, table, column, and stored procedure levels.

The account to which account is granted permission.

Password changes the password of the account

Columns permission action column

The REQUIRE and WITH clauses are optional, and the require clause is used for secure connections using SSL. The WITH clause is used to grant GRANT OPTION permissions (allowing the account's own permissions to be granted to others).

Account authority arrangement

Database management authority

CREATE USER FILE (READ OR WRITE MYSQL SERVER'S FILES) GRANT OPTION RELOAD PROCESS REPLICATION CLIENT REPLICATION SLAVE SHUTDOWN SUPER (kill command to shutdown processes or other super user operation)

Database object permissions

ALTER/ALTER ROUTINE/CREATE/CREATE ROUTINE/CREATE TEMPORARY TABLES/CREATE VIEWS/DELETE/DRPOP/EVENT/EXECUTE/INDEX/INSERT/LOCK TABLES/REFERENCE/SELECT/SHOW VIEW/TRIGGER/UPDATE

Other permissions

ALL ALL operations inluding GRANT privilege

USAGE a special privilege and means NULL privilege

Users must have appropriate permissions and GRANT OPTION permissions when they are authorized. At the same time, the scope of MySQL permissions includes the overall database system, databases, data tables, data columns, and so on. When paying rights, the scope of permissions granted is controlled by the ON clause, as follows:

ON. Global permissions

ON * if no database (use db) is specified, it is a global permission, otherwise it is a permission to determine the default object of the database.

ON db_name.* database level permissions

ON db_name.tbl_name database table-level permissions

ON tbl_name Datasheet level permissions

ON db_name . Routie_name storage routine permissions

View the permissions that the user has.

Mysql > show grants for system

+-+ | Grants for system@% | |

+-+ | GRANT ALL PRIVILEGES ON *. * TO 'system'@'%' IDENTIFIED BY PASSWORD' * 58319282EAB9E38D49CA25844B73DA62C80C2ABC' |

+-+ 1 row in set (0.00 sec) 12345671234567

Revoke permissions and delete users

REVOKE privileges [columns] ON what FROM account

Modify the user's password

SET PASSWORD FOR system@'%' = PASSWORD ('PASSWD')

Of course, add, modify the user's rights can also be directly insert, update mysql.user table, these operations need perceptual but beginners to try.

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.

Views: 0

*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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report