015、数据库管理之用户和权限

用户和权限

- 认证与赋权
- 连接过程
- 本地连接
- 远程连接
- 查看用户信息
- 创建用户账号
- 创建角色
- 管理用户账户
- 管理角色
- 设置账号密码
- 忘记root密码
- 实验1-用户和角色
- 实验2-授权注意事项

认证与赋权

认证：对用户进行验证
1. 是权限控制的第一步
2. 当用户第一次连接数据库时必须进行认证
3. 如果认证失败则无法连接数据库
授权：对用户的权限进行验证
1. 这个权限控制的第二步
2. TiDB 将会决定用户是否有权限进行想做的操作。

连接过程

在这里插入图片描述

本地连接

在这里插入图片描述
使用mysql客户端从本地服务器连接TiDB数据库，需要执行用户名和密码

mysql -uusername -ppassword -hlocalhost -Pport

远程连接

在这里插入图片描述
使用mysql客户端从远程机器连接TiDB数据库，需要执行用户名和密码

mysql -uusername -ppassword -hip -Pport

查看用户信息

mysql> select user,host,authentication_string from mysql.user;
+------+------+-----------------------+
| user | host | authentication_string |
+------+------+-----------------------+
| root | %    |                       |
+------+------+-----------------------+
1 row in set (0.00 sec)

创建用户账号

注意：用户名和密码都会大小写敏感

create user 'test'@'127.0.0.1' identifid by '1234';
create user 'test';

创建角色

创建2个角色r_admin和r_dev@localhost:
角色名，大小写敏感
需要带上客户端主机名和IP地址
如果主机名被忽略则默认是’%’
用户与角色差异：

是被锁住的
没有密码
被存储在mysql.user表中
用户登录后，必须使用set role all命令开启用户被赋予的角色。

管理用户账户

mysql> revoke all privileges on *.* from 'user1'@'localhost';
Query OK, 0 rows affected (0.10 sec)mysql> rename user 'test'@'localhost' to 'user1'@'localhost';
Query OK, 0 rows affected (0.14 sec)mysql> grant all privileges on *.* to 'user1'@'localhost' with grant option;
Query OK, 0 rows affected (0.12 sec)mysql> drop user 'user1'@'localhost';
Query OK, 0 rows affected (0.15 sec)

管理角色

1、 赋予角色权限:
grant select on 'test'.* to 'r_dev'@'localhost';
2、将角色授予用户
grant 'r_admin' to 'user1'@'localhost';
3、查看角色拥有的权限
show grants for 'dev'@'localhost';4、回收角色权限
revoke insert,update,delete on 'test'.'*' from 'r_dev'@'localhost';5、删除角色
drop role 'r_admin'@'localhost';

设置账号密码

1、create user 创建密码
CREATE USER 'TEST'@'localhost' identified by 'mypass'
2、为存在的帐号修改密码
set password for 'test'@'localhost' = 'mypasswd'
alter user 'test'@'localhost' identified by 'mypasswd'

忘记root密码

1、修改配置文件
[security]
skip-grant-table = ture
2、重启数据库后生效
mysql -uroot -p -P 4000

实验1-用户和角色

1、创建用户和角色
[root@tidb ~]# mysql --host 192.168.16.12 --port 4000 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 415
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create user 'hulk'@'192.168.16.12' identified by 'pingcap';
Query OK, 0 rows affected (0.12 sec)mysql> create role r_manager,r_staff;
Query OK, 0 rows affected (0.11 sec)2、 查看用户和角色
用户和角色都被存储到mysql.user 表中。
角色是没有密码的角色是被锁定的
角色没有密码mysql> select user,host,authentication_string from mysql.user \G;
*************************** 1. row ***************************user: roothost: %
authentication_string: 
*************************** 2. row ***************************user: hulkhost: 192.168.16.12
authentication_string: *926E4B88EB93FD344DF0870EE025D6EB153C02DE
*************************** 3. row ***************************user: r_managerhost: %
authentication_string: 
*************************** 4. row ***************************user: r_staffhost: %
authentication_string: 
4 rows in set (0.00 sec)ERROR: 
No query specifiedmysql> select * from mysql.user where user='r_staff' \G;
*************************** 1. row ***************************Host: %User: r_staffauthentication_string: plugin: mysql_native_passwordSelect_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NProcess_priv: NGrant_priv: NReferences_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NIndex_priv: NCreate_user_priv: NEvent_priv: NRepl_slave_priv: NRepl_client_priv: NTrigger_priv: NCreate_role_priv: NDrop_role_priv: NAccount_locked: YShutdown_priv: NReload_priv: NFILE_priv: NConfig_priv: N
Create_Tablespace_Priv: N
1 row in set (0.03 sec)ERROR: 
No query specifiedmysql> alter user 'hulk'@'192.168.16.12' identified by 'tidb';
Query OK, 0 rows affected (0.10 sec)mysql> exit
Bye
[root@tidb ~]# mysql -h192.168.16.12 -P 4000 -uhulk -ptidb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 417
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exit
Bye
[root@tidb ~]# mysql --host 192.168.16.12 --port 4000 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 419
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.3、删除用户和角色
mysql> drop role t_staff;
ERROR 1396 (HY000): Operation DROP ROLE failed for t_staff@%
mysql> drop role r_staff;
Query OK, 0 rows affected (0.13 sec)mysql> drop role r_manager;
Query OK, 0 rows affected (0.15 sec)mysql> drop user 'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.14 sec)mysql>

