MySQL列权限授予及注意事项

妖狐艹你老母 2022-04-15 00:35 215阅读 0赞

对于权限的授予，库和表级别的都比较常见，但具体到表的某个列的访问权限怎么授予呢？创建视图？是否也可以按照独立的列授予insert|delete|update的权限呢？  
其实，MySQL对列的授权，是有专门的语法实现的，例如授予test库priv\_test表的id列的select权限给sam用户，可以用以下语法实现：  
grant select(id) on test.priv\_test to sam@‘localhost’;

mysql> select * from priv_test;
    +------+------+
    | id   | id2  |
    +------+------+
    |    1 |    1 |
    +------+------+
    1 row in set (0.00 sec)
    
    mysql> grant select(id) on test.priv_test to sam@'localhost';
    Query OK, 0 rows affected (0.15 sec)
    
    mysql> show grants for sam@'localhost';
    +----------------------------------------------------------------+
    | Grants for sam@localhost                                       |
    +----------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `sam`@`localhost`                        |
    | GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
    +----------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@linux-base ~]# mysql -usam -psam123
    mysql: [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 64
    Server version: 8.0.12 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    +--------------------+
    2 rows in set (0.00 sec)
    
    mysql> use test;
    Database changed
    mysql> desc priv_test;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from priv_test;
    ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'
    mysql> select id from priv_test; 
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)

既然可以为单独的列授予select权限，那么DML操作呢？例如insert能不能用以下语法对单独的列授权呢？  
grant insert(id) on test.priv\_test to sam@‘localhost’;

mysql> show grants for sam@'localhost';
    +----------------------------------------------------------------+
    | Grants for sam@localhost                                       |
    +----------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `sam`@`localhost`                        |
    | GRANT SELECT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
    +----------------------------------------------------------------+
    2 rows in set (0.01 sec)
    
    mysql> grant insert(id) on test.priv_test to sam@'localhost';
    Query OK, 0 rows affected (0.15 sec)
    
    mysql> show grants for sam@'localhost';
    +-------------------------------------------------------------------------------+
    | Grants for sam@localhost                                                      |
    +-------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `sam`@`localhost`                                       |
    | GRANT SELECT (`id`), INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
    +-------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@linux-base ~]# mysql -usam -psam123
    mysql: [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 70
    Server version: 8.0.12 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, 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 test;
    Database changed
    mysql> insert into priv_test(id) values (1);
    Query OK, 1 row affected (0.14 sec)
    
    mysql> insert into priv_test(id2) values (1); 
    ERROR 1143 (42000): INSERT command denied to user 'sam'@'localhost' for column 'id2' in table 'priv_test'
    mysql> select id from priv_test;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select * from priv_test;
    ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'

确实可以，不错不错。那么，是否会存在一种情况：对一个列有insert的权限，但没有select的权限？那么插入数据之后，还能不能看由自己插入的东西呢？

mysql> select * from priv_test;
    +------+------+
    | id   | id2  |
    +------+------+
    |    1 |    1 |
    +------+------+
    1 row in set (0.03 sec)
    
    mysql> revoke SELECT (`id`) ON `test`.`priv_test` from `sam`@`localhost`;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> show grants for sam@'localhost';
    +----------------------------------------------------------------+
    | Grants for sam@localhost                                       |
    +----------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `sam`@`localhost`                        |
    | GRANT INSERT (`id`) ON `test`.`priv_test` TO `sam`@`localhost` |
    +----------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@linux-base ~]# mysql -usam -psam123
    mysql: [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 72
    Server version: 8.0.12 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, 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 test;
    Database changed
    mysql> insert into priv_test(id) values (2);
    Query OK, 1 row affected (0.14 sec)
    
    mysql> select id from priv_test;
    ERROR 1142 (42000): SELECT command denied to user 'sam'@'localhost' for table 'priv_test'

可以看到，有insert没select，就会出现能插入，但无法查看数据。在权限授予上需要注意。  
另外即使删除了表，用户拥有的权限也不会自动回收的，这也是一个安全隐患的地方。