0

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');
  • Please note that, strictly speaking, "view rows inserted by users from the same company as mine" is not the same as "view rows that have the same company ID as mine". A user might want to insert a row with a company ID other than their company ID. Would you want the user to be able to view that row? Would you want them to be able to insert that row in the first place? – Andriy M Apr 12 '16 at 8:28
  • A user with company ID 100 can insert rows in relation to that company ID only and not for Company ID 200. So lets say user B inserted a row for company ID 100. Then when user X which belongs to company ID 100 when logs into oracle 10g he/she can view the row inserted by user B for company ID 100 and other rows with company ID 100 – DoraMail Apr 12 '16 at 19:30
1

According to the question all you need to do is attach a predicate on 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? So 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 small demo for your requirement. Here in my demo I have create doramail as an ADMIN user who owns the tables user_info, customer and 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 is 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.
  • I tried the method of using the function above you have mentioned but it says that "policy predicate has errors" . It is not working – DoraMail Apr 12 '16 at 20:21
  • 1
    @DoraMail show me the predicate being attached to your query using explain plan for select... method I have shown on the answer. Just add 1-access... 2-filter... on your question. – JSapkota Apr 13 '16 at 3:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.