实验2-授权注意事项

创建对象
mysql> create table emp(id int,name varchar(20));
Query OK, 0 rows affected (0.15 sec)mysql> insert into emp values(1,'tom');
Query OK, 1 row affected (0.03 sec)mysql> insert into emp values(2,'jack');
Query OK, 1 row affected (0.02 sec)创建用户
mysql> create user 'hulk'@'192.168.16.12' identified by 'pingcap';
Query OK, 0 rows affected (0.22 sec)创建角色
mysql> create role r_mgr,r_emp;
Query OK, 0 rows affected (0.09 sec)mysql> grant select on test.emp to r_emp;
Query OK, 0 rows affected (0.15 sec)授予权限
mysql> grant insert ,update,delete on test.* to r_mgr;
Query OK, 0 rows affected (0.10 sec)授予角色，注意不是立即生效
mysql> grant r_emp to r_mgr,'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.11 sec)mysql> mysql> create table dept(id int ,dname varchar(20));
Query OK, 0 rows affected (0.16 sec)mysql> insert into dept values(1,'dev');
Query OK, 1 row affected (0.08 sec)mysql> insert into dept values(2,'sales');
Query OK, 1 row affected (0.04 sec)mysql> grant select on test.dept to 'hulk'@'192.168.16.12';
Query OK, 0 rows affected (0.34 sec)

指定用户登录
[root@tidb ~]# mysql --host 192.168.16.12 -P4000 -uhulk -ppingcap
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 409
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
角色包含的权限没有生效
mysql> select * from emp;
ERROR 1142 (42000): SELECT command denied to user 'hulk'@'192.168.16.12' for table 'emp'
mysql> select * from dept;
+------+-------+
| id   | dname |
+------+-------+
|    1 | dev   |
|    2 | sales |
+------+-------+
2 rows in set (0.02 sec)

用户’hulk’@‘192.168.16.12’ 无法查询emp，因为权限是通过角色r_emp赋予的，但这个角色并没有在会话总开启。
用户’hulk’@‘192.168.16.12’ 可以查询表dept，因为权限是直接赋予用户的。

----查看当前角色并没有
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.09 sec)mysql> show grants;
+-----------------------------------------------------+
| Grants for User                                     |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'hulk'@'192.168.16.12'        |
| GRANT SELECT ON test.dept TO 'hulk'@'192.168.16.12' |
| GRANT 'r_emp'@'%' TO 'hulk'@'192.168.16.12'         |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

--- 角色生效
mysql> set role roll;
ERROR 3530 (HY000): `roll`@`%` is not granted to hulk@192.168.16.12
mysql> set role all;
Query OK, 0 rows affected (0.02 sec)mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `r_emp`@`%`    |
+----------------+
1 row in set (0.01 sec)mysql> show grants;
+-----------------------------------------------------+
| Grants for User                                     |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'hulk'@'192.168.16.12'        |
| GRANT SELECT ON test.dept TO 'hulk'@'192.168.16.12' |
| GRANT SELECT ON test.emp TO 'hulk'@'192.168.16.12'  |
| GRANT 'r_emp'@'%' TO 'hulk'@'192.168.16.12'         |
+-----------------------------------------------------+
4 rows in set (0.00 sec)mysql> select * from emp;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | jack |
+------+------+
2 rows in set (0.02 sec)

015、数据库管理之用户和权限

用户和权限

认证与赋权

连接过程

本地连接

远程连接

查看用户信息

创建用户账号

创建角色

管理用户账户

管理角色

设置账号密码

忘记root密码

实验1-用户和角色

实验2-授权注意事项

相关文章

【案例实战】SpringBoot整合Redis实现缓存分页数据查询

笔记本电脑的键盘灯如何关

机械键盘插入linux系统中，机械键盘背光灯不亮，并且键盘快捷键无作用

计算机键盘灯光怎么关闭,笔记本怎么关键盘的灯_笔记本电脑关闭键盘背光的步骤-win7之家...

电脑键盘灯光的调节方法

笔记本禁用键盘

送别用计算机怎么按,电脑键盘上的三个灯离别是什么作用？

关闭笔记本小键盘灯