学校网站制作软件,做行业网站,c语言做的网站,潍坊快速建站模板文章目录Mysql 的权限分类权限数据的存储每种权限的修改策略和作用范围刷新权限MySQL 提供的操作权限授权语法格式参考示例授予用户拥有某个数据库的全部权限授予用户拥有所有数据库的全部权限授予用户拥有某个数据库的部分权限授予用户拥有某个数据库中的某个表的部分权限授予…
文章目录Mysql 的权限分类权限数据的存储每种权限的修改策略和作用范围刷新权限MySQL 提供的操作权限授权语法格式参考示例授予用户拥有某个数据库的全部权限授予用户拥有所有数据库的全部权限授予用户拥有某个数据库的部分权限授予用户拥有某个数据库中的某个表的部分权限授予用户也具有给其它用户授权的权限授予用户操作表外键的权限授予用户创建临时表的权限授予用户操作索引的权限授予用户创建视图的权限授予用户查看视图源代码的权限授予创建存储过程和函数的权限授予用户 file 权限授予用户 super 权限授予用户 process 权限授予修改/删除存储过程和函数的权限授予执行/调用存储过程和函数的权限授予用户只能查询某个表的特定列授予用户执行指定存储过程的权限授予用户执行指定函数的权限授予某个用户拥有所有数据库的全部权限且拥有授权给其它账户的权限查看用户所拥有的权限Mysql 的权限分类
分为以下四种
1.全局权限
mysql grant all privileges on *.* to test% with grant option; # 赋予全部的权限
mysql grant select on *.* to test%; # 赋予查询的权限
mysql revoke all privileges on *.* from test%; # 撤销全部的权限说明通过通配符 *.* 赋予权限会往 mysql.user 表插入一条记录。
2.db权限
mysql grant all privileges on testdb.* to test% with grant option;
mysql revoke all privileges on testdb.* from test%;
mysql grant select on testdb.* to test%;
mysql revoke select on testdb.* from test%;说明通过 db_name.* 授予权限会往 mysql.db 表插入一条记录。
3.表权限
mysql grant all privileges on testdb.student to test% with grant option;
mysql grant select on testdb.student to test%;说明上述的授权语句会往 mysql.tables_priv 表插入一条记录。
4.列权限
mysql grant select(id), insert(id,a) on testdb.student to test% with grant option;说明上述的授权语句会往 mysql.columns_priv 表插入一条记录。
权限数据的存储
每种权限的数据都会在磁盘和内存中存储具体的存储位置为
1.全局权限 磁盘表 mysql.user 内存数组 acl_user
2.db权限 磁盘表 mysql.db 内存数组 acl_dbs
3.表权限 磁盘表 mysql.tables_priv 内存和列权限组成的 hash 结构 column_priv_hash
4.列权限 磁盘表 mysql.columns_priv 内存和表权限组成的 hash 结构 column_priv_hash
每种权限的修改策略和作用范围
1.全局权限
策略已存在的连接不生效新建立连接立即生效。即已经存在的会话不会起作用必须重新创建会话权限才会生效。
范围当前线程即重启服务后就失效了
2.db权限
策略所有连接立即生效但是已经存在的会话必须执行 use db_name 后权限才会生效。因为会话在执行 use db_name 数据库的相关权限会保存在会话变量中即使之后别的会话 revoke 或者 grant 权限也不会影响到该会话在切换出该 db 之前该会话会一直持有原来的权限
范围全局即重启服务后依旧有效
3.表权限
策略所有连接立即生效即已经存在的会话也会立刻生效
范围全局即重启服务后依旧有效
4.列权限 策略所有连接立即生效即已经存在的会话也会立刻生效
范围全局 即重启服务后依旧有效
刷新权限
对于全局权限FLUSH PRIVILEGES 操作会清空 acl_user 数组acl_dbs 数组column_priv_hash 集合然后从 mysql.user 表 mysql.db 表mysql.table_priv 表mysql.columns_priv 表读取数据到对应的内存对象中。所以说如果内存中的权限数据和磁盘表中的数据一致的话FLUSH PRIVILEGES 其实是可以不用做的。
使用 GRANT、REVOKE、SET PASSWORD、RENAME USER 等命令来更改用户的权限内存和磁盘中的数据都是同步更新的即 MySQL 服务器会注意到这些变化并立即将更新后的用户数据和权限数据加载至内存中所以执行上述这些命令后是不需要 FLUSH PRIVILEGES 的。
FLUSH PRIVILEGES 的使用场景
如果使用 INSERT、UPDATE、DELETE 等 DML 语句直接修改权限表mysql.user、mysql.db、mysql.tables_priv、mysql.columns_priv内存中的权限数据是不会同步更新的此时我们就需要重启服务器当 MySQL 启动时所有的权限都会被加载到内存中或者使用 FLUSH PRIVILEGES 命令来更新内存权限数据。即权限需在重启服务器或者 FLUSH PRIVILEGES 之后方可生效。
MySQL 提供的操作权限 另请参见官方文档https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
授权语法格式
给用户授权的语法格式
mysql GRANT permission1, permission2, ... ON database_name.table_name TO user_namehost;
mysql GRANT ALL PRIVILEGES ON database_name.table_name TO user_namehost WITH GRANT OPTION;说明: 1.GRANT 表示授予的含义 2.permission1, permission2, ... 表示授予的具体权限如 SELECT、INSERT、UPDATE 等权限之间使用逗号分隔如果要授予所的权限则使用 ALL PRIVILEGES 或者 ALL 3.ON 表示这些权限对哪些数据库和表生效格式【数据库名.表名】这里写 *.* 表示所有数据库的所有表。如果要指定将权限应用到test 库的 user 表中可以这么写test.user 4.database_name 数据库名 5.table_name 表名 6.TO 将权限授予哪个用户。格式【用户名登录IP或域名】 7.user_name 是用户名host 是主机名主机名可以指定为 %表示没有限制在任何主机都可以登录也可以指定为 192.168.0.%表示在 192.168.0.0 ~ 192.168.0.255 这个网段的主机上远程登录数据库服务器 8.user_name 和 host 可以使用单引号或者双引号引起来也可以直接省略掉 9.identified by 用来指定用户的登录密码 10.with grant option 表示允许用户将自己的权限授权给其它用户 11.对同一个用户多次添加权限时会和已有权限合并不会覆盖已有权限。即权限会自动叠加不会覆盖之前授予的权限比如你先给用户添加一个 SELECT 权限后来又给用户添加了一个 INSERT 权限那么该用户就同时拥有了 SELECT 和 INSERT 权限。
参考示例
授予用户拥有某个数据库的全部权限
mysql GRANT ALL PRIVILEGES ON database_name.* TO user_namehost;注 1.星号 * 是通配符表示匹配全部的表 2.PRIVILEGES 关键词可以省略
授予用户拥有所有数据库的全部权限
mysql GRANT ALL PRIVILEGES ON *.* TO user_namehost;注星号是通配符。第一个星号表示匹配任意的数据库第二个星号表示匹配任意的表。
授予用户拥有某个数据库的部分权限
mysql GRANT SELECT, INSERT, DELETE ON database_name.* TO user_namehost;
mysql grant select,update on test.* to lwx37.114.28.114;注用户表中虽然 user 和 host 是两个字段但是授权的时候必须通过 userhost 来指定被授权的用户因为这个两个字段是联合主键两个字段联合起来必须唯一。
授予用户拥有某个数据库中的某个表的部分权限
例如授予用户 pig% 拥有数据库 test 中的表 user 的查询、插入权限
mysql GRANT SELECT, INSERT ON test.user TO pig%; 授予用户也具有给其它用户授权的权限
语法格式
mysql GRANT permission1, permission2, ... ON database_name.table_name TO user_namehost WITH GRANT OPTION; 例如授予用户 liaowenxiong% 可以给其它用户授予针对数据库 test 中的表 student 的查询、插入的权限的权限
mysql GRANT select,insert ON test.student TO liaowenxiong% WITH GRANT OPTION;授予用户操作表外键的权限
mysql grant references on qydpw.* to ‘lwx’‘192.168.0.%’;授予用户创建临时表的权限
mysql grant create temporary tables on qydpw.* to ‘lwx’‘192.168.0.%’;授予用户操作索引的权限
mysql grant index on qydpw.* to ‘developer’‘192.168.0.%’;授予用户创建视图的权限
mysql grant create view on qydpw.* to ‘developer’‘192.168.0.%’;授予用户查看视图源代码的权限
mysql grant show view on qydpw.* to ‘developer’‘192.168.0.%’;授予创建存储过程和函数的权限
mysql grant create routine on qydpw.* to ‘developer’‘192.168.0.%’;
mysql grant create routine on qydpw.* to ‘lwx’‘127.0.0.1’;授予用户 file 权限
拥有 file 权限才可以执行 select into outfile 和 load data infile 语句。
mysql grant file on *.* to ‘lwx’‘localhost’;授予用户 super 权限
拥有 super 权限用户可以终止任何查询使用 set 语句修改系统变量使用 CHANGE MASTER 和 PURGE MASTER LOGS。
mysql grant super on *.* to ‘lwx’‘localhost’;授予用户 process 权限
通过这个权限用户可以执行 SHOW PROCESSLIST 和 KILL 命令。默认情况下每个用户都可以执行 SHOW PROCESSLIST 命令但是只能查询本用户的进程。
mysql SHOW PROCESSLIST;
-----------------------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info |
-----------------------------------------------------------------------------------------
| 2515 | root | localhost | qydpw | Query | 0 | starting | SHOW PROCESSLIST |
| 2596 | qydpw | 47.114.59.224:46182 | qydpw | Sleep | 1779 | | NULL |
| 2597 | qydpw | 47.114.59.224:46192 | qydpw | Sleep | 1689 | | NULL |
| 2598 | qydpw | 47.114.59.224:46194 | qydpw | Sleep | 1686 | | NULL |
| 2599 | qydpw | 47.114.59.224:46196 | production | Sleep | 1648 | | NULL |
| 2600 | qydpw | 47.114.59.224:46208 | production | Sleep | 1505 | | NULL |
| 2601 | qydpw | 47.114.59.224:46212 | qydpw | Sleep | 1444 | | NULL |
| 2602 | qydpw | 47.114.59.224:46218 | qydpw | Sleep | 1333 | | NULL |
| 2603 | qydpw | 47.114.59.224:46230 | production | Sleep | 1104 | | NULL |
| 2604 | qydpw | 47.114.59.224:46234 | production | Sleep | 1066 | | NULL |
| 2605 | qydpw | 47.114.59.224:46244 | qydpw | Sleep | 833 | | NULL |
| 2606 | qydpw | 47.114.59.224:46246 | qydpw | Sleep | 814 | | NULL |
| 2607 | qydpw | 47.114.59.224:46250 | production | Sleep | 792 | | NULL |
| 2608 | qydpw | 47.114.59.224:46252 | production | Sleep | 788 | | NULL |
| 2609 | qydpw | 47.114.59.224:46256 | production | Sleep | 727 | | NULL |
| 2610 | qydpw | 47.114.59.224:46258 | qydpw | Sleep | 692 | | NULL |
| 2611 | qydpw | 47.114.59.224:46268 | production | Sleep | 489 | | NULL |
| 2612 | qydpw | 47.114.59.224:46270 | qydpw | Sleep | 480 | | NULL |
| 2613 | qydpw | 47.114.59.224:46274 | qydpw | Sleep | 416 | | NULL |
| 2614 | qydpw | 47.114.59.224:46280 | production | Sleep | 323 | | NULL |
| 2615 | qydpw | 47.114.59.224:46290 | production | Sleep | 90 | | NULL |
-----------------------------------------------------------------------------------------注意super、process、file 等权限不能够指定某个数据库on 后面必须跟 *.*如下示例
mysql grant super on pyt.* to lwxlocalhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql grant super on *.* to lwxlocalhost;
Query OK, 0 rows affected (0.01 sec)授予修改/删除存储过程和函数的权限
mysql grant alter routine on qydpw.* to developer192.168.0.%; 授予执行/调用存储过程和函数的权限
mysql grant execute on qydpw.* to developer192.168.0.%;授予用户只能查询某个表的特定列
mysql grant select(advice_id,user_id) on qydpw.tf_advice to lwxlocalhost;授予用户执行指定存储过程的权限
例如授予用户执行存储过程 sys.diagnostics 的权限
mysql grant execute on procedure sys.diagnostics to lwx127.0.0.1;授予用户执行指定函数的权限
例如授予用户执行函数 sys.ps_thread_account 的权限
mysql grant execute on function sys.ps_thread_account to lwx127.0.0.1;授予某个用户拥有所有数据库的全部权限且拥有授权给其它账户的权限
GRANT ALL PRIVILEGES ON *.* TO user_namehost WITH GRANT OPTION; 查看用户所拥有的权限
查看其它用户的权限
mysql SHOW GRANTS FOR user_namehost;例如查看用户 liaowenxiong% 所拥有的权限
mysql show grants for liaowenxiong%;
--------------------------------------------------------------------------
| Grants for liaowenxiong% |
--------------------------------------------------------------------------
| GRANT USAGE ON *.* TO liaowenxiong% |
| GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO liaowenxiong% |
--------------------------------------------------------------------------
2 rows in set (0.00 sec)查看当前登录用户自己的权限
mysql show grants;
