您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本文主要给大家介绍MySQL列权限管理讲义，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，亿速云累计多年的实践经验可分享给大家。
列权限管理
MySQL是由列级别权限存在的。这也体现了MySQL高级特性。实现了限制用户对表上特定列的访问权限。
一般都是实现对表级别不具备访问权限，但是对某些列有访问权限。当然也存在其他情形。1# 列权限相关的字典表：
(root@localhost)[mysql]> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec) 总共7列，很好理解。一条特定的列级别权限共需要定义5个维度，host+db+user+table+column。可授予的
权限种类分为4中，select, insert, update, refernces。其中前3项已经投入使用，references在5.6中还未正式
生效。2# 授权方法
列权限的授权方法和其他维度的授权方法有些许的差异，因为并非按照想像中会用on db.table.column这样的形式，而是将列名附带在授权种类之后：
测试update，确认没有update权限在name列上，表上也没有。
(test1@localhost)[sample2]> update smp set name='bbb';
ERROR 1142 (42000): UPDATE command denied to user 'test1'@'localhost' for table 'smp'
(test1@localhost)[sample2]> 对name列授权update：(root@localhost)[mysql]> grant update (name) on sample2.smp to test1;
Query OK, 0 rows affected (0.00 sec) 再次尝试update name列，更新成功。(test1@localhost)[sample2]> update smp set name='bbb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 验证update id列，可以预见的失败：(test1@localhost)[sample2]> update smp set id=2;
ERROR 1143 (42000): UPDATE command denied to user 'test1'@'localhost' for column 'id' in table 'smp'3# 权限的查询：
4个方式，一个是show grants，另一个是跑sql查询字典表,DBA可以查询mysql.columns_priv, 普通用户可以查询information_schema.COLUMN_PRIVILEGES。两者有细微的差别，但主要列一样,第四种方式是查询mysql.tables_priv。
#1，直接show grants
(root@localhost)[mysql]> show grants for test1;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
| GRANT SELECT ON `sample2`.* TO 'test1'@'%' |
| GRANT ALL PRIVILEGES ON `sample`.* TO 'test1'@'%' WITH GRANT OPTION |
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%' |
| GRANT UPDATE (name) ON `sample2`.`smp` TO 'test1'@'%' |
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%' |
+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
#2，查询mysql.columns_priv;
(root@localhost)[mysql]> select * from mysql.columns_priv;
+------+---------+-------+------------+-------------+---------------------+-------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+------+---------+-------+------------+-------------+---------------------+-------------+
| % | sample2 | test1 | smp | name | 0000-00-00 00:00:00 | Update |
+------+---------+-------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)
#3，查询information_schema.COLUMN_PRIVILEGES
(root@localhost)[mysql]> select * from information_schema.COLUMN_PRIVILEGES;
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'test1'@'%' | def | sample2 | smp | name | UPDATE | NO |
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)
#4，查询mysql.tables_priv
(root@localhost)[mysql]> select * from mysql.tables_priv where db='sample2';
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
| % | sample2 | test1 | smp | root@localhost | 0000-00-00 00:00:00 | | Update |
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.00 sec) MySQL的程序（process/routine）
一个全局权限：CREATE ROUTINE，在user,db表中体现
三个对象级权限，主要分为procedure和function两个对象类型。对于程序而言他们的权限种类有
1，EXECUTE #执行权限
2，ALTER ROUTINE #修改权限
3，GRANT #授予权限
相关的字典表：(root@localhost)[mysql]> desc procs_priv; +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | | | Grantor | char(77) | NO | MUL | | | | Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
看了以上MySQL列权限管理讲义介绍，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，大家可以继续关注亿速云行业资讯板块，会定期给大家更新行业新闻和知识，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。