CODEWITHSUNDEEP
oracle-databaseoracle11g
Joseph
Joseph

Reputation: 97

Oracle 11 -hiding some rows for one user only

We have a requirement from our managers to hide some rows in one of the most used table in our database (oracle 11g) from a specific user (user3), we try to use the row-level security (RLS) package with one simple function as shown below, and its work fine in the scenarios that we tested. my questions are:

  1. Will this solution work for our purpose?

  2. If this table was used in a complex view, will the database hide the specific rows in the view results?

  3. Is this a good practice or there is a more efficient way?

  4. Will this solution affect the performance to a critical level?

the function that will be called by the RLS policy:

CREATE OR REPLACE FUNCTION test_rls_policy( 
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (400);
  currentuser varchar2(100);
 BEGIN

 --retrive the current user
 select user into currentuser from dual;

 -- check if the user is user3, then add the where condition, else null
 if currentuser = 'USER3' then 
  return_val := 'type<>4'; -- this will be added to the where clause dynamically
  else 
  return_val := '1=1';
  end if;

  RETURN return_val;
 END test_rls_policy;
/

Upvotes: 0

Views: 1351

Answers (3)

APC
APC

Reputation: 146209

To answer your questions:

  1. Yes
  2. Yes
  3. Yes (to best practice)
  4. Yes

Regarding efficiency, using Oracle built-in capabilities will always be more performative than rolling your own. There are third party products which do similar things but you would really only look at those if you were on Standard Edition (because RLS is an Enterprise Edition feature) or maybe if you had a heterogeneous environment and wanted a single security solution for all your databases.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

VPD, resp. RLS is certainly a valid way to to it.

Maybe a simpler and cheaper (but less flexible) way is to work with views. 

create view v_your_table as 
select *
from your_table
where type<>4;

CREATE OR REPLACE SYNONYM USER3.your_table FOR v_your_table;

Grant all required privileges on view v_your_table to USER3 and revoke all for table your_table. USER3 can also insert/update/delete table trough the view (if applicable).

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

Have a look at Oracle Virtual private database, which

enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Upvotes: 0

Related Questions