CODEWITHSUNDEEP
oracle-databaseprivileges
MaTaDoRcpe
MaTaDoRcpe

Reputation: 99

Oracle grant Privilege User A to User B


    I create user A and B with system admin
    -- CREATE USER1 SQL
    CREATE USER USERA IDENTIFIED BY 123456 ;
    GRANT CREATE SESSION TO USERA;
    CREATE USER USERB IDENTIFIED BY 123456 ;
    GRANT CREATE SESSION TO USERB;

    and set Privileges to USERA
    -- SYSTEM PRIVILEGES USERA
    GRANT CREATE TABLE TO USERA;
    GRANT DELETE ANY TABLE TO USERA;
    GRANT GRANT ANY PRIVILEGE TO USERA;

    and create table by USERA

    CREATE TABLE tableA 
    (ID VARCHAR2(5)
    , DATA VARCHAR2(20) );

    and create Privileges to USERB by USERA set select tableA
    GRANT SELECT ON tableA to USERB;

    but USERB can't select tableA 
    ERROR:ORA-00942: table or view does not exist

    How do I solve the problem?

Upvotes: 1

Views: 224

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

If you don't specify the owner for a table, Oracle will assume the owner is the current user.

select * from usera.tablea;

To avoid this issue, it's usually recommended to create a local synonym (owned by userb):

create synonym userb.tablea for usera.tablea;

Now, userb can query the table:

select * from tablea;

Upvotes: 0

Related Questions