Mysql用户权限管理

Mysql用户权限管理

mysql用户权限管理

常用权限

• SQL语句：
  • SELECT 
  • INSERT
  • UPDATE
  • DELETE
  • INDEX (所有权限）
• 存储过程：
  • CREATE ROUTINE 允许创建存储过程或函数
  • ALTER ROUTINE
  • EXECUTE
  • TRIGGER
• 管理权限：
  • SUPER
  • RELOAD
  • SHOW DATABASE
  • SHUTDOWN
  • GRANT OPTION

创建用户

不配置权限创建用户

mysql> create user 'hwf'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

创建带有权限的用户

mysql> grant select,update,insert,delete on sys.* to 'ww'@'%' identified；

Query OK, 0 rows affected, 1 warning (0.00 sec)

删除用户

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

查看链接用户进程

mysql> mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 92502 | Waiting on empty queue | NULL             |
| 23 | root            | localhost | NULL | Query   |     0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

查看用户的权限

查看当前用户权限

mysql> mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户权限

mysql> show grants for 'hwf'@'%';
+---------------------------------+
| Grants for hwf@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'hwf'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

授予用户具体权限

//*.*：代表所有库，这个库里的所有表
mysql> grant select,update,insert,delete on sys.* to 'hwf'@'%';
Query OK, 0 rows affected (0.00 sec)

再次查看hwf用户的权限

mysql> show grants for 'hwf'@'%';
+--------------------------------------------------------------+
| Grants for hwf@%                                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hwf'@'%'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sys`.* TO 'hwf'@'%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

要是再次给用户添加权限，继续使用grant命令

mysql> grant create,index on sys.* to 'hwf'@'%';
Query OK, 0 rows affected (0.01 sec)

查看是否添加

mysql> show grants for 'hwf'@'%';
+-----------------------------------------------------------------------------+
| Grants for hwf@%                                                            |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hwf'@'%'                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX ON `sys`.* TO 'hwf'@'%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

删除用户权限/回收权限

回收指定权限

mysql> revoke create,index on sys.* from 'hwf'@'%';
Query OK, 0 rows affected (0.01 sec)

加all权限回到初始化

mysql> revoke all on sys.* from 'hwf'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'hwf'@'%';
+---------------------------------+
| Grants for hwf@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'hwf'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

使赠予权限的用户拥有赋予其他人自己拥有的权限

mysql> grant create,index on sys.* to 'hwf'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

hwf用户拥有了赋予其它用户自己有的权限的能力

mysql> show grants for 'hwf'@'%';
+---------------------------------------------------------------+
| Grants for hwf@%                                              |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hwf'@'%'                               |
| GRANT CREATE, INDEX ON `sys`.* TO 'hwf'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+
2 rows in set (0.00 sec)

修改用户密码

mysql> alter user 'ww'@'%' identified by '456';

Query OK, 0 rows affected (0.00 sec)

查看用户所在数据

mysql库下边，的表

• user     全局权限
• db         数据库权限
• table_priv  表级别的权限
• cilumns_priv 列级别的权限

查看当前拥有的用户和密码

mysql> select user,host,authentication_string from user;
+---------------+-------------+-------------------------------------------+
| user          | host        | authentication_string                     |
+---------------+-------------+-------------------------------------------+
| root          | localhost   | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| mysql.session | localhost   | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost   | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| hwf           | 192.168.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root          | 192.168.1.% | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| hwf           | %           | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ww            | %           | *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+---------------+-------------+-------------------------------------------+
7 rows in set (0.00 sec)

查看hwf全局用户权限

mysql>use mysql;
mysql> select * from user where user='hwf' G\

*************************** 2. row ***************************
                  Host: 192.168.1.%
                  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

用户资源管理（一般不用数据库内部设置，都是通过操作系统设置）

resource_option:{

| MAX_QUERIES_PER_HOUR count

• 每小时执行查询的次数【可以改为每分钟，秒】

| MAX_UPDATES_PER_HOUR count

• 每小时执行更新的此时

| MAX_CONNECTIONS_PER_HOUR count

• 每小时连接的最大次数

| MAX_USER_CONNECTIONS count

• 单个用户最大的连接次数

}

设置hwf用户只能有一个连接数

mysql> alter user 'hwf'@'%' with max_user_connections 1;
Query OK, 0 rows affected (0.49 sec)

设置hwf用户一个小时之内最大的连接次数为3次

mysql> alter user 'hwf'@'%' with max_connections_per_hour 3;
Query OK, 0 rows affected (0.00 sec)

Mysql8.0角色的权限

-- 创建Role，已有用户进行绑定

create role senior_dba,app_dev;
grant all on *.* to senior_dba with grant option;
grant select,insert,update,delete on wp.* to app_dev;

-- 创建用户与角色绑定

create user tom@'192.168.1.%' identfied by '123';
grant senior_dba to tom@'192.168.1.%';

-- 显示用户权限

show grants for 'tom'@'192.168.1.%';
show grants for 'tom'@'192.168.1.%' using senior_dba;

-- 删除角色

drop role sentior_dba;
....