Home >Database >Mysql Tutorial >How does MySQL perform permission management?
How does MySQL perform permission management?
- 青灯夜游forward
- 2019-02-26 11:00:234204browse
The content of this article is to introduce how MySQL performs permission management? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
MySQL's permission table is loaded into the memory when the database is started. When the user passes the identity authentication, the corresponding permissions are accessed in the memory. In this way, the user can set permission ranges in the database. Various operations within. [Video tutorial recommendation: MySQL tutorial]
1. Access to permission tables
In the two processes of permission access, the system will use "mysql" The three most important permission tables in the database (created when MySQL is installed, the database name is "mysql") are user, host and db.
Among these three tables, the most important one is the user table, followed by the db table. The host table is not used in most cases.
The columns in user are mainly divided into 4 parts: user columns, permission columns, security columns and resource control columns.
Usually the most commonly used columns are user columns and permission columns, among which permission columns are divided into normal permissions and management permissions. Ordinary permissions are used for database operations, such as select_priv, super_priv, etc.
When a user connects, the access process of the permission table has the following two processes:
First start with the three host, user and password in the user table Fields are used to determine whether the connected IP, username, and password exist in the table. If they exist, the authentication is passed, otherwise the connection is rejected.
If the identity authentication is passed, the database permissions will be obtained in the order of the following permission table: user -> db -> tables_priv -> columns_priv.
In these permission tables, the scope of permissions decreases in order, and global permissions cover local permissions. The first stage above is easy to understand. Let’s use an example to explain the second stage in detail.
In order to facilitate testing, you need to modify the variable sql_mode
// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户，除非还指定了密码) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1. Create user zj@localhost and grant select permissions to all tables on all databases
MySQL [mysql]> grant select on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ...
2. Check the db table
MySQL [mysql]> select * from db where user='zj' \G ; Empty set (0.00 sec)
and you can find that the select_priv column of the user table is "Y", but there is no record in the db table. In other words, users who have the same permissions on all databases do not need Record to the db table, and only need to change the select_priv in the user table to "Y". In other words, every permission in the user table represents permissions on all databases.
3. Change the permissions on zj@localhost to only select permissions on all tables on the t2 database.
MySQL [mysql]> revoke select on *.* from zj@localhost; Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql]> grant select on t2.* to zj@localhost; Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql]> select * from user where user='zj' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='zj' \G; *************************** 1. row *************************** Host: localhost Db: t2 User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N
At this time, we found that select_priv in the user table changed to "N", and a record with db t2 was added to the db table. That is to say, when certain permissions are only granted to part of the database, the corresponding permission column in the user table remains "N", and the specific database permissions are written to the db table. The permission mechanism of table and column is similar to that of db.
As can be seen from the above example, when a user passes permission authentication and permissions are assigned, the permissions will be assigned in the order of user -> db -> tables_priv -> columns_priv, that is, the global permissions will be checked first. Table user, if the corresponding permission in user is "Y", then this user's permission on all databases is "Y", and db, tables_priv and columns_priv will no longer be checked; if it is "N", this user's permission will be checked in the db table. The specific database corresponding to the user, and obtains the permission of "Y" in the db; if the corresponding permission in the db is "N", then check the permissions in tables_priv and columns_priv in turn, if all are "N", then it is judged as Does not have permission.
2. Account management
Mainly includes account creation, permission changes and account deletion.
1. Create an account
Use grant syntax to create, example:
(1) Create user zj, with permissions that can execute all permissions on all databases, and can only be executed from Connect locally.
MySQL [mysql]> grant all privileges on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y
It can be found that except for the grant_priv permission, all permissions are "Y" in the user table.
(2) Based on (1), increase the grant permission for zj
MySQL [(none)]> grant all privileges on *.* to zj@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y ...
(3) Based on (2), set the password to “123”
MySQL [mysql]> grant all privileges on *.* to zj@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL
It can be found that the password becomes a bunch of encrypted strings.
(4) Create a new user zj2, which can connect from any IP. The permissions are to perform select, update, insert and delete operations on all tables in the t2 database. The initial password is "123"
MySQL [mysql]> grant select ,insert, update,delete on t2.* to 'zj2'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql]> select * from user where user='zj2' and host="%" \G; *************************** 1. row *************************** Host: % User: zj2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULL MySQL [mysql]> select * from db where user="zj2" and host='%' \G; *************************** 1. row *************************** Host: % Db: t2 User: zj2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N ......
The permissions in the user table are all "N", and the permissions of the records added in the db table are all "Y". In general, only grant users appropriate permissions without granting excessive permissions.
The IP limit in this example is that all IPs can connect, so it is set to "*". In the mysql database, it is controlled through the host field of the user table. Host can be the following types of assignments.
Host value can be a host name or IP number, or "localhost" indicates the local host.
You can use the wildcard characters "%" and "_" in the Host column value
Host value "%" matches any host name, empty Host values are equivalent to "%" and their meaning is the same as the pattern matching operation of the like operator.
注意: mysql 数据库的 user 表中 host 的值为 “*” 或者空，表示所有外部 IP 都可以连接，但是不包括本地服务器 localhost，因此，如果要包括本地服务器，必须单独为 localhost 赋予权限。
(5) 授予 super、process、file 权限给用户 zj3@%
MySQL [mysql]> grant super,process,file on *.* to 'zj3'@'%'; Query OK, 0 rows affected, 1 warning (0.00 sec)
因为这几个权限都是属于管理权限，因此不能够指定某个数据库，on 后面必须跟 “.”,下面语法将提示错误
MySQL [mysql]> grant super,process,file on t2.* to 'zj3'@'%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
(6) 只授予登录权限给 zj4@localhost
MySQL [mysql]> grant usage on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> exit Bye zj@bogon:~$ mysql -uzj4 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78 Server version: 5.7.18-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.02 sec)
usage 权限只能用于数据库登录，不能执行任何操作
2. 查看账号权限
账号创建好后，可以通过如下命令查看权限:
show grants for user@host;
示例：
MySQL [(none)]> show grants for zj@localhost; +-------------------------------------------------------------------+ | Grants for zj@localhost | +-------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'zj'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------------+ 1 row in set (0.01 sec)
3. 更改账号权限
可以进行权限的新增和回收。和创建账号一样，权限变更也有两种方法：使用 grant(新增) 和 revoke (回收) 语句，或者更改权限表。
示例:
(1) zj4@localhost 目前只有登录权限
MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
(2) 赋予 zj4@localhost 所有数据库上的所有表的 select 权限
MySQL [(none)]> grant select on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +------------------------------------------+ | Grants for zj4@localhost | +------------------------------------------+ | GRANT SELECT ON *.* TO 'zj4'@'localhost' | +------------------------------------------+ 1 row in set (0.00 sec)
(3) 继续给 zj4@localhost 赋予 select 和 insert 权限，和已有的 select 权限进行合并
MySQL [(none)]> show grants for 'zj4'@'localhost'; +--------------------------------------------------+ | Grants for zj4@localhost | +--------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'zj4'@'localhost' | +--------------------------------------------------+ 1 row in set (0.00 sec)
revoke 语句可以回收已经赋予的权限，对于上面的例子，这里决定要收回 zj4@localhost 上的 insert 和 select 权限：
MySQL [(none)]> revoke select,insert on *.* from zj4@localhost; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
usage 权限不能被回收，也就是说，revoke 用户并不能删除用户。
4. 修改账号密码
(1) 可以用 mysqladmin 命令在命令行指定密码。
shell> mysqladmin -u user_name -h host_name password "123456"
(2) 执行 set password 语句。
mysql> set password for 'username'@'%' = password('pwd');
如果是更改自己的密码，可以省略 for 语句
mysql> set password=password('pwd');
(3) 可以在全局级别使用 grant usage 语句(在“.”)来指定某个账户的密码而不影响账户当前的权限。
mysql> grant usage on *.* to 'username'@'%' identified by 'pwd';
5. 删除账号
要彻底的删除账号，可以使用 drop user ：
drop user zj@localhost;
6. 账号资源限制
创建 MySQL 账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：
max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数
How does MySQL perform permission management?
posted on
MySQL Permission Management
##Permission Control
- # 1.5K Reading
It takes 38 minutes to read
MySQL's permission table is loaded into the memory when the database is started. When the user passes the identity authentication, the corresponding permissions are accessed in the memory. In this way, this Users can perform various operations within the scope of authority in the database. 1. Access to permission tablesIn the two processes of permission access, the system will use the "mysql" database (created when MySQL is installed, and the database name is "mysql" ), the three most important permission tables are user, host and db. Among these three tables, the most important one is the user table, followed by the db table. The host table is not used in most cases.
The columns in user are mainly divided into 4 parts: user columns, permission columns, security columns and resource control columns.
select_priv
Usually the most commonly used columns are user columns and permission columns, among which permission columns are divided into normal permissions and management permissions. Ordinary permissions are used for database operations, such as,
super_priv, etc.
When a user connects, the access process of the permission table has the following two processes:
First start with the three host, user and password in the user table Fields are used to determine whether the connected IP, username, and password exist in the table. If they exist, the authentication is passed, otherwise the connection is rejected.
If the identity authentication is passed, the database permissions will be obtained in the order of the following permission table: user -> db -> tables_priv -> columns_priv.In these permission tables, the scope of permissions decreases in order, and global permissions cover local permissions. The first stage above is easy to understand. Let’s use an example to explain the second stage in detail.
- In order to facilitate testing, you need to modify the variable sql_mode
-
// sql_mode 默认值中有 NO_AUTO_CREATE_USER (防止GRANT自动创建新用户，除非还指定了密码) SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
1. Create user zj@localhost and grant select permissions to all tables on all databases
MySQL [mysql]> grant select on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ...
-
- 2. Check the db table
MySQL [mysql]> select * from db where user='zj' \G ; Empty set (0.00 sec)
and you can find that the select_priv column of the user table is "Y", but there is no record in the db table. In other words, users who have the same permissions on all databases do not need Record to the db table, and only need to change the select_priv in the user table to "Y". In other words, every permission in the user table represents permissions on all databases.
MySQL [mysql]> revoke select on *.* from zj@localhost; Query OK, 0 rows affected, 1 warning (0.02 sec) MySQL [mysql]> grant select on t2.* to zj@localhost; Query OK, 0 rows affected, 1 warning (0.04 sec) MySQL [mysql]> select * from user where user='zj' \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N ... MySQL [mysql]> select * from db where user='zj' \G; *************************** 1. row *************************** Host: localhost Db: t2 User: zj Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N
At this time, we found that select_priv in the user table changed to "N", and a record with db t2 was added to the db table. That is to say, when certain permissions are only granted to part of the database, the corresponding permission column in the user table remains "N", and the specific database permissions are written to the db table. The permission mechanism of table and column is similar to that of db. As can be seen from the above example, when a user passes permission authentication and permissions are assigned, the permissions will be assigned in the order of user -> db -> tables_priv -> columns_priv, that is, the global permissions will be checked first. Table user, if the corresponding permission in user is "Y", then this user's permission on all databases is "Y", and db, tables_priv and columns_priv will no longer be checked; if it is "N", this user's permission will be checked in the db table. The specific database corresponding to the user, and obtains the permission of "Y" in the db; if the corresponding permission in the db is "N", then check the permissions in tables_priv and columns_priv in turn, if all are "N", then it is judged as Does not have permission.
2. Account management
Mainly includes account creation, permission changes and account deletion.
1. Create an account
Use grant syntax to create, example:
(1) Create user zj, with permissions that can execute all permissions on all databases, and can only be executed from Connect locally.
MySQL [mysql]> grant all privileges on *.* to zj@localhost; Query OK, 0 rows affected, 2 warnings (0.00 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y
It can be found that except for the grant_priv permission, all permissions are "Y" in the user table.
(2) Based on (1), increase the grant permission for zj
MySQL [(none)]> grant all privileges on *.* to zj@localhost with grant option; Query OK, 0 rows affected, 1 warning (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host='localhost' \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y ...
(3) Based on (2), set the password to “123”
MySQL [mysql]> grant all privileges on *.* to zj@localhost identified by '123' with grant option; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> select * from user where user="zj" and host="localhost" \G ; *************************** 1. row *************************** Host: localhost User: zj Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:29:42 password_lifetime: NULL
It can be found that the password becomes a bunch of encrypted strings.
(4) 创建新用户 zj2，可以从任何 IP 连接，权限为对 t2 数据库里的所有表进行 select 、update、insert 和 delete 操作，初始密码为“123”
MySQL [mysql]> grant select ,insert, update,delete on t2.* to 'zj2'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [mysql]> select * from user where user='zj2' and host="%" \G; *************************** 1. row *************************** Host: % User: zj2 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N ...... authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2017-09-25 20:37:49 password_lifetime: NULL MySQL [mysql]> select * from db where user="zj2" and host='%' \G; *************************** 1. row *************************** Host: % Db: t2 User: zj2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N ......
user 表中的权限都是“N”，db 表中增加的记录权限则都是“Y”。一般的，只授予用户适当的权限，而不会授予过多的权限。
本例中的 IP 限制为所有 IP 都可以连接，因此设置为 “*”，mysql 数据库中是通过 user 表的 host 字段来进行控制，host 可以是以下类型的赋值。
Host 值可以是主机名或IP号，或 “localhost” 指出本地主机。
可以在 Host 列值使用通配符字符 “%” 和 “_”
Host 值 “%” 匹配任何主机名，空 Host 值等价于 “%”，它们的含义与 like 操作符的模式匹配操作相同。
注意: mysql 数据库的 user 表中 host 的值为 “*” 或者空，表示所有外部 IP 都可以连接，但是不包括本地服务器 localhost，因此，如果要包括本地服务器，必须单独为 localhost 赋予权限。
(5) 授予 super、process、file 权限给用户 zj3@%
MySQL [mysql]> grant super,process,file on *.* to 'zj3'@'%'; Query OK, 0 rows affected, 1 warning (0.00 sec)
因为这几个权限都是属于管理权限，因此不能够指定某个数据库，on 后面必须跟 “.”,下面语法将提示错误
MySQL [mysql]> grant super,process,file on t2.* to 'zj3'@'%'; ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
(6) 只授予登录权限给 zj4@localhost
MySQL [mysql]> grant usage on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 2 warnings (0.01 sec) MySQL [mysql]> exit Bye zj@bogon:~$ mysql -uzj4 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 78 Server version: 5.7.18-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.02 sec)
usage 权限只能用于数据库登录，不能执行任何操作
2. 查看账号权限
账号创建好后，可以通过如下命令查看权限:
show grants for user@host;
示例：
MySQL [(none)]> show grants for zj@localhost; +-------------------------------------------------------------------+ | Grants for zj@localhost | +-------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'zj'@'localhost' WITH GRANT OPTION | +-------------------------------------------------------------------+ 1 row in set (0.01 sec)
3. 更改账号权限
可以进行权限的新增和回收。和创建账号一样，权限变更也有两种方法：使用 grant(新增) 和 revoke (回收) 语句，或者更改权限表。
示例:
(1) zj4@localhost 目前只有登录权限
MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
(2) 赋予 zj4@localhost 所有数据库上的所有表的 select 权限
MySQL [(none)]> grant select on *.* to 'zj4'@'localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +------------------------------------------+ | Grants for zj4@localhost | +------------------------------------------+ | GRANT SELECT ON *.* TO 'zj4'@'localhost' | +------------------------------------------+ 1 row in set (0.00 sec)
(3) 继续给 zj4@localhost 赋予 select 和 insert 权限，和已有的 select 权限进行合并
MySQL [(none)]> show grants for 'zj4'@'localhost'; +--------------------------------------------------+ | Grants for zj4@localhost | +--------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'zj4'@'localhost' | +--------------------------------------------------+ 1 row in set (0.00 sec)
revoke 语句可以回收已经赋予的权限，对于上面的例子，这里决定要收回 zj4@localhost 上的 insert 和 select 权限：
MySQL [(none)]> revoke select,insert on *.* from zj4@localhost; Query OK, 0 rows affected, 1 warning (0.00 sec) MySQL [(none)]> show grants for zj4@localhost; +-----------------------------------------+ | Grants for zj4@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO 'zj4'@'localhost' | +-----------------------------------------+ 1 row in set (0.00 sec)
usage 权限不能被回收，也就是说，revoke 用户并不能删除用户。
4. 修改账号密码
(1) 可以用 mysqladmin 命令在命令行指定密码。
shell> mysqladmin -u user_name -h host_name password "123456"
(2) 执行 set password 语句。
mysql> set password for 'username'@'%' = password('pwd');
如果是更改自己的密码，可以省略 for 语句
mysql> set password=password('pwd');
(3) 可以在全局级别使用 grant usage 语句(在“.”)来指定某个账户的密码而不影响账户当前的权限。
mysql> grant usage on *.* to 'username'@'%' identified by 'pwd';
5. 删除账号
要彻底的删除账号，可以使用 drop user ：
drop user zj@localhost;
6. 账号资源限制
创建 MySQL 账号时，还有一类选项称为账号资源限制，这类选项的作用是限制每个账号实际具有的资源限制，这里的“资源”主要包括：
max_queries_per_hour count : 单个账号每小时执行的查询次数
max_upodates_per_hour count : 单个账号每小时执行的更新次数
max_connections_per_hour count : 单个账号每小时连接服务器的次数
max_user_connections count : 单个账号并发连接服务器的次数
你可能感兴趣的
评论
The above is the detailed content of How does MySQL perform permission management?. For more information, please follow other related articles on the PHP Chinese website!