CODEWITHSUNDEEP
oracle-databaseoracle12cpdbvpd
Dev123
Dev123

Reputation: 21

How to access data of a table (on which VPD is applied) in the PDB of another schema using PDB User?

I want access data of VPD table outside of its Schema Database. In other words I want to access data of a VPD tables into PDB of another Schema.

Let say database(CDB) name is DEVDB.

Now, Lets consider I have created a schema in DEVDB called : VPDSchema

I created two tables in VPDSchema:

1) DBNAME(DB_Name)
2) DBUSER(DB_Name, Username)

Now I created a Table in VPDSchema called: VPDTable ( data is to filtered on basis of i.e.DBName Column)

So the details are:

Schema Name: VPDSchema
Tables Name: VPDTable
User: VPDSchema_User
Password:******
DataBase:DEVDB

Now I applied below Function and Policy on VPDTable inside VPDSchema

Function: VPDFunction
  //Matches from DBUSER & 
    Return Respective DB  

 //POLICY
BEGIN
     SYS.DBMS_RLS.ADD_POLICY(
      object_schema   => 'VPDSchema',
      object_name     => 'VPDTable',
      policy_name     => 'policy_vpd1',
      function_schema => 'vpdSchema',
     policy_function => 'vpdFuction',
    statement_types => 'select'
   );
 END;
 /

Now let's consider I have created another schema Called: UserSchema

So,

Schema Name : UserSchema
       User : UsersSchema_User
   password : *******
   DataBase : DEVDB

Now, I created a PDB for a specific user on for this schema (UserSchema)

PDB Created : PDBDB
PDB created for : UserSchema
username : UserSchema_User
password : ****
DataBase : PDBDB

Now the problem is: When I create view of VPDtable inside Userschema for UserSchema_user@DEVDB, I can view data of it as both share same CDBC (DEVDB)

BUT when I create view of VPDtable for inside PDB like userSchema_user@PDBDB, it return 0 records

I tried adding policy in UserSchema but it didn't work. I am stuck here, please help

Upvotes: 1

Views: 322

Answers (0)

Related Questions