Grant doesn't works in PDB

User_UL6AR

Dear Oracle Community,

Sorry to bother you again. Maybe my question is stupid. I have installed Oracle 12c and created a PDB like this:

-- creation of PDB PEEI 

create pluggable database PEEI admin user PEEI_SYS identified by PEEI 

roles = (DBA);

-- open PDB PEEI 

alter pluggable database PEEI open read write;

-- create automatic trigger to start all pluggable database

create or replace trigger Sys.After_Startup after startup on database

begin

  execute immediate 'alter pluggable database all open';

end After_Startup;

After that I have created a user PEEI in the PDB PEEI. I would like that the user PEEI can insert, delete, update but not do create table and delete table to ensure security in production. Here is my code:

-- create tablespace for PEEI 

sqlplus PEEI_SYS/[email protected]

CREATE TABLESPACE PEEI_DATA DATAFILE SIZE 5G;

CREATE TABLESPACE PEEI_IDX DATAFILE SIZE 5G;

-- create roles 

CREATE ROLE ROLE_PEEI_READ NOT IDENTIFIED;

CREATE ROLE ROLE_PEEI_WRITE NOT IDENTIFIED;

-- create peei user 

CREATE USER "PEEI" IDENTIFIED BY "PEEI" DEFAULT TABLESPACE PEEI_DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

--grant roles for PEEI 

GRANT ROLE_PEEI_READ TO PEEI;

GRANT RESOURCE TO PEEI;

GRANT ROLE_PEEI_WRITE TO PEEI;

GRANT CONNECT TO PEEI;

ALTER USER PEEI DEFAULT ROLE ALL;

--System Privileges for PEEI 

GRANT UNLIMITED TABLESPACE TO PEEI;

GRANT GRANT ANY PRIVILEGE TO PEEI;

GRANT ALTER ANY ROLE TO PEEI;

GRANT GRANT ANY ROLE TO PEEI;

However after my code. When I create a table in PEEI_SYS, the user PEEI cannot see the table. Could you please help me ?

Thank you very much in advance.

User_UL6AR

I have solved this issue. It's a stupid question. I just need to create the table in PEEI_SYS schema and do the grant with explicit name like this:

GRANT SELECT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_READ;

GRANT DELETE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;

GRANT INSERT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;

GRANT UPDATE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;

After that I can create a SAS library and setting schema = PEEI_SYS. In this way the user PEEI cannot delete tables from PEEI_SYS schema.

Have a nice day.