MySQL权限体系
1.MySQL数据库中验证用户，需要检查三项值：用户名，密码，来源主机；这三项的正确值保存在mysql库的user表中。
2.权限变更何时生效，如果是grant，revoke，set password，rename user 等MySQL提供的命令则立即生效，如果是通过insert，update，delete修改字典表的方式，则需要重启MySQL服务。
3.创建用户
mysql> create user json identified by 'Json@123'; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from mysql.user; +-----------+-----------+ | host | user | +-----------+-----------+ | % | json | | % | root | | localhost | mysql.sys | | localhost | root | +-----------+-----------+ 4 rows in set (0.00 sec)
4.修改用户密码
mysql> select user from mysql.user; +-----------+ | user | +-----------+ | json | | root | | mysql.sys | | root | +-----------+ 4 rows in set (0.02 sec) mysql> set password for json=password('Password@1234'); Query OK, 0 rows affected, 1 warning (0.00 sec)
设置密码过期：
mysql> alter user json password expire; Query OK, 0 rows affected (0.0 sec)
设置密码不过期：
mysql> alter user json password expire never; Query OK, 0 rows affected (0.00 sec)
创建用户指定登陆主机
mysql> create user json2@'192.168.1.18' identified by 'Password@123'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | json | % | | root | % | | json2 | 192.168.1.18 | | mysql.sys | localhost | | root | localhost | +-----------+--------------+ 5 rows in set (0.00 sec)
创建用户指定一个地址段登陆：
mysql> create user json5 @'192.168.1.%' identified by 'Password@123'; Query OK, 0 rows affected (0.04 sec)
修改密码报错：ERROR 1133 (42000): Can't find any matching row in the user table
mysql> select user,host from mysql.user; +-----------+--------------+ | user | host | +-----------+--------------+ | json | % | | root | % | | json5 | 192.168.1.% | | json2 | 192.168.1.18 | | json3 | 192.168.1.18 | | json2 | 192.168.1.19 | | json4 | 192.168.1.19 | | mysql.sys | localhost | | root | localhost | +-----------+--------------+ 9 rows in set (0.00 sec)
mysql> set password for json5=password('Password@1234');
ERROR 1133 (42000): Can't find any matching row in the user table
正确做法：要加host
mysql> set password for json5@'192.168.1.%'=password('Password@123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)
用grant方式创建用户
mysql> grant select on gis.* to ims@'192.168.1.%' identified by 'Password@123'; Query OK, 0 rows affected, 1 warning (0.02 sec)
通过插入user表创建用户
insert into user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values ('192.168.1.%','Json8',password('Password@123'),'','','');
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5、权限授予
用户管理的核心就是权限分配，MySQL数据库中授予权限的命令为GRANT，它不仅能授予权限，甚至还能创建用户。
mysql> grant select on mysql.user to json4@'192.168.1.19'; Query OK, 0 rows affected (0.00 sec)
查看用户的权限
mysql> show grants for json4@'192.168.1.19'; +----------------------------------------------------------+ | Grants for json4@192.168.1.19 | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO 'json4'@'192.168.1.19' | | GRANT SELECT ON `mysql`.`user` TO 'json4'@'192.168.1.19' | +----------------------------------------------------------+ 2 rows in set (0.00 sec)
回收用户的权限
mysql> revoke select on mysql.user from json4@192.168.1.19; Query OK, 0 rows affected (0.00 sec)
USAGE权限，这个权限的功能就是‘没有权限’，但不是完全没有权限，只有‘登陆权限’使用create user 创建的用户，只有USAGE权限。
一次性回收所有权限到USAGE
mysql> revoke all,grant option from json5@'192.168.1.%'; Query OK, 0 rows affected (0.00 sec)
删除用户
mysql> drop user json5@'192.168.1.%'; Query OK, 0 rows affected (0.00 sec)
6.权限级别
总的来说，MySQL数据库的权限从大到小的粒度上可以分为5类：全局、数据库、表、列、程序。通过对5个大类权限的细分，可以精确的为某个用户分配从某台机器连接进来访问某个数据库下某个表的某个列的某个部分记录权限。
全局权限：与全局相关的权限记录在mysql.user表中
mysql> grant create on *.* to json_global identified by 'Password@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from mysql.user where user='json_global'\G *************************** 1. row *************************** Host: % User: json_global Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: Y Drop_priv: N Reload_priv: N
[root@localhost ~]# mysql -ujson_global -p
可以查看所有的数据库：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gis |
| mysql |
| performance_schema |
| sanwei |
| sys |
+--------------------+
6 rows in set (0.02 sec)
mysql> use gis;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
可以创建表：
mysql> create table t_tx_zwyc_wlg(oid int,address varchar(20));
Query OK, 0 rows affected (0.09 sec)
删除表不行；
mysql> drop table t_tx_zwyc_wlg;
ERROR 1142 (42000): DROP command denied to user 'json_global'@'localhost' for table 't_tx_zwyc_wlg'
修改也不行
mysql> alter table t_tx_zwyc_wlg add (shape varchar(20));
ERROR 1142 (42000): ALTER command denied to user 'json_global'@'localhost' for table 't_tx_zwyc_wlg'
查看也不行
mysql> select * from t_tx_zwyc_wlg;
ERROR 1142 (42000): SELECT command denied to user 'json_global'@'localhost' for table 't_tx_zwyc_wlg'
数据库级别权限：数据库级别的权限信息记录在mysql.db表中。
数据库级别的权限，主要用于控制账户（user@host）操作某个数据库的权限，在这一粒度对用户做了授权以后，改用户就拥有了该数据库下所有对象的所有权限。
mysql> grant create on sanwei.* to json6 identified by 'Password@123'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from mysql.user where user='json6'\G
*************************** 1. row ***************************
Host: %
User: json6
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
mysql> select * from mysql.db where user='json6'\G
*************************** 1. row ***************************
Host: %
Db: sanwei
User: json6
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: Y
Drop_priv: N
Grant_priv: N
References_priv: N
用新建的用户连接数据库：
[root@localhost ~]# mysql -ujson6 -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sanwei |
+--------------------+
2 rows in set (0.00 sec)
表级别的权限，表对象授权信息报错在mysql.tables_priv字典表中；
mysql> grant all on gis.gisusers to json7 identified by 'Passowrd@123'; Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from mysql.tables_priv where user='json7'\G
*************************** 1. row ***************************
Host: %
Db: gis
User: json7
Table_name: gisusers
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv:
1 row in set (0.00 sec)
列级权限,保存在mysql.columns_priv
mysql> grant select (username) on gis.gisusers to json8 identified by 'Password@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from mysql.columns_priv; +------+-----+-------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +------+-----+-------+------------+-------------+---------------------+-------------+ | % | gis | json8 | gisusers | username | 0000-00-00 00:00:00 | Select | +------+-----+-------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec)
程序级别权限：这部分权限保存在mysql.procs_priv中；
mysql> desc mysql.procs_priv; +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | | | Grantor | char(93) | NO | MUL | | | | Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.01 sec)
浙公网安备 33010602011771号