Forum Stats

  • 3,838,503 Users
  • 2,262,377 Discussions
  • 7,900,674 Comments

Discussions

Grant doesn't works in PDB

User_UL6AR
User_UL6AR Member Posts: 14 Green Ribbon
edited Mar 2, 2021 8:51AM in Database Installation

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.

Answers

  • User_UL6AR
    User_UL6AR Member Posts: 14 Green Ribbon

    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.