Mysql用户权限管理

root
233
文章
0
评论
2021年6月2日19:29:36 评论 5375字阅读17分55秒

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

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年6月2日19:29:36
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: