Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Grant from schema A of Schema B tables to schema C

mradul goyalApr 27 2022 — edited Apr 28 2022

I have an application schema U32_C5 and another schema MIG_SRC which has some tables and then we have a ROLE RO_ROLE on which we have some grants of tables from Schema U32_C5. The RO_ROLE is assigned to another Schema MRGO_RO which is Read Only.
Here I need to grant select on tables from MIG_SRC to MRGO_RO catch here is that the process which and from where I can include these grants is run from application schema U32_C5 in production so when I tried to to something like below it says table or view does not exists (which seems to be obvious)
execute Grant from U32_C5
grant select on MIG_SRC.MOBILE_CELLPHONE_PAIRINGS to MRGO_RO;
Another way which I think of is creating DB link on MRGO_RO for MIG_SRC Schema but it enables read/write operation as well on MIG_SRC tables which is not allowed on production.
DB Links is present on U32_C5 Schema for MIG_SRC Schema.
So looking for a way to accomplish above task without creating DB Link any suggestion is welcome.
Sample Script of requirement what I want to achieve Please Remember I cannot and do not want Login to MIG_SRC and only way I am looking for is to do it by using U32_C5 and without DBA HELP

A RO ROLE Created by DBA's 

Create role RO_ROLE;

/* Create application schema, table inside it and grant select on it to RO_ROLE*/

CREATE USER U32_C5 IDENTIFIED BY U32_C5 DEFAULT TABLESPACE; 

GRANT ALTER SESSION TO U32_C5; 
GRANT CREATE SESSION TO U32_C5; 

GRANT CREATE database link TO U32_C5; 

GRANT CREATE table TO U32_C5; 


create table U32_C5_test_tab (id number);

grant select on U32_c5.U32_C5_Test_tab to RO_ROLE;


/* Create Read Only schema, grant RO_ROLE to it */

CREATE USER mrgo_ro IDENTIFIED BY mrgo_ro DEFAULT TABLESPACE;  

GRANT ALTER SESSION TO mrgo_ro; 
GRANT CREATE SESSION TO mrgo_ro; 

grant ro_role to mrgo_ro;

/* Create SRC schema, table inside it */

CREATE USER MIG_SRC IDENTIFIED BY MIG_SRC DEFAULT TABLESPACE; 

GRANT ALTER SESSION TO MIG_SRC; 
GRANT CREATE SESSION TO MIG_SRC; 

GRANT CREATE database link TO MIG_SRC; 

GRANT CREATE table TO MIG_SRC; 

create table mig_src_test_tab (id number);


/* login to Apllication Schema U32_C5 */

sqlplus U32_C5/U32_C5@SID

grant select on mig_src.mig_src_test_tab to mrgo_ro;  -- for me it gives error here at this step table or does not exist 

/* login to Read Only Schema mrgo_ro */

sqlplus mrgo_ro/mrgo_ro@SID

select * from mig_src.mig_src_test_tab;
or 
select * from mig_src_test_tab;
Comments
Post Details
Added on Apr 27 2022
11 comments
633 views