mysql/数据库权限管理.md at main

wxin/mysql

Fork 0

wxin fad5007b77 上传文件至 /

2025-03-11 20:42:19 +08:00

16 KiB

Raw Permalink Blame History

权限管理

一：权限管理

1. 权限级别

Global level：系统级，所有库，所有表的权限
Database level：某个数据库中的所有表的权限
Table level：库中的某个表的权限
Column level：表中的某个字段的权限
procs level：某个存储过程的权限
proxies level：代理服务器的权限

2. 查看权限记录表

因为超级管理员默认已经设置；所以直接查询权限即可

Global level

mysql> select * from mysql.user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           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
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *AD894C69730A54BD7AB4A411AC954632A005973A
      password_expired: N
 password_last_changed: 2025-03-09 17:33:44
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: Y
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2025-03-09 17:31:55
     password_lifetime: NULL
        account_locked: Y
*************************** 3. row ***************************
                  Host: localhost
                  User: mysql.sys
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
      password_expired: N
 password_last_changed: 2025-03-09 17:31:55
     password_lifetime: NULL
        account_locked: Y
3 rows in set (0.00 sec)

字段介绍：

用户字段：root
权限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817

Select_priv：查询权限
Insert_priv：插入权限
Update_priv：更新权限
Delete_priv：删除权限
......

Database level

mysql> select * from mysql.db\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: sys
                 User: mysql.sys
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
2 rows in set (0.00 sec)

测试库权限：

mysql> create database t1;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on t1.* to 't1'@'localhost' identified by 'Wdsji@213';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看：

*************************** 3. row ***************************
                 Host: localhost
                   Db: t1
                 User: t1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
3 rows in set (0.00 sec)

验证：

[root@mysql ~]# mysql -ut1 -p'Wdsji@213';
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t1                 |
+--------------------+
2 rows in set (0.00 sec)

Table level

mysql> select * from mysql.tables_priv\G
*************************** 1. row ***************************
       Host: localhost
         Db: mysql
       User: mysql.session
 Table_name: user
    Grantor: boot@connecting host
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select
Column_priv: 
*************************** 2. row ***************************
       Host: localhost
         Db: sys
       User: mysql.sys
 Table_name: sys_config
    Grantor: root@localhost
  Timestamp: 2025-03-09 17:31:55
 Table_priv: Select
Column_priv: 
2 rows in set (0.00 sec)

创建库表验证：

mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t2;
Database changed
mysql> create table u1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into u1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> grant all on t2.u1 to 't2'@'localhost' identified by 'Wxhfua@3414';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table u2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
| u2           |
+--------------+
2 rows in set (0.00 sec)

权限查看：

mysql> select * from mysql.tables_priv\G
*************************** 3. row ***************************
       Host: localhost
         Db: t2
       User: t2
 Table_name: u1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv: 
3 rows in set (0.00 sec)

验证：（登录t2账户，看到u1表，看不到u2代表权限成功）

[root@mysql ~]# mysql -ut2 -p'Wxhfua@3414'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| t2                 |
+--------------------+
2 rows in set (0.00 sec)

mysql> use t2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_t2 |
+--------------+
| u1           |
+--------------+
1 row in set (0.00 sec)

Column level

mysql> select * from mysql.columns_priv\G
Empty set (0.00 sec)

mysql> insert into mysql.columns_priv(host,db,user,table_name,column_name,column_priv) values('%','t2','t2','u1','id','select');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.columns_priv\G
*************************** 1. row ***************************
       Host: %
         Db: t2
       User: t2
 Table_name: u1
Column_name: id
  Timestamp: 2025-03-11 19:37:00
Column_priv: Select
1 row in set (0.00 sec)

注意：

前提是有库，有表，有权限

二：用户管理

1. 登录和退出

[root@mysql ~]# mysql -h 192.168.18.160 -P 3306 -u root -pmysql -e "show databases;"
[root@mysql ~]# mysql -h 192.168.18.160 -P 3306 -u root -pmysql mysql -e "show tables;"

2. 创建用户

方式一：

mysql> create user wxin;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
注意：
	该报错是因为密码强度问题，取消密码强度即可创建用户
	
mysql> create user wxin@'%' identified by 'Wfsf@31241';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

方式二：

mysql> grant all on *.* to 'Wxin'@'localhost' identified by 'Wfsf@31241';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;

注意：

该方式采用授权的方式

ALL 所有权限 select 单独某一个权限（多个权限用逗号隔开）

mysql> grant select on *.* to 'Wxin'@'localhost' identified by 'Wfsf@31241';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;

*.*		所有的库所有的表   也可以单独某一个库某一个表

root@localhost  用户有则授权无则创建   localhost % 10.19.40.% 10.19.40.11

3. 删除用户

方式一：

mysql> drop user wxin@'%';
Query OK, 0 rows affected (0.00 sec)

方式二：

ysql> delete from mysql.user where user='Wxin' and Host='%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

4. 修改密码

方式一：

[root@mysql ~]# mysqladmin -uroot -p'Wxin@123456' password 'Wxin@123'

方式二：

mysql> update mysql.user set authentication_string=password(123456) where user='Wxin' and Host='%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1
mysql> flush privileges;

注意：

刷新授权表后生效：flush privileges

自己设置自己密码：

mysql> set password='Wxin@123';
Query OK, 0 rows affected (0.00 sec)

root用户修改其他用户密码：

方法一：

mysql> set password for wxin@'localhost'='Wxin@123456';
Query OK, 0 rows affected (0.00 sec)

方式二：

mysql> update mysql.user set authentication_string=password('Wxin@123') where user='wxin' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

5. 查看密码策略

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

参数解释：

validate_password_dictionary_file 指定密码验证的文件路径
validate_password_length 密码最小长度
validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数
validate_password_number_count 密码至少要包含的数字个数
validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1
1. 0/LOW：只检查长度
2. 1/MEDIUM：检查长度、数字、大小写、特殊字符
3. 2/STRONG：检查长度、数字、大小写、特殊字符字典文件
validate_password_special_char_count密码至少要包含的特殊字符数

修改密码策略：

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 4      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

关闭密码策略：

修改配置文件，添加以下参数：
[root@mysql ~]# vim /etc/my.cnf
validate_password=off

16 KiB Raw Permalink Blame History Unescape Escape

权限管理

一：权限管理

1. 权限级别

2. 查看权限记录表

Global level

Database level

Table level

Column level

二：用户管理

1. 登录和退出

2. 创建用户

3. 删除用户

4. 修改密码

5. 查看密码策略

16 KiB

Raw Permalink Blame History