当前位置: 首页 > news >正文

安全用户角色权限

$PATH
搞系统设置设置⾥头path
⽬标包含mysql 可执⾏⽂件,那么就是由使⽤
在终端使⽤
./bin/mysql -h192.168.71.164 -P3306 -uroot -proot
1.远程登录前提条件是mysql.user表中的host属性为%,如果是
localhost就不允许远程登录,update mysql.user set host="%"
where user="root",flush privileges;
2.远程管理,可以使⽤图形化⼯具,sqlyog,navicat,掌握命令⼯
具,客户端⼯具 mysql
3.mysql -h192.168.71.129 -P3306 -uzhangmin -pZhang_min123
-h 主机 ip或者是域名 如果是localshost或者是127.0.0.1可省略
-P 端⼝ 默认是3306,如果是默认的,可以省略
-u ⽤户名
-p 密码,可以不换⾏直接输⼊,也可以换⾏ 不回显输⼊密码
创建账户
create user 'zhangmin'@'%' identified by
'Zhang_min123';
给权限
grant all on *.* to 'zhangmin'
创建库
create database if not exists test;
创建表
use test;
create table user(
id int primary key,
username varchar(45) not null,
password varchar(45) not null
);
添加数据
insert into test.user values(1,"zhangsan","123");
insert into test.user values(2,"lisi","456");
insert into test.user values(3,"wamngwi","789");
insert into test.user values(4,"zhaoliu","aaa");
添加lilaosi账号,修改密码,查看mysql.user中的lilaosi的信息
mysql> create user 'lilaosi'@'%' identified by
'lilaoshi_123';
ERROR 1819 (HY000): Your password does not satisfy
the current policy requirements
mysql> create user 'lilaosi'@'%' identified by
'Lilaoshi_123';
Query OK, 0 rows affected (0.01 sec)mysql> alter user 'lilaosi'@'%' identified by
'Lilaosi_123';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | lilaosi |
| % | root |
| % | zhangmin |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
6 rows in set (0.00 sec)
使⽤root账号,为lilaosi账号添加test库存中所有的表的所有权限
grant all on test.* to 'lilaosi';
# lilaosi就获得了test库中所有的表的操作权限,但是,由于
root没有个lilaosimysql库的权限,所以lilaosi账号⽆法查看
mysql库
1.密码安全策略
查看密码策略
mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| 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.00 sec)
修改策略
mysql> set global validate_password.length=0;
mysql> set global validate_password.policy=LOW;
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 4 |
| validate_password.mixed_case_count | 0 |
| validate_password.number_count | 0 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 0 |
+--------------------------------------+-------+
2.⽤户
创建⽤户
练习
创建三个账号,abc[abcd],ccc[a1b2c3] ,ddd[231343]
<mysql> create user 'efg'@'%' identified by 'efg';
ERROR 1819 (HY000): Your password does not satisfy
the current policy requirements
mysql> create user 'efgh'@'%' identified by 'efgh';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | efgh |
| % | root |
| % | zhangmin |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | test1 |
+-----------+------------------+
删除⽤户
mysql> drop user 'zhangmin';
Query OK, 0 rows affected (0.02 sec)
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| efgh |
| root |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| test1 |
+------------------+
6 rows in set (0.00 sec)
修改⽤户
mysql> alter user 'zhangmin' identified by
'abc123';
Query OK, 0 rows affected (0.01 sec)
练习
abc。ccc。ddd三个账号的密码修改为1234
查看⽤户
3.⻆⾊
创建⻆⾊
mysql> create role 'a';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'a';
+-------------------------------+
| Grants for a@% |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)
1.添加jingli⻆⾊
create role 'jingli';
2.添加yuangong⻆⾊
craete role 'yaungong';
3.为jingli添加select insert delete update权限
grant select ,insert,delete,update on test.user to
'jingli';
4.为yuangong添加select,insert权限
grant select,insert on test.user to 'yuangong';
5.查看⻆⾊保存的表格
selet host,user from mysql.user;
6.查看⻆⾊的权限
show grants for 'jingli';
show grants for 'yaungogng';
新增bbb和ccc两个⽤户bbb是经理需要增删改查权限,ccc是员⼯是
只需要新增和查看的权限
grant jingli to ‘bbb’;
grant yuangong to 'ccc';
查看⻆⾊
修改⻆⾊
删除⻆⾊
4.权限
刷新权限
为root账号添加权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> grant system_user on *.* to "root";
mysql> show grants for 'root';+
-------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
| Grants for root@%
|
+--------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,
DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,
INDEX, ALTER, SHOW DATABASES, SUPER, CREATE
TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION
SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT,
TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE
ON *.* TO `root`@`%` WITH GRANT OPTION |
| GRANT SYSTEM_USER ON *.* TO `root`@`%`
|
+--------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
------------------------------+
2 rows in set (0.00 sec)
新增权限
修改权限
mysql> show grants for "efgh";
+--------------------------------------------------
-+
| Grants for efgh@%
|
+--------------------------------------------------
-+
| GRANT SELECT, INSERT, DELETE ON *.* TO `efgh`@`%`
|
+--------------------------------------------------
-+
1 row in set (0.00 sec)
练习步骤
1.添加aaa账户,设置密码aaaa
drop user aaa;
create user 'aaa'@'%' identified by 'aaaa';
2.使⽤aaa账户访问mysql服务
mysql -h127.0.0.1 -P3306 -uaaa -paaaa3.查看test数据库发现么有权限
show databases;
4.退出并使⽤root账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
5.为aaa账户添加查看test.user表的权限
grant select on test.user to 'aaa';
6.退出root,使⽤aaa账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
7.查看数据库,查看表,查看表内容 能够正常查看
show databases;
user test;
show tables;
select * from user;
8.输⼊数据,没有权限
insert into user values(5,"ermazi","ermazi");####
9.退出aaa使⽤root登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
10.为aaa添加insert权限
grant insert on test.user to 'aaa';
11.退出root使⽤aaa登录
exit|quit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
12.向user表添加⼀⾏新的数据
查看权限
删除权限
insert into test.user
values(6,"zhangsanfeng","zhangsanfen");
13.修改user中⼀⾏的数据的password(密码)为111,没有
update权限
update test.user set password='zsf' where username-
'zhangsanfeng';
mysql> revoke all on *.* from "efgh";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "efgh";
+----------------------------------+
| Grants for efgh@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `efgh`@`%` |
+----------------------------------+
1 row in set (0.00 sec)
http://www.lryc.cn/news/415926.html

相关文章:

  • 代理模式学习
  • 深入理解Go 语言信号量 Semaphore
  • VisualStudio2019下载与安装
  • 李宏毅老师机器学习常见英语词汇
  • 人工智能时代,程序员如何保持核心竞争力?
  • WiFi to Ethernet: 树莓派共享无线连接至有线网口,自动通过Captive Poartal网页登录认证
  • 【神软大数据治理平台-高级动态SQL(接口开发)】
  • 【Java数据结构】Map和Set超详细两万字讲解(内含搜索树+哈希表)
  • 中国制造2025,会抛弃精益生产吗?
  • Rust 循环
  • 数据结构(其四)--特殊矩阵的存储
  • 系统化学习 H264视频编码(06)哥伦布编码
  • 手机在网状态接口如何对接?(一)
  • 数据结构链表2(常考习题1)(C语言)
  • Rust的运行时多态
  • sqllabs通关
  • RTSP系列四:RTSP Server/Client实战项目
  • sqli-labs-php7-master第11-16关
  • c++初阶 string的底层实现
  • 微信小程序实现上传照片功能
  • lombok安装成功但是找不到方法
  • 单细胞Seurat的umi矩阵-与feature、counts(用于质控)
  • 安防视频监控EasyCVR视频汇聚平台设备发送了GPS位置,但是订阅轨迹为空是什么原因?
  • 在 VueJS 中使用事件委托处理点击事件(事件委托,vue事件委托,什么是事件委托,什么是vue的事件委托)
  • 密码学简史:时间密语
  • 【Java数据结构】---初始数据结构
  • MySQL--主从复制
  • Linux RT调度器之负载均衡
  • pwn学习笔记(8)--初识Pwn沙箱
  • Day18_2--Vue.js Ajax(使用 Axios)基础入门学习