加入收藏 | 设为首页 | 会员中心 | 我要投稿 聊城站长网 （https://www.0635zz.com/）- 智能语音交互、行业智能、AI应用、云计算、5G!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

如何在MySQL数据库中设定相应级别的访问权限

发布时间：2023-09-11 15:06:17 所属栏目：MySql教程 来源：
导读：本文主要给大家简单讲讲mysql.db数据库层权限该如何设置，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望mysql.db数据库层权限该如何设置这篇文章可以给大家带来
本文主要给大家简单讲讲mysql.db数据库层权限该如何设置，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望mysql.db数据库层权限该如何设置这篇文章可以给大家带来一些实际帮助。
 
1# 数据库层权限记录位置
 
表级别的权限记录在mysql.tables_priv表中。
 
(root@localhost)[mysql]> (root@localhost)[mysql]> desc tables_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 |                   |                             |
 
| Grantor     | char(77)                                                                                                                          | NO   | MUL |                   |                             |
 
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                             |
 
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   |     |                   |                             |
 
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
 
8 rows in set (0.00 sec)
 
然而关于数据库层面的权限记录在mysql.db表中
 
(root@localhost)[mysql]> desc db;
 
+-----------------------+---------------+------+-----+---------+-------+
 
| Field                 | Type          | Null | Key | Default | Extra |
 
+-----------------------+---------------+------+-----+---------+-------+
 
| Host                  | char(60)      | NO   | PRI |         |       |
 
| Db                    | char(64)      | NO   | PRI |         |       |
 
| User                  | char(16)      | NO   | PRI |         |       |
 
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
 
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
 
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
 
| References_priv       | enum('N','Y') | NO   |     | N       |       |
 
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
 
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
 
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
 
+-----------------------+---------------+------+-----+---------+-------+
 
22 rows in set (0.00 sec)
 
(root@localhost)[mysql]> select * from db\G
 
*************************** 1. row ***************************
 
                 Host: %
 
                   Db: sample
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: N
 
          Update_priv: N
 
          Delete_priv: N
 
          Create_priv: Y
 
            Drop_priv: N
 
           Grant_priv: N
 
      References_priv: N
 
           Index_priv: N
 
           Alter_priv: N
 
Create_tmp_table_priv: N
 
     Lock_tables_priv: N
 
     Create_view_priv: N
 
       Show_view_priv: N
 
  Create_routine_priv: N
 
   Alter_routine_priv: N
 
         Execute_priv: N
 
           Event_priv: N
 
         Trigger_priv: N
 
1 row in set (0.00 sec)
 
这条对应的grant语句是：
 
(root@localhost)[mysql]> show grants for test1;
 
+------------------------------------------------------------------------------------------------------+
 
| Grants for test1@%                                                                                   |
 
+------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
 
| GRANT SELECT, CREATE ON `sample`.* TO 'test1'@'%'                                                    |
 
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%'                                                |
 
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%'                                                        |
 
+------------------------------------------------------------------------------------------------------+
 
4 rows in set (0.00 sec)
 
第二条：
 
GRANT SELECT, CREATE ON sample.* TO 'test1'@'%'
 
尝试再增加权限：
 
(root@localhost)[mysql]> grant all privileges on sample.* to test1;
 
Query OK, 0 rows affected (0.00 sec)
 
(root@localhost)[mysql]>
 
(root@localhost)[mysql]>
 
(root@localhost)[mysql]> select * from db\G
 
*************************** 1. row ***************************
 
                 Host: %
 
                   Db: sample
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: Y
 
          Update_priv: Y
 
          Delete_priv: Y
 
          Create_priv: Y
 
            Drop_priv: Y
 
           Grant_priv: N
 
      References_priv: Y
 
           Index_priv: Y
 
           Alter_priv: Y
 
Create_tmp_table_priv: Y
 
     Lock_tables_priv: Y
 
     Create_view_priv: Y
 
       Show_view_priv: Y
 
  Create_routine_priv: Y
 
   Alter_routine_priv: Y
 
         Execute_priv: Y
 
           Event_priv: Y
 
         Trigger_priv: Y
 
