用户授权

在数据库服务器上添加新的链接用户

grant授权

·授权:添加用户并设置权限

·命令格式

- grant 权限列表 on 库名 to 用户名@”客户端地址”
　　identified by“密码”　　//授权用户密码
　　with grant option;　　//有授权权限,可选项，不想让其他用户有grant权限，就不使用此行

mysql>grant all on db4.* to yaya@"%"
　　　　　　identified by "123qqq...A” ;

·权限列表

- all 　　　　//所有权限

- usage　　　　//无权限

- select , update, insert　　　　//个别权限

- select, update (字段1，... ..，字段N)　　//指定字段·库名

_*.*　　　　　　　　　　　　　　/所有库所有表

-库名 .* 　　　　　　　　　　//一个库

-库名.表名　　　　　　　　　　　//一张表

·用户名

-授权时自定义要有标识性

-存储在mysql库的user表里

·客户端地址

- % 　　　　　　　　 //所有主机

-192.168.4.% //网段内的所有主机-

-192.168.4.1 / /1台主机

-localhost　　　　　　//数据库服务器本机

应用示例

一添加用户mydba，对所有库、表有完全权限

–允许从任何客户端连接，密码123qqq...A

-且有授权权限

mysql> grant all on *.* to mydba@"%“
　　　　identified by "123qqq...A" with grant option ;

-添加admin用户，允许从192.168.4.0/24网段连接，对db3库的user表有查询权限，密码123qqq.….A

mysq> grant select on db3.user to
　　　　　　　　admin@"192.168.4.%"

　　　　　　　　　　　　identified by "123qqq...A";

-添加admin2用户，允许从本机连接，允许对db3库的所有表有查询/更新/插入/删除记录权限，密码
123qqq…..A

mysql> grant select,insert,update,delete on db3.*to
　　　　　　　　　　　　　　　　admin2@"localhost"

　　　　　　　　　　　　　　　　　　　　 identified by "123qqq.….A";

删除用户：：把新添加的用户删除

服务端

授权库：mysql库保存新添加的用户信息和权限信息

mysql库记录授权信息，主要表如下︰

-user　　　　　　　　表记录已有的授权用户及权限

- db表　　　　　　 记录已有授权用户对数据库的访问权限

- tables_priv表　　　　记录已有授权用户对表的访问权限

- columns priv　　　　表记录已有授权用户对字段的访问权限

查看表记录可以获取用户权限;也可以通过更新记录，修改用户权限

admin@"192.168.148.%"
-> identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant select,insert,update,delete on db3.* to
-> admin2@"localhost"
-> identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to mydba@"%"
-> identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on db4.* to yaya@"%"
-> identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
mysql> select host，user from mysql.user;
+---------------+-----------+
| host | f |
+---------------+-----------+
| % | mydba |
| % | yaya |
| 192.168.148.% | admin |
| localhost | admin2 |
| localhost | mysql.sys |
| localhost | root |
+---------------+-----------+
6 rows in set (0.00 sec)
mysql> show grants for mydba@"%";
+--------------------------------------------+
| Grants for mydba@% |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mydba'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.user where host="%" and user="mydba"\G;
*************************** 1. row ***************************
Host: % //所有主机
User: mydba //用户名
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: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30
password_expired: N
password_last_changed: 2022-02-20 20:45:21
password_lifetime: NULL
account_locked: N
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> select * from mysql.user where host="192.168.148.%" and user="admin"\G;
*************************** 1. row ***************************
Host: 192.168.148.% //148网段
User: admin //用户
Select_priv: N //无权限
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30
password_expired: N
password_last_changed: 2022-02-20 20:47:01
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

