Wednesday, November 18, 2009

What is the difference between a Role and a Responsibility?

Responsibilities can now be considered a special type of role that represents the set of navigation menus contained within an application. Therefore, responsibilities loosely represent an application itself, whereas roles can be used to determine what parts of that application (and data therein) a user has access. This represents a shift in the definition of a responsibility in Oracle Applications. Previously, a responsibility has been used not only to define the application navigation menus, but also to confer privileges and permissions within that application. Using this definition of responsibility, it was often necessary to create several similar responsibilities in order to effectively carve out data and functional security access for a group of users. This has increased the overall cost of ownership as the number of responsibilities has grown.
Oracle Applications follows the Role Based Access Control (RBAC) Reference Model (ANSI INCITS 359-2004) definition of a role as "a job function within the context of an organization with some associated semantics regarding the authority and responsibility conferred on the user assigned to the role." Roles can now be defined to determine what applications (responsibilities) as well as what data and functions within those applications a user has access to.
By leveraging the RBAC model, users will no longer need to be directly assigned the lower level permissions and responsibilities, as these can be implicitly inherited based upon the roles assigned to the user. Roles can now be defined to consolidate responsibilities and other roles through role inheritance, as well as lower level permissions (functions) and data security policies. This is accomplished through a one-time setup, where all the permissions are assigned to the role. In order to make a mass update in a production system a client only needs to change the permissions or role inheritance hierarchies defined for a role, then all of the users assigned to that role will instantly inherit the new permissions.

Tuesday, November 17, 2009

Creating USER MANAGEMENT ROLE in ORACLE through API

Role Creation API

UMX_ACCESS_ROLES_PVT.insert_role(p_role_name in varchar2,
p_orig_system in varchar2,
p_orig_system_id in number,
p_start_date in date,
p_expiration_date in date,
p_display_name in varchar2,
p_owner_tag in varchar2,
p_description in varchar2);

Example;


begin
UMX_ACCESS_ROLES_PVT.insert_role('UMXRAJEEVCODE2',
'UMX',
0,
to_date('18/11/2009','DD/MM/YYYY'),
null,
'rajeev role name2',
'NCRX',
'rajeev desc2');
commit;
end;

Assigement of Roles to Users


Begin
wf_local_synch.PropagateUserRole(
p_user_name => '&USER_NAME',
p_role_name => '&ROLE_KEY');
commit;
end;

We can create or Assign Role from SYSADMIN user though USER MANAGEMENT RESPONSIBILITY.

Wednesday, November 4, 2009

DBMS SCHEDULAR

Creating Schedule:-
begin

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=1', comments => 'Runtime: Every day all 1 minutes');

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=1', comments => 'Runtime: Every day all 1 SECOND');
dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_0_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=0', comments => 'Runtime: Every day all 0 SECOND');
commit;
end;

Creating Program :-

begin -- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB1', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(0,1); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 0,1' );

-- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB2', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(2,3); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 2,3' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB3', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(4,5); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 4,5' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB4', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(6,7); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 6,7' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB5', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(8,9); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 8,9' ); commit;
end;


Creating Jobs :--

begin -- Connect both dbms_scheduler parts by creating the final job

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_0_AND_1', program_name=> 'REPLAY_STANDARD_ORDER_JOB1', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_2_AND_3', program_name=> 'REPLAY_STANDARD_ORDER_JOB2', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_4_AND_5', program_name=> 'REPLAY_STANDARD_ORDER_JOB3', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_6_AND_7', program_name=> 'REPLAY_STANDARD_ORDER_JOB4', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_8_AND_9', program_name=> 'REPLAY_STANDARD_ORDER_JOB5', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes');
end;

Run Jobs:-

begin dbms_scheduler.run_job('REPLAY_JOB_FOR_0_AND_1',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_2_AND_3',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_4_AND_5',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_6_AND_7',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_8_AND_9',TRUE);

end;

drop job:-
begin--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_0_AND_1', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_2_AND_3', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_4_AND_5', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_6_AND_7', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_8_AND_9', force=>TRUE);
dbms_scheduler.drop_job('REPLAY_JOB_FOR_0_AND_1');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_2_AND_3');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_4_AND_5');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_6_AND_7');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_8_AND_9');

end;


Extra Information

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'INTERVAL_EVERY_MINUTE', attribute => 'repeat_interval', value => 'freq=MINUTELY;interval=2' );
select * from user_scheduler_job_log where job_name='REPLAY_JOB_FOR_0_AND_1';
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where (job_name = 'REPLAY_JOB_FOR_0_AND_1' OR job_name = 'REPLAY_JOB_FOR_2_AND_3') and run_duration <> '+00 00:00:00.000000' order by actual_start_date desc
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'REPLAY_JOB_FOR_0_AND_1' order by actual_start_date desc