OraFAQ Forum: Security » OLS does not appear to work for PROXY connections

Home » RDBMS Server » Security » OLS does not appear to work for PROXY connections

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OLS does not appear to work for PROXY connections [message #185439]

Tue, 01 August 2006 14:27

bniekerk
Messages: 2
Registered: February 2006
Location: London

Junior Member

Hypothetical case: 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.

Is this correct?

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;

Report message to a moderator

Previous Topic:	Creation of New Username & Password in SQL*PLUS?
Next Topic:	I have two questions about authentication,can you help me?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:17:13 CDT 2024