我需要为用户提供MySQL视图和存储的 routine ，但不是基础表

发布于02月20日

I have a database with multiple schemas. One of these schemas contains some data that needs to remain hidden from the other schemas while allowing limited access to other data. The intent is to only make the data accessible via Views and Stored Routines.
I'm using MySQL 5.7.33 on an Ubuntu server and MySQL Workbench 8.0 on Windows.
My question is, how do I set up the schema to make views, etc. accessible, but not the tables.
I've given users the following permissions on the restricted schema.

And the CREATE statement for the view begins:

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `DBA`@`%` 
SQL SECURITY DEFINER
VIEW `CommonData`.`VW_SystemProperty` AS ...

When I run a select on the view I get the error:
Error Code: 1142. SELECT command denied to user '' for table 'VW_SystemProperty'
What am I missing?

推荐答案

虽然不能在过程或视图名称中使用通配符授予特权，但可以使用以下脚本生成这GRANT条语句. 这只是一种节省精力和避免错误的技术.

select
    CONCAT('GRANT SELECT ON `CommonData`.`', t.TABLE_NAME, '` to user;') as GrantStatement
from
    INFORMATION_SCHEMA.TABLES t
where
    t.TABLE_TYPE = 'VIEW'
    and t.TABLE_SCHEMA = 'CommonData'
    AND t.TABLE_NAME like 'VW[_]%'

一旦生成，就需要手动运行这些语句.

当然，您可以根据您的要求在WHERE条款中添加/删除限制.

存储过程也可以采用类似的方法:

select
    CONCAT('GRANT EXECUTE ON `CommonData`.`', r.SPECIFIC_NAME, '` to user;') as GrantStatement
from
    INFORMATION_SCHEMA.ROUTINES r
where
    r.ROUTINE_CATALOG = 'CommonData'
    and r.ROUTINE_NAME like 'usp%'

我需要为用户提供MySQL视图和存储的 routine ，但不是基础表

推荐答案

Mysql相关问答推荐

根据SQL中的平均范围对学生进行排名

在停靠容器中备份和恢复MySQL数据库时出现Unicode字符问题

SQL MaxSum查询为列 Select 了不正确的值

带有值分隔的MySQL分组不起作用

如果存在N个特殊行，如何 Select 它们，其余的必须填充常规行，总行数不能超过MySQL中的X行？

按优先级 for each 具有 status_id 的员工 Select 单行

即使在某些表中不匹配，如何从 MySQL 中的多个表中 Select 所有记录

SQL：如何为给定组中的所有记录 Select 一列与另一列不匹配的位置

使用sql查找源和最终目的地

SQL使用LIMIT获取结果和结果中的行数

Mysql 中相同列的相关 2 查询

在 postgresql 中实现 UML 类：创建类型与创建表

如何判断嵌套查询返回的元组中的每个条目是否相同？

使用case查询并更新最后一条记录

mysql错误：错误1018(HY000)：无法读取'.'的目录(错误号：13)

用于国际和多语言目的的数据库建模

如何通过一个查询批量更新 mysql 数据？

MySQL如何为数据库中的所有表生成DDL

如何在 MySQL 中为用户设置默认架构

"SELECT COUNT(*)" 很慢，即使有 where 子句