Stack Overflow
Stack Overflow

Questions tagged [vpd]

Use this tag for questions dealing with Oracle Fine Grained Access Control and the `DBMS_RLS` package,

Virtual Private Database is an Oracle security feature which restricts the data which users can see and/or change. Security policies are defined for database tables, which create filters that are applied automatically to DML statements.

18 questions
2
votes
0 answers

any real world example in RSA with ORDS in oracle

I'm using ORDS. I'm going to use RAS(real application security) or VPD to control authorization. I read oracle RAS documentation and I didn't catch how to use it with ORDS(without apex). and this is true for using VPD, I created user in ords.war but…
fantom
  • 67
  • 8
2
votes
0 answers

Exchange subpartition failed because of VPD even after grant exempt policy

I have a process that execute EXCHANGE SUBPARTITION statement. Acoording to the fact that all the tables in the schema have a VPD POLICY protection, we have a stored procedure that grant EXEMPT ACCESS POLICY to the user just before the action and…
user2671057
  • 1,411
  • 2
  • 25
  • 43
2
votes
1 answer

ORA-28113: policy predicate has error

I need some help with Oracle's VPD feature. I have never used it before but did some research online about it, however I'm running into a problem. Here are the steps that I have taken: QuanTriDL: create table NhanVien2 table…
Quang Minh
  • 31
  • 1
  • 1
  • 6
1
vote
0 answers

Oracle VPD to introduce non-invasive dbms_utility.format_call_stack

We have a situation whereby the code generates an intermetent 'Insert' on a table. The idea is to enable VPD (via DBMS_RLS.ADD_POLICY) methods, on 'insert' of the table, and policy function, which is going to generate Call stack to trace which piece…
Piyush
  • 21
  • 1
  • 2
1
vote
0 answers

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 :…
Dev123
  • 21
  • 4
1
vote
1 answer

Speeding up queries subject to VPD policy

I'm currently applying this predicate on a VPD policy: CREATE OR REPLACE FUNCTION limit_table( schema_p IN VARCHAR2, table_p IN VARCHAR2) RETURN VARCHAR2 AS pred VARCHAR2 (400); BEGIN pred := ' ((select count(granted_role) from…
Unziello
  • 103
  • 8
1
vote
1 answer

Create security policy function to allow a user to see rows owned by a specific user

How to revise the function so that the user (HR1) can view rows where the CTL_UPD_USER is either VPD_CLERK1 or VPD_CLERK2? I'm very new to Oracle and pl/sql. RETURN VARCHAR2 AS con VARCHAR2 (200); BEGIN IF USER ='HR1' THEN con :=…
Sydney
  • 11
  • 1
1
vote
0 answers

Performance Impact of Using RLS

Generic_Schema has one common table, GENERIC_TABLE. The primary key of GENERIC_TABLE is COL1 and COMPANY_ID. Company_id Col1 Col2 Col3 -------------- ---- ---- ---- ALL_COMPANIES A B C Comp1 A B1 C1 Comp3 A B3 …
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72
0
votes
0 answers

Get wrong result when using SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') in function of Oracle VPD

I am using Oracle 19 and Navicat. When I execute this, it returns TRUE. SELECT SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') FROM DUAL; However, when I implement a policy to a table, using a function to control permission, in which…
user20137254
  • 1
  • 1
0
votes
0 answers

Oracle CASE expression in query plan

The background to this question is that I am currently investigating the query plans generated by having Oracle VPD column masking policies active. I would assume that the underlying rewrite is expressed as a CASE expression, e.g. SELECT CASE WHEN…
Stanley Clark
  • 63
  • 8
0
votes
1 answer

My question is how to create a VPD in Oracle with SQL that will also mask data

I am trying to create a VPD in Oracle using SQL statements. The purpose of this problem is so an employee could ONLY view records for employees in the same department while masking their coworker's salaries as NULL. The code for the table being used…
Venzie
  • 1
0
votes
0 answers

How to apply Oracle VPD Policy to restrict update some specific columns?

I have some problems when trying to solve my homework about VPD Policy of Oracle. The question is "MYADMIN account has the right to select and update students' Enroll Information. But he is not allowed to see the Score of the student's Enroll…
Orange Pumpkin
  • 11
  • 2
0
votes
0 answers

DBMS_RLS.add_policy: object_name not working

Hi guys, I'm learning the VPD feature of Oracle and I've got stuck at these below codes. BEGIN DBMS_RLS.add_policy( object_schema => 'user_bacsi_01', object_name => 'HOSOBENHNHAN', policy_name =>…
tcvduc
  • 129
  • 8
0
votes
1 answer

How to add a policy in Oracle that a user can select all data from table and only can insert,update, delete his data with condition

I have a object name like this. grant execute on dbms_rls to public; create table empholiday ( EmpNo number(5), Name nvarchar2(60), Holiday date ); INSERT INTO empholiday VALUES (1,'HANN','02-JAN-2019'); INSERT INTO empholiday VALUES…
TrongDat
  • 1
  • 2
0
votes
1 answer

oracle ORA-00904: "E3": invalid identifier in function for vpd

I am learning oracle vpd so I created the following tables: create table employees ( empid varchar(30) primary key,empname varchar(30), dept varchar(30)); insert into employees values ('E1','mark','ACCOUNTING'); insert into employees values…
john93283
  • 1
1
2