MySQL查询所有视图并给用户授予指定视图权限
视图其实就是一张虚拟的表，所有也可以认为是一张表，MySQL查询所有视图:
show table status where comment='view'\G;查询视图结构：
DESC master_data_user;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| username | varchar(255) | YES | | NULL | |
| org_id | varchar(255) | YES | | NULL | |
| status | varchar(255) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| org_name | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| telephone | varchar(255) | YES | | NULL | |
| post | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
或者
SHOW TABLE STATUS LIKE 'master_data_user'\G;
*************************** 1. row ***************************
Name: master_data_user
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)创建data_view用户并授予单个视图权限：
grant select,show view on escdb.master_data_org to data_view@'192.168.%' IDENTIFIED BY 'xxxxxxx';
grant select,show view on escdb.master_data_user to data_view@'192.168.%' ;
flush privileges;data_view登录后即只能查看master_data_org和master_data_user视图；
查看当前登录用户权限：
show grants;
+----------------------------------------------------------------------------------+
| Grants for data_view@192.168.% |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'data_view'@'192.168.%' |
| GRANT SELECT, SHOW VIEW ON `escdb`.`master_data_org` TO 'data_view'@'192.168.%' |
| GRANT SELECT, SHOW VIEW ON `escdb`.`master_data_user` TO 'data_view'@'192.168.%' |
+----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Tag标签:「权限 mysql 视图 grant」更新时间:「2021-11-03 14:57:36」阅读次数:「636」