Forum Stats

  • 3,826,733 Users
  • 2,260,699 Discussions
  • 7,897,065 Comments

Discussions

Grant from schema A of Schema B tables to schema C

User_NY02I
User_NY02I Member Posts: 356 Blue Ribbon
edited Apr 28, 2022 7:45AM in SQL & PL/SQL

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/[email protected]

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/[email protected]

select * from mig_src.mig_src_test_tab;
or 
select * from mig_src_test_tab;


«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    Hi, @User_NY02I

    Roles don't count in AUTHID DEFINER stored procedures. If u32_c5 needs to do something with mig_src's in a stored procedure, then either

    • the necessary privileges must be granted directly to u32_c5, and not merely to some role that u_32_c5 has, OR
    • the procedure must be declared AUTHID CURRENT_USER.

    If you can't do either of those things, then perhaps you could write the procedure in the mig_src schema, and GRANT EXECUTE to u32_c5. (This grant wouldn't need to be direct; it could be granted to a role that u32_c5 has.)

  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    Thanks Frank Kulash for your reply.

    Current implementation is that every developer has its own read only accounts on production DB to query tables in case of issue during migration events and to manage those grants DBA's had created a ROLE RO_ROLE which they assign to existing or any new read only users.

    so as of now we have select privileges for tables which are in U32_C5 schema (which is application schema as well) and only this schema has rights to do DML, DDL and to statements.

    Now in last migration event due to some issue we need to query some tables on MIG_SRC schema tables which was not able as we do not have privileges to query them however there is DB Link created on U32_C5 schema for MIG_SRC but we could not use those users on production so to avoid such scenario in future when we do not have access to certain important tables at crucial time decide to provide grants to Devs Read Only account using RO_ROLE since it is already being utilized for this purpose.

    However perhaps I did not get your reply but assuming that you are asking giving privileges to stored procedures which isn't the objective at the moment just provide some select grants on tables to Role and then to a Read Only schemas to enable them to query data.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    @Frank Kulash

    Roles don't count in AUTHID DEFINER stored procedures

    You have to

    GRANT some_role TO PACKAGE pkg_x
    

    If you want pkg_x to do something with privileges granted to the definer via role. (CBAC)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond
    edited Apr 27, 2022 7:42PM

    Hi, @User_NY02I

    Sorry, I don't understand what you're asking. It would help a lot if you could post a complete test script (CREATE TABLE statements for just a couple of tables and the GRANT statements that already exist) plus the desired results (the new GRANT statements that you need to issue).

    If the anonymous block that you posted in your second message works, then why not use it? If you need to make a stored procedure that does exactly what thy anonymous block does, then just make sure the procedure is declared as AUTHID CURRENT_USER.

    If the anonymous block you posted doesn't work, then you probably don't have the right privileges.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    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 order for U32_C5 to:

    Grant select on mig_src.table_1 to ro_role
    

    Mig_src (or DBA) needs to

    grant select on mig_src.table_1 to U32_C5
      WITH ADMIN OPTION
    

    But, if these permission are "for a limited time only", I'd create a separate role and grant that role to RO_ROLE

  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    Thanks for your reply @Frank Kulash @Mike Kutz

    I Will try to provide sample script for Schema, Role, Tables and Grants creation that is required to complete this Objective and meanwhile in the Night :) I got some idea's to try Public Synonyms, Views, Alter session set current_schema and WITH GRANT OPTION (on fancy side I not good with at)

    Please let me know of your views if any of above options which I seems not a good idea or otherwise.

  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    I have edited the question and removed the plsql block which I think is not required at this time and added a sample script to re-create the scenario which I am trying to do

  • DjZg
    DjZg Member Posts: 21 Red Ribbon

    Can you connect as SYS user?

  • User_NY02I
    User_NY02I Member Posts: 356 Blue Ribbon

    @DjZg No That won't be possible neither on MIG_SRC schema Only to my RO Account and the Application Schema (U32_C5) which runs the process

  • DjZg
    DjZg Member Posts: 21 Red Ribbon

    Then I think its not possible. You have to grant tables from user on which you don't have any privileges