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

Giganews Newsgroups
Subject: SA_SESSION.SET_ACCESS_PROFILE does not work for PROXY connections, any idea why?
Posted by:  bernard (bernard_at_bosvark.com) (berna…@bosvark.com)
Date: 1 Aug 2006

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;

Replies