seems to be a bug.
bernard (bernard_at_bosvark.com) wrote:
> 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 Thu Aug 03 2006 - 09:05:34 CDT
