Create new User with same responsibilities as an existing user

Find responsibilities attached to a user

SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,fnd_user fu
WHERE fu.user_name = '&&username'
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

SIMPLEST WAY TO Create application users with same responsibilties  across instances

Downloading Application User Responsibilities

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites in another instance

FNDLOAD apps/torpedor12 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Create another Application user with same Responsibilities as the existing application user.

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt FND_USER USER_NAME='DC128877'

Uploading Application user Responsibilites to another Applicatioon user in same instance

do vi *.ldt file
Change username to new username
vi XX_FND_USER_PASSI.ldt

In this ldt file change USER_NAME to new user - then upload the ldt file

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSI.ldt

Note : If Application user does not exists it will create it and if it exists it will add the responsibilities its missing.

Can not login to Applications : oracle.apps.fnd.framework.OAException: Could not load application module 'oracle.apps.fnd.sso.login.server.MainLoginPageAM'.

No user able to connect: 

oracle.apps.fnd.framework.OAException: Could not load application module

'oracle.apps.fnd.sso.login.server.MainLoginPageAM'.

## Detail 0 ##

oracle.apps.fnd.framework.OAException: Application: FND, Message Name:

FND_NO_TRANSACTION_ID.

===========CAUSE =============

One of the Tablespace is full 

=======SOLUTION =================

Increase the tablespace and retry ....

Ref: 737960.1
        

Oracle Database User Management

CREATING THE USER:

create user <USERNAME> identified by <PASSWORD> 

default tablespace <TABLESPACE NAME> 

temporary tablespace <TABLESPAC NAME> 

quota 30m on <TABLESPACE NAME>; 

Eg:

create user <John> identified by <John> 

default tablespace <users> 

temporary tablespace <temp> 

quota 30m on <users>; 

Above command creates a user chandra with password chandra. Consider the tablespace you have in which chandra will store his data is "USERS". The tablespace used for storing temporary segments will be "TEMP" and the amount of space which the user chandra can use on "USERS" tablespace is 30M.

DROPING THE USER:

            SQL> DROP USER John CASCADE;
 

ALTERING/UNLOCKING ACCOUNT: 

SQL> alter user john identified by john account unlock;

The above command alters or unlocks the "john" user with password "john".

SQL> select username, account_status, default_tablespace, 

temporary_tablespace, profile from dba_users 

where username = 'john';

SQL> select * from dba_ts_quotas where username = 'john'; 

GRANTING AND REVOKING PRIVILEGES:

Syntax:

SQL>GRANT <PRIVILAGE> TO <USERNAME>

SQL>REVOKE <PRIVILAGE> FROM <USERNAME>

Eg:
SQL> GRANT create table to john;‎ 
SQL> GRANT create session to john;
SQL> GRANT create any table, create tablespace to john;
SQL> REVOKE create any table from john;
SQL> REVOKE create tablespace from john;
SQL> GRANT select, insert, update, delete on <TABLENAME> to john;
SQL> REVOKE update,delete on USERS.PAY_PAYMENT_MASTER from john;
 

ROLES:

Creating Roles
Create Role	CREATE ROLE <role_name>;
	CREATE ROLE read_only;
Create Password Protected Role	CREATE ROLE <role_name> IDENTIFIED BY <password>;
	CREATE ROLE dba IDENTIFIED BY "johnny";
Assigning Privileges And Roles To Roles
Assign Privilege To A Role	GRANT <privilege_name> TO <role_name>;
	GRANT create session TO read_only
Create A Role Heirarchy	GRANT <role_name> TO <role_name>;
	CREATE ROLE ap_clerk; GRANT read_only TO ap_clerk; GRANT select ON general_ledger TO ap_clerk; GRANT insert ON ap_master TO ap_clerk; GRANT update ON ap_master TO ap_clerk; GRANT insert ON ap_detail TO ap_clerk; GRANT update ON ap_detail TO ap_clerk;
Add Another Layer To The Heirarchy	GRANT <roles and privileges> TO <role_name>;
	CREATE ROLE ap_manager IDENTIFIED BY appwd; GRANT ap_clerk TO ap_manager; GRANT delete ON ap_master TO ap_manager; GRANT delete ON ap_detail TO ap_manager; GRANT select any table TO ap_manager;
Assigning Roles
Assigning Roles To Users	GRANT <roles_name> TO <user_name>;
	GRANT read_only TO jbiden; GRANT ap_clerk TO jstough; GRANT ap_clerk TO ckeizer; GRANT ap_clerk TO rallen; GRANT ap_manager TO escott;
Revoking Privileges From Roles
Revoke Privilege	REVOKE <privilege_name> FROM <role_name>;
	REVOKE select any table FROM ap_manager;
Revoking Roles
Revoke a role from a user	REVOKE <role_name> FROM <user_name>;
	REVOKE ap_manager FROM escott;
Revoke A Role And Drop Any Invalidated Constraints	REVOKE ALL ON <table_name> FROM <schema_name>  CASCADE CONSTRAINTS;
	REVOKE ALL ON invoices FROM abc  CASCADE CONSTRAINTS;
Activating & Decactivating Roles
Activating A Role	SET ROLE <role_name>;
	SET ROLE ap_clerk;
Activating A Password Protected Role	SET ROLE <role_name> IDENTIFIED BY <role_password>;
	SET ROLE ap_manager IDENTIFIED BY appwd;
Activating All Roles	SET ROLE all;
Activating All Roles Except One	SET ROLE all EXCEPT <role_name>;
	SET ROLE all EXCEPT ap_manager;
Deactivating A Role	Can not be done on an indiviDUAL basis
Deactivating All Roles	SET ROLE none;
Drop Role
Dropping A Role	DROP ROLE <role_name>;
	DROP ROLE manager_role;