?

?

針對Mike賬號 db1庫下面的t1表的 id，name字段授予select權限，age字段授予update權限

授權格式 select(要授權的字段，要授權的字段) 用戶括號 括起來? 、update()

mysql> grant select(id,name),update(age) on db1.t1 to 'mike'@'localhost'; Query OK, 0 rows affected (0.11 sec)

?

授權的記錄

mysql> select * from mysql.columns_priv; +-----------+-----+------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +-----------+-----+------+------------+-------------+---------------------+-------------+ | localhost | db1 | mike | t1 | id | 0000-00-00 00:00:00 | Select | | localhost | db1 | mike | t1 | name | 0000-00-00 00:00:00 | Select | | localhost | db1 | mike | t1 | age | 0000-00-00 00:00:00 | Update | +-----------+-----+------+------------+-------------+---------------------+-------------+ 3 rows in set (0.00 sec)

?

驗證

?

mysql> exit Bye[root@mysql ~]# mysql -umike -p123 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 10 Server version: 5.6.36 Source distributionCopyright (c) 2000, 2017, 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> use db1; 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> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec)mysql> select * from t1; ERROR 1142 (42000): SELECT command denied to user 'mike'@'localhost' for table 't1'mysql> select id,name from t1; +------+------+ | id | name | +------+------+ | 1 | mike | | 2 | alex | | 3 | NULL | | 4 | NULL | +------+------+ 4 rows in set (0.00 sec)

*代表所有字段

只能查看t1表中的 id，name字段

?

不能查age字段 但可以用update age字段

mysql> select age from t1; ERROR 1143 (42000): SELECT command denied to user 'mike'@'localhost' for column 'age' in table 't1'

?

轉載于:https://www.cnblogs.com/mingerlcm/p/9925792.html

總結

以上是生活随笔為你收集整理的mysql 权限管理 针对表的字段 级别 授权 columns_priv表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。