[selectsplat]

Greetings,

I've recently been asked to architect and implement a column based security model for an enterprise data warehouse to cnform with some hippa/sar-box standards.

I'm no DBA, but after a little bit of research, I decided I wanted to use the DBMS_RLS.DROP_POLICY/ADD_POLICY method in conjunction with EXEMPT status for users that will have security clearance. I've tested everything piecemeal, and everything works perfectly.

To implement this, I had hoped to have one stored procedure, where I would keep all of my policies. That way, when the security changed, I could re-apply all of the permissions at once, with the execution of one procedure.

However, I am discovering that I can't get a stored procedure to recognoze the DBMS_RLS library. I can execute each block individually, but when I put it in a stored procedure, I can't get it to copile.

I have a few questsion.

1.) Would this be a recommended implementation (putting the policies in a stored procedure)? If not, is there a better way?

2.) Why will this not compile?

PHP Code:

CREATE OR REPLACE PROCEDURE p_apply_security AS
BEGIN 
   
BEGIN
  DBMS_RLS.DROP_POLICY (object_schema     => 'my_schema',
                        object_name       => 'addresses',
                        policy_name       => 'sp_addresses');
  Exception when others then null;
END;

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'my_schema',
                       object_name           => 'addresses',
                       policy_name           => 'sp_addresses',
                       function_schema       => 'my_schema',
                       sec_relevant_cols     => 'house_no, street1, street2',
                       policy_function       => 'f_security_hide_all_rows',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;


BEGIN
  DBMS_RLS.DROP_POLICY (object_schema     => 'my_schema',
                        object_name       => 'ar_elect_billing_defaults',
                        policy_name       => 'sp_ar_elect_billing_defaults');
  Exception when others then null;
END;

BEGIN
  DBMS_RLS.ADD_POLICY (object_schema         => 'my_schema',
                       object_name           => 'ar_elect_billing_defaults',
                       policy_name           => 'sp_ar_elect_billing_defaults',
                       function_schema       => 'my_schema',
                       sec_relevant_cols     => 'contact, group_number',
                       policy_function       => 'f_security_hide_all_rows',
                       sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;

END;
/ 


[anacedent]

>Why will this not compile?
I give up.
My car won't go.
Tell me why my car won't go.

By using CUT & PASTE to show exactly what you are doing & how Oracle responds,
may allow us (TINU) to better understand the challenges you face.

Any error code & message would be most helpful.

[selectsplat]

I understand. My fault for not explaining well enough.

The error received is:
PLS-00201: identifier 'DBMS_RLS' must be declared

As I said, I can execute the blocks inside the procedure without error. But I cna't get the procedure to compile with the same blocks inside it.

[anacedent]

Privileges acquired via ROLE do NOT apply within PL/SQL procedures.

Also you may need to invoke as "SYS.DBMS_RLS"