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; ....
继续阅读

我的微信
这是我的微信扫一扫
评论