Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

oracleoracle-10gSecurity

So lets say 100, 200, 400, 600 are company_ids and there are 6 administrators who each have their company_ids. The owner of the table User_Info is Admin who is also the owner of other tables and can view, edit, delete, update any tables, procedures, or anything. Has all rights.
Now Admin has created users MKarki and other such in the User_Info table with respective company_ids.
There are other tables inserted using Admin's account in Oracle 10g like customer, employee, shipment and so on. Each of those table have "Company_ID" as a foreign key.
A user when logs into their own session can insert, update, delete the records from the table customer, employee or any other based on the company_id it belongs to. MKarki cannot select, insert, update, or delete records which has company_id 200 (this one is already taken care of).

Example: So the User_Info table has these columns:

Company_ID F_Name    L_Name    Username    Owner   Level_Of_Security
100        Mala      Karki      MKarki     Admin        5
100        Piya      Karki      PKarki      Admin        5
200        Giya      Khadka     GKhadka     Admin        5

Now customer table looks like this:

CUS_ID  Company_ID  F_Na  L_Na    Owner   Sec_Level
23       100        Hor    Loki    MKarki       4
22       100        Bholu  Kam     PKarki       4
34       200        Golu   Pop     GKhadka      4

Based on the tables above: When MKarki logs into her own Oracle account and executes the Select statement: Select * from Customer. She should be able to view all records that have company_Id 100. (This is what I need to know as how this can be implemented???)

So now if PKarki logs into oracle 10g then she should be able to see both of the customer values for company_ID 100 even if one belongs to MKarki since PKarki is also from same company ID.
Now we need to implement a security policy function for the above business policy.

The environment is Oracle 10g and it is for Virtual Private Database

I am unable to get any rows even after setting the context with package, package body, and procedure. (No rows selected or "Error: Policy predicate has errors" if I tweak the Sys_Context a little bit)

I have also created the context with package like this:

Create or replace Context CompAdmin USING Pac_Comp_Adm;

Create or Replace Package Pac_Comp_Adm IS Procedure Get_Company_ID; END; /

Create or Replace PACKAGE BODY Pac_Comp_Adm 
IS PROCEDURE Get_Company_ID IS V_Company_ID Number; 
BEGIN 
Select  Company_ID INTO V_Company_ID FROM User_Info 
Where Company_ID = SYS_CONTEXT('USERENV','SESSION_USER');

DBMS_SESSION.SET_CONTEXT('COMPADMIN','Company_ID', V_Company_ID); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END; /

Create or Replace FUNCTION Helo_Comp_ID ( P_SCHEMA_NAME IN varchar2, P_OBJECT_NAME IN varchar2) 
Return varchar2 IS V_Hello Varchar2(300); 
BEGIN If User = 'ADMIN' 
then V_Hello := ''; 
Else 
V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']'; 
END IF; 
RETURN V_Hello; 
END; /

After this I am setting the policies using the function above as follows:

exec DBMS_RLS.ADD_Policy     ('ADMIN','CUSTOMER','Row_Upper_Sec','ADMIN','Helo_Comp_ID','SELECT,INSERT,UPDATE,DELETE');

Best Answer

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right? If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN                                                                
---------- --------------------                                                 
       100 MKarki                                                               
       200 VKarki                                                               
       300 CKarki                                                               

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim  
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Ricky                                                                
       100 Joe                                                                  
       200 Jimmy                                                                
       300 Cashy                                                                
       300 Tim   
SQL> conn / as sysdba
Connected.    
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME                                                                 
---------- --------------------                                                 
       100 Joe                                                                  
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Related Solutions

Designing Simple Schema for Disaggregation of Demand Forecast

Okay, so here's the data model I came up with.

PRODUCT -- to store product information and maintain parent-child hierarchy

id  NUMBER  "Primary Key Not Null"                  
level_code  VARCHAR2    Not Null                    
name    VARCHAR2    Not Null                    
description VARCHAR2                        
parent_id   NUMBER  Foreign Key references PRODUCT(id)

ORDERS -- to store orders for products

id  NUMBER  "Primary Key Not Null"                  
prod_id     NUMBER  "Foreign Key references PRODUCT(id) Not Null"                   
order_type  VARCHAR2    "Not Null Default 'Default'"
order_qty   NUMBER  Not Null
order_date  NUMBER  Foreign Key references DATE_INFO(date_key)
delivery_date   NUMBER  "Foreign Key references DATE_INFO(date_key)
Check delivery_date >= order_date"

FORECAST -- to store forecast value for products (store value for higher levels, store value for lower levels after disaggregation from a parent)

id  NUMBER  "Primary Key Not Null"
product_id  NUMBER  "Foreign Key references PRODUCT(id) Not Null"
forecast_value  NUMBER  Not Null
week    NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"

DISAGGREGATION_RULES -- to store which method was used for disaggregating a value from a higher level to lower level and how much percentage got distributed to lower level

id  NUMBER  "Primary Key Not Null"
parent_product_id   NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
child_product_id    NUMBER  "Foreign Key id references PRODUCT(id) Not Null"
method  VARCHAR2    Not Null                    
from_week   NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null"
to_week NUMBER  "Foreign Key references DATE_INFO(date_key) Not Null Check end_week >= start_week"
percent_distribution    NUMBER  Not Null

DATE_INFO -- date dimension, has information about start date (has to be Saturday) and end date corresponding to the week in which a particular date falls

date_key    NUMBER  "Primary Key
Not Null"                   
full_date   DATE    Not Null                    
week_begin_date DATE    Not Null                    
week_end_date   DATE    Not Null

As for the bucket number .. I am calculating week start date (date on Saturday, in my case) with the following function

CREATE OR REPLACE FUNCTION get_week_start_date(v_bucket_num IN NUMBER)
  RETURN DATE
IS
  week_start_date DATE;
BEGIN
  SELECT (TRUNC(SYSDATE+2, 'IW')-2) + ((v_bucket_num-1) * 7)
  INTO week_start_date FROM dual;
  RETURN week_start_date;
END;

How to reference a table from another schema inside a package

From section "6 Coding PL/SQL Subprograms and Packages" in the "Oracle Database Advanced Application Developer's Guide, 11g Release 2 (11.2), E41502-05"

To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
- The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
- The owner cannot obtain required privileges through roles.

You must have granted select permission on this table to the schema that owns the package. It is important that this permissions are granted directly to the package owner and not indirect using a role. Using a role is sufficient to select the table directly by Toad but it is not sufficient if you want to select the table in a package.

Best Answer

Related Solutions

Designing Simple Schema for Disaggregation of Demand Forecast

How to reference a table from another schema inside a package

Related Question