向不同模式的用户授予权限 [英] Granting permission to users on different schema

问题描述

我在模式A中有表.我在模式B中使用模式A中的表创建了视图.

I have tables in Schema A. I created views in Schema B using the tables in schema A.

我想授予用户从架构B的视图中选择数据的权限.

I want to grant permissions to a user to select the data from view in Schema B.

为此，我知道我们必须在架构A中的表上向用户B启用授予选项. 但是我想在一个脚本中执行此操作(此脚本必须在模式B中).有没有办法使用模式A的用户名/密码来做到这一点.

For this to work i know we have to enable the grant option on tables in Schema A to user B. But I want to do it in a single script (This script has to be in schema B). Is there a way to do this using the user name/password of schema A.

推荐答案

希望有一个脚本来部署更改并不稀奇.事实是，这样的脚本需要由超级用户运行，因为它需要在ANY级别具有系统特权.这通常意味着一个DBA帐户，最好是一个应用程序帐户，否则就是SYSTEM或SYS.

It's not unusual to want to have a single script to deploy a change. The thing is, such a script needs to be run by a power user, because it needs to have system privileges at the ANY level. This usually means a DBA account, preferably an application account but otherwise SYSTEM or SYS.

所以您想要的脚本如下所示:

So the script you want would look like this:

grant select on user_a.t23 to user_b
/
grant select on user_a.t42 to user_b
/
create view user_b.v_69 as
select t23.col1, t42.col2
from   user_a.t42
       join user_a.t23
           on (t42.id = t23.id)
/
grant select on user_b.v_69 to user_c
/

一种常见的情况是，我们有一套单独的脚本，这些脚本被编写为由不同的用户运行，但是现在我们需要将它们捆绑到一个部署中.原始脚本不包含架构名称，并且有很多很好的原因导致我们不想在脚本中对其进行硬编码.

A common scenario is that we have a suite of individual scripts which have been written to be run by different users but which we now need to bundle up into a single deployment. The original scripts don't contain the schema names, and there are many good reasons why we wouldn't want to hardcode them in the scripts.

构建主脚本的一种方法是使用更改CURRENT_SCHEMA语法:

One way to build that master script is to use change the CURRENT_SCHEMA syntax:

alter session set current_schema=USER_A
/
@run_grants_to_userb.sql

alter session set current_schema=USER_B
/
@create_view69.sql
@run_grants_to_userc.sql

我们仍然需要DBA用户来运行主脚本.切换当前模式的一个优点是，它允许我们部署诸如数据库链接之类的对象，这些对象通过语法的怪异不能在其声明中包含模式名称.一个陷阱是用户没有更改，因此使用USER伪列的脚本可能会产生不需要的结果.

We still need a DBA user to run the master script. One advantage of switching the current schema is that it allows us to deploy objects like database links, which through a quirk of syntax cannot have the schema name in their declaration. One gotcha is that the user doesn't change, so a script which employs the USER pseudo-column may produce unwanted results.

这篇关于向不同模式的用户授予权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！