1 row in set (0.00 sec)
 
授予all privileges权限。注意点是grant option并不包含在all privileges里面。可以用with子句
 
(root@localhost)[mysql]> grant all privileges on sample.* to test1 with grant option;
 
Query OK, 0 rows affected (0.00 sec)
 
(root@localhost)[mysql]> select * from db\G
 
*************************** 1. row ***************************
 
                 Host: %
 
                   Db: sample
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: Y
 
          Update_priv: Y
 
          Delete_priv: Y
 
          Create_priv: Y
 
            Drop_priv: Y
 
           Grant_priv: Y
 
      References_priv: Y
 
           Index_priv: Y
 
           Alter_priv: Y
 
Create_tmp_table_priv: Y
 
     Lock_tables_priv: Y
 
     Create_view_priv: Y
 
       Show_view_priv: Y
 
  Create_routine_priv: Y
 
   Alter_routine_priv: Y
 
         Execute_priv: Y
 
           Event_priv: Y
 
         Trigger_priv: Y
 
1 row in set (0.00 sec)
 
回收all privileges权限, 错误写法，revoke并不能带with grant option来回收grant option
 
(root@localhost)[mysql]> revoke all privileges on sample.* from test1 with grant option;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant option' at line 1
 
(root@localhost)[mysql]>
 
这样写还是不对：
 
revoke all privileges, grant option  on sample.* from test1;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on sample.* from test1' at line 1
 
分开写就可以了：
 
(root@localhost)[mysql]> revoke all privileges on sample.* from test1;
 
Query OK, 0 rows affected (0.00 sec)
 
(root@localhost)[mysql]> revoke grant option on sample.* from test1;
 
Query OK, 0 rows affected (0.00 sec)
 
(root@localhost)[mysql]>
 
grant option在授予的时候是用with子句，回收的时候需要单独回收。
 
2#一般用户可访问的数据库：
 
在test1用户下，查看可以访问的数据库：
 
(test1@localhost)[(none)]> show databases
 
    -> ;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| sample             |
 
+--------------------+
 
3 rows in set (0.00 sec)
 
(test1@localhost)[(none)]>
 
(test1@localhost)[(none)]>
 
(test1@localhost)[(none)]>
 
(test1@localhost)[(none)]> use mysql;
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
Database changed
 
(test1@localhost)[mysql]> show tables;
 
+-----------------+
 
| Tables_in_mysql |
 
+-----------------+
 
| user            |                #由于只有user表上被授予了select权限，所以show tables只能看到一个表
 
+-----------------+
 
1 row in set (0.00 sec)
 
(test1@localhost)[mysql]> show grants;
 
+---------------------------------------------------------------------+
 
| Grants for test1@%                                                  |
 
+---------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD <secret>   |
 
| GRANT ALL PRIVILEGES ON `sample`.* TO 'test1'@'%' WITH GRANT OPTION |
 
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%'               |
 
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%'                       |
 
+---------------------------------------------------------------------+
 
4 rows in set (0.00 sec)-----------------------------------------------------------------+
 
| Grants for test1@%                                                |
 
+-------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD <secret> |
 
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%'             |
 
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%'                     |
 
+-------------------------------------------------------------------+
 
3 rows in set (0.00 sec)
 
(test1@localhost)[mysql]>
 
sample数据库是被授予的all privileges,所以全库的表对象都是可用呗test1用户访问。至于mysql，只有user表上有一个select权限，所以也被归类在可用访问的数据库之列，但是实际上用show tables只能看到一个表。
 
3# 数据库层权限的从无到有，给一个用户授予权限后，db表的变化：
 
a# 建立一个新的数据库sample2
 
(root@localhost)[(none)]> create database sample2;
 
Query OK, 1 row affected (0.00 sec)
 
这个时候查看用户test1所拥有的数据库权限，是只有sample数据库。MYSQL数据库并不显示。
 
(root@localhost)[mysql]> select * from db\G
 
