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 |
- +-----------------+
- 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
来源: http://www.bubuko.com/infodetail-2748585.html