Tuesday, November 30, 2004

Will dbms_job work with policies?

Yes, they will!
DBMS_JOB executes in the context of a user.

Use the following to test:

Create a table:
create table out_ctx (usr_ctx number);

Create a procedure:
create procedure test_ctx
as
usr_ctx number;
begin
select sys_context('MY_CONTEXT','USER_ID') into usr_ctx from dual;

insert into out_ctx values (usr_ctx);
commit;
end;


Spool the job:
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'test_ctx;',
SYSDATE, 'SYSDATE + 1/720');
COMMIT;
END;
/
PRINT jobno


Check the result:
select * from out_ctx;

Delete the job:

BEGIN
DBMS_JOB.REMOVE(&job_number);
END;
/

Friday, November 26, 2004

VPD tuning

Use Note 69408.1
Create the kkoipt_table, set the event to 10060 and do some tests

Another great source of information is http://www.petefinnigan.com/papers/rls.sql


Wednesday, November 17, 2004

Oracle VPD setup

This will be a quick guide on how to use the VPD feature in an Oracle database.

Start with creating a package that will fill your context.

create or replace package
user.mypackage
is
procedure set_user_id_context;
end;

Here comes the package body:

create or replace package body
user.mypackage
is
procedure set_user_id_context
is
sec_user_id number;
begin
--
-- in this case we have a table that holds my users and each user has a code
--
select
code
into
sec_user_id
from
usr
where
upper(id) =
upper(
sys_context(
'USERENV'
,'SESSION_USER'
)
);
--
-- set the context
--

dbms_session.set_context(
'MYCONTEXT_NAME'
, 'USER_ID'
, sec_user_id
);

exception

--
-- This is necessary in order to allow users that are
-- not in the usr table to login, as this procedure is
-- used in a logon trigger
--

when no_data_found then
dbms_session.set_context(
'MY_CONTEXT_NAME'
, 'USER_ID'
, NULL
);
end set_user_id_context;
end;



Then create a context:

connect user/password@sid


create or replace context my_context_name using user.mypackage;
/