*************************** 1. row ***************************
 
                 Host: %
 
                   Db: sample
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: Y
 
          Update_priv: Y
 
          Delete_priv: Y
 
          Create_priv: Y
 
            Drop_priv: Y
 
           Grant_priv: Y
 
      References_priv: Y
 
           Index_priv: Y
 
           Alter_priv: Y
 
Create_tmp_table_priv: Y
 
     Lock_tables_priv: Y
 
     Create_view_priv: Y
 
       Show_view_priv: Y
 
  Create_routine_priv: Y
 
   Alter_routine_priv: Y
 
         Execute_priv: Y
 
           Event_priv: Y
 
         Trigger_priv: Y
 
1 row in set (0.00 sec)
 
(root@localhost)[mysql]>
 
#这个时候test1用户去尝试访问sample2数据库也是失败的：
 
(test1@localhost)[sample]> use sample2
 
ERROR 1044 (42000): Access denied for user 'test1'@'%' to database 'sample2'
 
(test1@localhost)[sample]>
 
运行授权语句，对test1受援sample2的select权限，这个时候mysql.db数据库中出现了2行数据，多了一行关于sample2的记录：
 
(root@localhost)[mysql]> select * from db\G
 
*************************** 1. row ***************************
 
                 Host: %
 
                   Db: sample2
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: N
 
          Update_priv: N
 
          Delete_priv: N
 
          Create_priv: N
 
            Drop_priv: N
 
           Grant_priv: N
 
      References_priv: N
 
           Index_priv: N
 
           Alter_priv: N
 
Create_tmp_table_priv: N
 
     Lock_tables_priv: N
 
     Create_view_priv: N
 
       Show_view_priv: N
 
  Create_routine_priv: N
 
   Alter_routine_priv: N
 
         Execute_priv: N
 
           Event_priv: N
 
         Trigger_priv: N
 
*************************** 2. row ***************************
 
                 Host: %
 
                   Db: sample
 
                 User: test1
 
          Select_priv: Y
 
          Insert_priv: Y
 
          Update_priv: Y
 
          Delete_priv: Y
 
          Create_priv: Y
 
            Drop_priv: Y
 
           Grant_priv: Y
 
      References_priv: Y
 
           Index_priv: Y
 
           Alter_priv: Y
 
Create_tmp_table_priv: Y
 
     Lock_tables_priv: Y
 
     Create_view_priv: Y
 
       Show_view_priv: Y
 
  Create_routine_priv: Y
 
   Alter_routine_priv: Y
 
         Execute_priv: Y
 
           Event_priv: Y
 
         Trigger_priv: Y
 
2 rows in set (0.00 sec)
 
从记录可用看到test1用户对sample2数据库拥有select权限。也就是访问权限，表示里面的对象都具有。但是里面是没有表的。
 
(test1@localhost)[(none)]> use sample2;
 
Database changed
 
(test1@localhost)[sample2]> show tables;
 
Empty set (0.00 sec)
 
#并且test1用户可用select出smp表的数据。
 
(test1@localhost)[sample2]> select * from smp;
 
+------+------+
 
| id   | name |
 
+------+------+
 
|    1 | aaa  |
 
+------+------+
 
1 row in set (0.00 sec)
 
# 然而test1用户并不显示的拥有任在sample2上的表权限。所谓的权限的继承。
 
(test1@localhost)[information_schema]> select * from TABLE_PRIVILEGES where TABLE_SCHEMA='sample2';
 
Empty set (0.00 sec)
 
#奇怪，并没有给表smp授权select，受援的是select  on sample2.*。尝试单独对表收回select 权限：
 
(root@localhost)[sample2]> revoke select on sample2.smp from test1;
 
ERROR 1147 (42000): There is no such grant defined for user 'test1' on host '%' on table 'smp'
 
#以失败告终。显示的revoke并不能收回隐式继承来的权限。
 
(test1@localhost)[sample2]> show tables;
 
+-------------------+
 
| Tables_in_sample2 |
 
+-------------------+
 
| smp               |
 
+-------------------+
 
1 row in set (0.00 sec)
 
 

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2016-2023 https://www.0635zz.com/ All Rights Reserved. 聊城站长网