mysql> desc mysql.tables_priv \G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Grantor
Type: char(93)
Null: NO
Key: MUL
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Table_priv
Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
Null: NO
Key:
Default:
Extra:
*************************** 8. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
8 rows in set (0.00 sec)
mysql> select * from mysql.tables_priv;
+---------------+-----+-----------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+---------------+-----+-----------+------------+----------------+---------------------+------------+-------------+
| localhost | sys | mysql.sys | sys_config | root@localhost | 2022-02-13 15:09:29 | Select | |
| 192.168.148.% | db3 | admin | user | root@localhost | 0000-00-00 00:00:00 | Select | |
+---------------+-----+-----------+------------+----------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv where user="admin"\G;
*************************** 1. row ***************************
Host: 192.168.148.% //访问的148网段计算机
Db: db3 //库名
User: admin //用户
Table_name: user //表名
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select //只有查询权限
Column_priv:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show grants for admin@"192.168.148.%";
+---------------------------------------------------------+
| Grants for admin@192.168.148.% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.148.%' |
| GRANT SELECT ON `db3`.`user` TO 'admin'@'192.168.148.%' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> desc mysql.tables_priv \G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Grantor
Type: char(93)
Null: NO
Key: MUL
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Table_priv //字段名
Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References',
'Index','Alter','Create View','Show view','Trigger')
//可以设置权限类型
Null: NO
Key:
Default:
Extra:
*************************** 8. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
8 rows in set (0.00 sec)
mysql> update mysql.tables_priv set table_priv="select,update,drop,insert"
where user="admin";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin@"192.168.148.%";
+-------------------------------------------------------------------------------+
| Grants for admin@192.168.148.% |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'192.168.148.%' |
| GRANT SELECT, INSERT, UPDATE, DROP ON `db3`.`user` TO 'admin'@'192.168.148.%' |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv \G;
*************************** 1. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Table_name: sys_config
Grantor: root@localhost
Timestamp: 2022-02-13 15:09:29
Table_priv: Select
Column_priv:
*************************** 2. row ***************************
Host: 192.168.148.%
Db: db3
User: admin
Table_name: user
Grantor: root@localhost
Timestamp: 2022-02-20 21:28:46
Table_priv: Select,Insert,Update,Drop
Column_priv:
2 rows in set (0.00 sec)

ERROR:
No query specified

撤销权限：删除添加的用户对数据的访问权限

·命令格式
mysql> revoke 权限列表 on 库名.表 from
　　　　　　　　用户名@"客户端地址";

mysql> show grants for admin2@"localhost";
+-------------------------------------------------------------------------+
| Grants for admin2@localhost |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin2'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db3`.* TO 'admin2'@'localhost' |
+-------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke update,delete on db3.* from admin2@"localhost";
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for admin2@"localhost";
+---------------------------------------------------------+
| Grants for admin2@localhost |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin2'@'localhost' |
| GRANT SELECT, INSERT ON `db3`.* TO 'admin2'@'localhost' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

案例：用户授权

1.1 问题

允许192.168.4.0/24网段主机使用root连接数据库服务器，对所有库和所有表有完全权限、密码为123qqq…A 。
添加用户dba007，对所有库和所有表有完全权限、且有授权权限，密码为123qqq…A 客户端为网络中的所有主机。
撤销root从本机访问权限，然后恢复。
允许任意主机使用webuser用户连接数据库服务器，仅对webdb库有完全权限，密码为123qqq…A 。
撤销webuser的权限，使其仅有查询记录权限。

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：用户授权

1）允许root从192.168.4.0/24访问，对所有库表有完全权限，密码为123qqq…A

授权之前，从192.168.4.0/24网段的客户机访问时，将会被拒绝：

[root@host120 ~]# mysql -u root -p -h 192.168.4.10
Enter password:  								//输入正确的密码
ERROR 2003 (HY000): Host '192.168.4.120' is not allowed to connect to this MySQL server

授权操作，此处可设置与从localhost访问时不同的密码：

mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY '123qqq…A';
Query OK, 0 rows affected (0.00 sec)

再次从192.168.4.0/24网段的客户机访问时，输入正确的密码后可登入：

[root@host120 ~]# mysql -u root -p -h 192.168.4.10
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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>

从网络登入后，测试新建一个库、查看所有库：

