Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    6

    Question Unanswered: Keeping all Security Policies in a Stored Procedure

    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;


  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2006
    Posts
    6
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Privileges acquired via ROLE do NOT apply within PL/SQL procedures.

    Also you may need to invoke as "SYS.DBMS_RLS"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •