SA_SESSION.SET_ACCESS_PROFILE does not work for PROXY connections, any idea why?

From: bernard (bernard_at_bosvark.com) <bernard_at_bosvark.com>
Date: 1 Aug 2006 11:52:26 -0700
Message-ID: <1154458346.920631.227500@i3g2000cwc.googlegroups.com>

I have a situation where I need to set the OLS access profile (and LABEL) for a user after database login and only after I verified the user's security level from an LDAP server. The implementation work fine for normal connections, but users that connect via a PROXY account does not seem to inherit the rights of the proxy account even though privileged to change the ACCESS_PROFILE.

What am I missing and what should be changed? Help is very much appreciated.

I need to use Proxy users and their credentials are locked up in a LDAP server so I need to check if they can select data only after they have been verified. So I can not define their access rights beforehand.

See below my test script that demonstrates the problem:

---

--Usage: (a) Need to have OLS installed

• (b) Do not run on your production database, this script drops users

  ---

  connect sys/oracle as sysdba

drop user midtiersrv cascade;
drop user secman cascade;
drop user midtieruser cascade;

--My AppServer User Also proxy user

create user midtiersrv identified by midtiersrv quota unlimited on users;
--My Security Manager schema

create user secman identified by secman;
--My Middel Tier User

create user midtieruser identified externally;
--Setup MidTier User as user who can only connect through midtiersrv
proxy user
alter user midtieruser grant connect through midtiersrv;

grant connect to midtieruser;
grant connect to midtiersrv ;
grant connect to secman ;
grant dba to secman ; --To save time
grant unlimited tablespace to secman ;

alter user lbacsys identified by lbacsys account unlock;

connect lbacsys/lbacsys

EXEC sa_sysdba.drop_policy(policy_name => 'TEST1POL');
--Create my Policy

BEGIN
  sa_sysdba.create_policy(policy_name => 'TEST1POL',

                          column_name => 'TEST1POL_LABEL',
                          default_options => 'READ_CONTROL');

END;
/
grant test1pol_dba to secman;

grant execute on sa_components  to secman;
grant execute on sa_label_admin to secman;
grant execute on sa_user_admin  to secman;

grant execute on char_to_label to secman;

connect secman/secman

--Create levels

begin

  sa_components.create_level(policy_name => 'TEST1POL',
                             long_name   => 'SECRET',
                             short_name  => 'SEC',
                             level_num   => 10);

  sa_components.create_level(policy_name => 'TEST1POL',
                             long_name   => 'NOTSECRET',
                             short_name  => 'NOT',
                             level_num   => 5);

end;
/
--Create Labels

begin

  sa_label_admin.create_label(policy_name => 'TEST1POL',
                              label_tag   => 1,
                              label_value => 'SEC');
  sa_label_admin.create_label(policy_name => 'TEST1POL',
                              label_tag   => 2,
                              label_value => 'NOT');

end;
/

--Authorize Users, this is where the problem comes in.S
begin
  sa_user_admin.set_user_labels

                   (policy_name    => 'TEST1POL',
                    user_name      => 'CLEARED_USER',
                    max_read_label => 'SEC');

  sa_user_admin.set_user_labels
                   (policy_name    => 'TEST1POL',
                    user_name      => 'NOTCLEARED_USER',
                    max_read_label => 'NOT');

  -- I can not set label for MIDTIERUSER since this users

• rights are mantained in an LDAP server and based on their --definition on LDAP we set their ACCESS_PROFILE end; /

create table midtiersrv.test as select rownum aid from all_objects where rownum < 100;
grant select on midtiersrv.test to midtieruser;

• Apply the policy before we update the data BEGIN sa_policy_admin.apply_table_policy (policy_name => 'TEST1POL', schema_name => 'MIDTIERSRV', table_name => 'TEST', table_options => 'NO_CONTROL' ); END; /
• Set all records to higest UPDATE midtiersrv.test SET TEST1POL_LABEL = char_to_label ('TEST1POL', 'SEC'); commit; UPDATE midtiersrv.test SET TEST1POL_LABEL = char_to_label ('TEST1POL', 'NOT') where aid <= 10; commit;

