安全用户角色权限

$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)