mysql> CREATE DATABASE rootdb;  				//创建新库rootdb
Query OK, 1 row affected (0.06 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| home               |
| mysql              |
| performance_schema |
| rootdb             |                         //新建的rootdb库
| sys                |
| userdb             |
+--------------------+
7 rows in set (0.01 sec)

退出，再重新以root登入，测试一下看看，权限又恢复了吧：

mysql> exit  									//退出当前MySQL连接
Bye
[root@dbsvr1 ~]# mysql -u root -p 				//重新以root登入
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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 DATABASE newdb2014;  				//成功创建新库
Query OK, 1 row affected (0.00 sec)

4）允许webuser从任意客户机登录，只对webdb库有完全权限，密码为 123qqq…A

添加授权：

mysql> GRANT all ON webdb.* TO webuser@'%' IDENTIFIED BY '888888';
Query OK, 0 rows affected (0.00 sec)

查看授权结果：

mysql> SHOW GRANTS FOR webuser@'%';
+----------------------------------------------------+
| Grants for webuser@%                               |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'                |
| GRANT ALL PRIVILEGES ON `webdb`.* TO 'webuser'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

5）撤销webuser的完全权限，改为查询权限

撤销所有权限：

mysql> REVOKE all ON webdb.* FROM webuser@'%';
Query OK, 0 rows affected (0.00 sec)

只赋予查询权限：

mysql> GRANT select ON webdb.* TO webuser@'%';
Query OK, 0 rows affected (0.00 sec)

确认授权更改结果：

mysql> SHOW GRANTS FOR webuser@'%';
+--------------------------------------------+
| Grants for webuser@%                       |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'        |
| GRANT SELECT ON `webdb`.* TO 'webuser'@'%' |
+--------------------------------------------+
2 rows in set (0.00 sec)

1 简述用户授权命令的语法格式。

参考答案

GRANT  权限列表  ON  库名.表名  TO  用户名@'客户端地址'   [ IDENTIFIED BY '密码' ]  [ WITH GRANT OPTION ];

2 数据库授权综合练习，按题目要求写出对应的授权命令。

1、查看当前数据库服务器有哪些授权用户?

2、授权管理员用户可以在网络中的任意主机登录，对所有库和表有完全权限且有授权的权限登陆密码123456

3、授权webadmin用户可以从网络中的所有主机登录，对bbsdb库拥有完全权限，且有授权权限，登录密码为 123456

4、不允许数据库管理员在数据库服务器本机登录。

参考答案

1.select user from mysql.user;
2.grant all on *.*  to  root@“%” identified  by “123456” with grant option;
3.grant all  on  bbsdb.*  to   webadmin@“%” identified  by “123456” with grant option; grant insert  on  mysql.user to  webadmin@“%”;
4.delete from mysql.user where host in (“::1”,”127.0.0.1”,”localhost”,”stu.tedu.cn”) and user=”host”;flush privileges;

3 简述撤销用户授权命令的语法格式。

参考答案

revoke   权限revoke  权限列表  on  数据库名  from  用户名@”客户端地址”；

无法撤销用户的授权
报错1 : ERROR 1147 (42000)
报错2 : no such grant defined for user
mysql> revoke select on webdb.a from
'webadmin'@'172.40.51.106;
ERROR 1147 (42000): There is no such grant defined for user
'webadmin' on host '172 . 40.51.106' on table 'a'

原因分析

授权用户对目标没有权限

只有给用户对目标库做过授权才可以撤销权限
解决办法
查看用户的授权信息,看用户是否对库有权限
[root@dbsvr1 ~]# mysql -uroot -p123
mysq|> show grants for 'webadmin'@'172.40.51.106';

posted @ 2022-02-20 21:49 东山有耳阅读(341) 评论(0) 收藏举报

刷新页面返回顶部

东山有耳

用户授权

用户授权

grant授权

·授权:添加用户并设置权限

·命令格式

·权限列表

·用户名

·客户端地址

应用示例

相关命令

·登录用户使用

删除用户：：把新添加的用户删除

授权库：mysql库保存新添加的用户信息和权限信息

撤销权限：删除添加的用户对数据的访问权限

案例：用户授权

1.1 问题

1.2 步骤

1 简述用户授权命令的语法格式。

2 数据库授权综合练习，按题目要求写出对应的授权命令。

3 简述撤销用户授权命令的语法格式。

公告

东山有耳

用户授权

用户授权

grant授权

·授权:添加用户并设置权限

·命令格式

·权限列表

·用户名

·客户端地址

应用示例

相关命令

·登录用户使用

删除用户：：把新添加的用户删除

授权库：mysql库 保存新添加的用户信息和权限信息

撤销权限：删除添加的用户对数据的访问权限

案例：用户授权

1.1 问题

1.2 步骤

1 简述用户授权命令的语法格式。

2 数据库授权综合练习，按题目要求写出对应的授权命令。

3 简述撤销用户授权命令的语法格式。

公告

授权库：mysql库保存新添加的用户信息和权限信息