--Apply pplicy for good

BEGIN
  sa_policy_admin.remove_table_policy

                   (policy_name    => 'TEST1POL',
                    schema_name    => 'MIDTIERSRV',
                    table_name     => 'TEST');
  sa_policy_admin.apply_table_policy
                  (policy_name      => 'TEST1POL',
                   schema_name      => 'MIDTIERSRV',
                   table_name       => 'TEST',
                   table_options    => 'READ_CONTROL');

END;
/

• Give SECMAN the rights to change ACCESS POLICIES begin sa_user_admin.set_user_privs (policy_name => 'TEST1POL', user_name => 'SECMAN', PRIVILEGES => 'PROFILE_ACCESS'); sa_user_admin.set_user_privs (policy_name => 'TEST1POL', user_name => 'MIDTIERSRV', PRIVILEGES => 'PROFILE_ACCESS'); sa_user_admin.set_user_privs (policy_name => 'TEST1POL', user_name => 'MIDTIERUSR', PRIVILEGES => NULL); end; /
• Just to prove it works, see results from following selects when
• changing the ACCESS_PROFILE connect secman/secman set col SEC_LABEL a25 select count(*) "No Access Set", sa_session.label('TEST1POL') SEC_LABEL from midtiersrv.test;

exec sa_session.set_access_profile ('TEST1POL','CLEARED_USER'); select count (*) "CLEARED Policy Set" ,

       sa_session.label ('TEST1POL') SEC_LABEL from midtiersrv.test ;

exec sa_session.set_access_profile ('TEST1POL','NOTCLEARED_USER'); select count (*) "NOTCLEARED User Policy Set",

       sa_session.label ('TEST1POL') SEC_LABEL from midtiersrv.test ;

• I'm using a Application Context to maintain tags on privilages drop context test_context;

create or replace package secman_pkg as
  procedure set_access;
end;
/

create or replace package body secman_pkg as   procedure set_access as
    v_ldap_result varchar2(100);
  begin

• Do some lookup on LDAP using DBMS_LDAP. Get following back form LDAP lookup. v_ldap_result := 'CLEARED_USER'; dbms_session.set_context('TEST_CONTEXT', 'SECLEVEL', v_ldap_result); sa_session.set_access_profile ('TEST1POL','CLEARED_USER'); end; begin null; end; /

create or replace context test_context using secman.secman_pkg;

• Now I need to be able to define the access for users based
• on their LDAP entries. Will do this during database login create or replace trigger secman.set_user_acces after logon on database declare v_ldap_result varchar2(10); begin secman.secman_pkg.set_access; end set_user_acces; /
• Now lets connect and test to see if it works? prompt Normal Connection from SECMAN connect secman/secman select sys_context ('TEST_CONTEXT','SECLEVEL') Context from dual; select count(*) "No Access Set", sa_session.label('TEST1POL') SEC_LABEL from midtiersrv.test;
• Now the lets test the trigger and authentication
• procedure for the MIDTIERSRV

prompt Normal Connection from MIDTIERSRV connect midtiersrv/midtiersrv
select sys_context ('TEST_CONTEXT','SECLEVEL') Context from dual; select count(*) "No Access Set",

       sa_session.label('TEST1POL') SEC_LABEL   from midtiersrv.test;

• But when I connect as the MIDTIERUSR user via the MIDTIERSRV proxy user
• it all goes WRONG!!!
• Why does the proxy user and the owner of the procedure that changes the access
• profile not have the rights to do so when the connection is a proxy connection.

prompt Proxy Connection of MIDTIERUSER via MIDTIERSRV connect midtiersrv[midtieruser]/midtiersrv select sys_context ('TEST_CONTEXT','SECLEVEL') Context from dual; select count(*) "No Access Set",

       sa_session.label('TEST1POL') SEC_LABEL   from midtiersrv.test; Received on Tue Aug 01 2006 - 13:52:26 CDT