Amazon Contextual Product Ads

Sunday, 15 May 2011

Using PL/SQL to create OA user and attach "System Administrator" responsibility

I faced several times when I had a full access to the DB, but could not access Oracle Application, because I had no user.

So I decided to write a function which will create a new user and attach it new responsibility

First of all, you need to compile a function which attaching the responsibility to specific user. Here is the source code

create or replace procedure Attach_Responsibility(p_resp_key varchar2,
p_user_id
number) is
l_respons_id
number;
l_resp_appl
number;
begin
begin
select t.responsibility_id
into l_respons_id
from fnd_responsibility_vl t
where t.responsibility_key=p_resp_key;
select t.application_id
into l_resp_appl
from fnd_responsibility_vl t
where t.responsibility_key=p_resp_key;
exception
when no_data_found then
dbms_output.put_line(
'Cannot get responsibility_id or application_id for SYSTEM_ADMINISTRATOR');
when others then
dbms_output.put_line(sqlerrm);
end; fnd_user_resp_groups_api.insert_assignment(user_id => p_user_id
,responsibility_id
=> l_respons_id --System Administrator
,responsibility_application_id => l_resp_appl
--,security_group_id => 0
,start_date => SYSDATE - 1000
,end_date
=> SYSDATE+1000
,description
=> 'Description');
commit;
end Attach_Responsibility;

And now the script that will create new user and call the function above to attach him X responsibility


declare
l_user_exists
number;
l_user_name fnd_user.
user_name%type := 'MYNEWUSER';
l
varchar2(1000);
l_user_id
number;
l_respons_id
number;
l_resp_appl
number;
begin
select count(1)
into l_user_exists
from fnd_user
where user_name = l_user_name;
dbms_output.put_line(
'User exist: '||l_user_exists);
if l_user_exists = 0 then
fnd_user_pkg.CreateUser(x_user_name
=> l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'welcome!1');
commit;
select user_id
into l_user_id
from fnd_user
where user_name=l_user_name;
dbms_output.put_line(
'User ID ['||l_user_name||']'||l_user_id);
else
select user_id
into l_user_id
from fnd_user
where user_name=l_user_name;
fnd_user_pkg.UpdateUser(x_user_name
=> l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME1');
end if;
commit;
Attach_Responsibility(
'SYSTEM_ADMINISTRATOR',
l_user_id);
Attach_Responsibility(
'APPLICATION_DEVELOPER',
l_user_id);
end;

User's name is MYNEWUSER

l_user_name    fnd_user.user_name%type := 'MYNEWUSER';

check if user already exists


select count(1)
into l_user_exists
from fnd_user
where user_name = l_user_name;

if user exists, change the password to WELCOME!1


fnd_user_pkg.UpdateUser(x_user_name => l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME!1');

if it doesn't exists, create with initial password WELCOME!1


fnd_user_pkg.CreateUser(x_user_name => l_user_name,
x_owner
=> 'CUST',
x_unencrypted_password
=> 'WELCOME!1');

After user created all we need to do is attach related responsibility. In this case we are going to attach "System Administrator" and "Application Developer" responsibilities


Attach_Responsibility('SYSTEM_ADMINISTRATOR',
l_user_id);
Attach_Responsibility(
'APPLICATION_DEVELOPER',
l_user_id);

No comments:

Post a Comment