Granting permission to users on different schema

Question

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

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

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.

Answer 1

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.

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

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.