Role access to user

SQL> conn adaptive_ro
Enter password:
Connected.

SQL> select * from dba_role_privs where GRANTEE = ‘ADAPTIVE_RO’
2 ;

GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
ADAPTIVE_RO ADAPTIVE_ROLE NO YES
ADAPTIVE_RO RESOURCE NO YES
ADAPTIVE_RO CONNECT NO YES

SQL>
SQL> select count(*) from apps.fnd_user;

COUNT(*)
———-
13958

SQL> revoke APPS_READ from ADAPTIVE_RO
2 ;

Revoke succeeded.

revoke ADAPTIVE_ROLE from ADAPTIVE_RO ;

SQL> select grantee, granted_role from dba_role_privs where grantee=’ADAPTIVE_RO’;

GRANTEE GRANTED_ROLE
—————————— ——————————
ADAPTIVE_RO RESOURCE
ADAPTIVE_RO CONNECT

create role ADAPTIVE_GRANTS identified by adaptive_grants;

grant select on gl.gl_balances to ADAPTIVE_GRANTS;
grant select on GL.GL_CODE_COMBINATIONS to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_LINES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_HEADERS to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS;

SQL> grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist

grant select on GL.GL_JE_SOURCES_TL to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES_TL to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_BATCHES to ADAPTIVE_GRANTS;
grant select on GL.GL_IMPORT_REFERENCES to ADAPTIVE_GRANTS;
grant select on AP.AP_AE_LINES_ALL to ADAPTIVE_GRANTS;
grant select on ap.ap_ae_headers_all to ADAPTIVE_GRANTS;
grant select on ap.ap_invoice_distributions_all to ADAPTIVE_GRANTS;
grant select on ap.ap_invoices_all to ADAPTIVE_GRANTS;
grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS;
SQL> grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS;
grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> grant select on APPS.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%SOURCES%’;
select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘PO_VENDORS’;

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%CATEGORIES%’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
GL GL_JE_CATEGORIES_TL_U3 INDEX
GL GL_JE_CATEGORIES_TL_U2 INDEX
GL GL_JE_CATEGORIES_TL_U1 INDEX
GL GL_JE_CATEGORIES_TL TABLE
GL GL_JE_CATEGORIES_S SEQUENCE
APPS OKL_GL_JE_CATEGORIES_UV VIEW
APPS GL_JE_CATEGORIES_PKG PACKAGE BODY
APPS GL_JE_CATEGORIES_V VIEW
APPS GL_JE_CATEGORIES_VL VIEW
APPS GL_JE_CATEGORIES_PKG PACKAGE
APPS GL_JE_CATEGORIES SYNONYM
APPS GL_JE_CATEGORIES_TL SYNONYM
APPS GL_JE_CATEGORIES_S SYNONYM

13 rows selected.

SQL> grant select on APPS.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;

Grant succeeded.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%SOURCES%’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
GL GL_JE_SOURCES_TL_U3 INDEX
GL GL_JE_SOURCES_TL_U2 INDEX
GL GL_JE_SOURCES_TL_U1 INDEX
GL GL_JE_SOURCES_TL TABLE
GL GL_JE_SOURCES_S SEQUENCE
APPS IGI_CBR_GL_JOURNAL_SOURCES_V VIEW
APPS GL_JE_SOURCES_PKG PACKAGE BODY
APPS GL_JE_SOURCES_V VIEW
APPS GL_INTERFACE_SOURCES_V VIEW
APPS GL_JE_SOURCES_VL VIEW
APPS GL_JE_SOURCES_PKG PACKAGE
APPS GL_JE_SOURCES SYNONYM
APPS GL_JE_SOURCES_TL SYNONYM
APPS GL_JE_SOURCES_S SYNONYM

14 rows selected.
SQL> grant select on APPS.GL_JE_SOURCES to ADAPTIVE_GRANTS;

Grant succeeded.
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘PO_VENDORS’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
APPS PO_VENDORS VIEW

SQL> grant select on APPS.PO_VENDORS to ADAPTIVE_GRANTS;

Grant succeeded.

SQL>
SQL> grant ADAPTIVE_GRANTS to ADAPTIVE_RO;

Grant succeeded.

SQL> select grantee, granted_role from dba_role_privs where grantee=’ADAPTIVE_RO’;

GRANTEE GRANTED_ROLE
—————————— ——————————
ADAPTIVE_RO RESOURCE
ADAPTIVE_RO CONNECT
ADAPTIVE_RO ADAPTIVE_GRANTS

SQL> conn adaptive_ro
Enter password:
Connected.
SQL> select count(*) from apps.fnd_user;
select count(*) from apps.fnd_user
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

SQL> alter user ADAPTIVE_RO default role ADAPTIVE_GRANTS, resource,connect;

User altered.
SQL> conn adaptive_ro/oracle123
Connected.
SQL> select count(*) From APPS.GL_JE_CATEGORIES ;

COUNT(*)
———-
177

 

 

 
SQL> show user
USER is “SYS”
SQL> select name from v$database;

NAME
———
UAT4
SQL> select count(*) from APPS.GL_CODE_COMBINATIONS_KFV;

COUNT(*)
———-
82940

SQL>
SQL> grant select on APPS.GL_CODE_COMBINATIONS_KFV to adaptive_ro;

Grant succeeded.

SQL> select count(*) from XLA.XLA_AE_LINES;

COUNT(*)
———-
36820855

SQL> grant select on XLA.XLA_AE_LINES to adaptive_ro;

Grant succeeded.

SQL> select count(*) from XLA.XLA_AE_HEADERS;

COUNT(*)
———-
12785537

SQL> grant select on XLA.XLA_AE_HEADERS to adaptive_ro;

Grant succeeded.

SQL> select count(*) from XLA.XLA_TRANSACTION_ENTITIES;

COUNT(*)
———-
11630609

SQL> grant select on XLA.XLA_TRANSACTION_ENTITIES to adaptive_ro;

Grant succeeded.

SQL> select count(*) from XLA.XLA_EVENTS;

COUNT(*)
———-
12845092

SQL> grant select on XLA.XLA_EVENTS to adaptive_ro;

Grant succeeded.

SQL> select count(*) from AP.AP_CHECKS_ALL;

COUNT(*)
———-
2128700

SQL> grant select on AP.AP_CHECKS_ALL to adaptive_ro;

Grant succeeded.

SQL> select count(*) from AR.HZ_PARTIES;

COUNT(*)
———-
6780418

SQL> grant select on AR.HZ_PARTIES to adaptive_ro;

Grant succeeded.

SQL> select count(*) from ap.ap_invoices_all;

COUNT(*)
———-
2692939

SQL> select count(*) from XXGLXLAAPDTL_V;
select count(*) from XXGLXLAAPDTL_V
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from apps.XXGLXLAAPDTL_V;

COUNT(*)
———-
10723123

SQL> grant select on apps.XXGLXLAAPDTL_V to adaptive_ro;

Grant succeeded.

***************************************************************************
SQL> show user
USER is “adaptive_ro”
SQL> select name from v$database;

NAME
———
UAT4

SQL> select count(*) from APPS.GL_CODE_COMBINATIONS_KFV;
select count(*) from APPS.GL_CODE_COMBINATIONS_KFV
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from APPS.GL_CODE_COMBINATIONS_KFV;

COUNT(*)
———-
82940

SQL> select count(*) from XLA.XLA_AE_LINES;
select count(*) from XLA.XLA_AE_LINES
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from XLA.XLA_AE_LINES;

COUNT(*)
———-
36820855

SQL> select count(*) from XLA.XLA_AE_HEADERS;
select count(*) from XLA.XLA_AE_HEADERS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from XLA.XLA_AE_HEADERS;

COUNT(*)
———-
12785537

SQL> select count(*) from XLA.XLA_TRANSACTION_ENTITIES;
select count(*) from XLA.XLA_TRANSACTION_ENTITIES
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from XLA.XLA_TRANSACTION_ENTITIES;

COUNT(*)
———-
11630609

SQL> select count(*) from XLA.XLA_EVENTS;
select count(*) from XLA.XLA_EVENTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from XLA.XLA_EVENTS;

COUNT(*)
———-
12845092

SQL> select count(*) from AP.AP_CHECKS_ALL;
select count(*) from AP.AP_CHECKS_ALL
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from AP.AP_CHECKS_ALL;

COUNT(*)
———-
2128700

SQL> select count(*) from AR.HZ_PARTIES;
select count(*) from AR.HZ_PARTIES
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from AR.HZ_PARTIES;

COUNT(*)
———-
6780418

SQL> select count(*) from ap.ap_invoices_all;

COUNT(*)
———-
2692939
SQL> select count(*) from apps.XXGLXLAAPDTL_V;
COUNT(*)
———-
10723123

 

 

 

Hi George,

We only performed steps as mentioned in 100-103394 but did not perform any steps as per 100-102909.
Hence we did not revoke access of apps schema objects from adaptive_ro user.
Kindly confirm if you were expecting the steps from both 100-103394 and 100-102909 to be replicated in PROD.

As per request 100-102909 we performed below steps on UAT4:
1) Revoked APPS_READ role from ADAPTIVE_RO user.
2) Granted select access on below objects:
* gl.gl_balances
* GL.GL_CODE_COMBINATIONS
* GL.GL_JE_LINES
* GL.GL_JE_HEADERS
* GL.GL_JE_SOURCES
* GL.GL_JE_SOURCES_TL
* GL.GL_JE_CATEGORIES
* GL.GL_JE_CATEGORIES_TL
* GL.GL_JE_BATCHES
* GL.GL_IMPORT_REFERENCES
* AP.AP_AE_LINES_ALL
* ap.ap_ae_headers_all
* ap.ap_invoice_distributions_all
* ap.ap_invoices_all
* PO.PO_VENDORS
### ADAPTIVE_RO user would be able to view and access objects exposed for public users.

As per request #100-103394, we performed below steps on UAT4:
1) Granted select access to below objects for ADAPTIVE_RO user:
APPS.GL_CODE_COMBINATIONS_KFV
XLA.XLA_AE_LINES
XLA.XLA_AE_HEADERS
XLA.XLA_TRANSACTION_ENTITIES
XLA.XLA_EVENTS
AP.AP_CHECKS_ALL
AR.HZ_PARTIES
ap.ap_invoices_all
2) Created view apps.XXGLXLAAPDTL_V (copied from DEV) and granted select access to ADAPTIVE_RO user.

Regards,
Madhuri.

SQL> conn adaptive_ro
Enter password:
Connected.

SQL> select * from dba_role_privs where GRANTEE = ‘ADAPTIVE_RO’
2 ;

GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
ADAPTIVE_RO ADAPTIVE_ROLE NO YES
ADAPTIVE_RO RESOURCE NO YES
ADAPTIVE_RO CONNECT NO YES

SQL>
SQL> select count(*) from apps.fnd_user;

COUNT(*)
———-
13958

SQL> revoke APPS_READ from ADAPTIVE_RO
2 ;

Revoke succeeded.

SQL> select grantee, granted_role from dba_role_privs where grantee=’ADAPTIVE_RO’;

GRANTEE GRANTED_ROLE
—————————— ——————————
ADAPTIVE_RO RESOURCE
ADAPTIVE_RO CONNECT

create role ADAPTIVE_GRANTS identified by adaptive_grants;

grant select on gl.gl_balances to ADAPTIVE_GRANTS;
grant select on GL.GL_CODE_COMBINATIONS to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_LINES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_HEADERS to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS;

SQL> grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_SOURCES to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist

grant select on GL.GL_JE_SOURCES_TL to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES_TL to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_BATCHES to ADAPTIVE_GRANTS;
grant select on GL.GL_IMPORT_REFERENCES to ADAPTIVE_GRANTS;
grant select on AP.AP_AE_LINES_ALL to ADAPTIVE_GRANTS;
grant select on ap.ap_ae_headers_all to ADAPTIVE_GRANTS;
grant select on ap.ap_invoice_distributions_all to ADAPTIVE_GRANTS;
grant select on ap.ap_invoices_all to ADAPTIVE_GRANTS;
grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS;
SQL> grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS;
grant select on PO.PO_VENDORS to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> grant select on APPS.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;
grant select on GL.GL_JE_CATEGORIES to ADAPTIVE_GRANTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%SOURCES%’;
select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘PO_VENDORS’;

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%CATEGORIES%’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
GL GL_JE_CATEGORIES_TL_U3 INDEX
GL GL_JE_CATEGORIES_TL_U2 INDEX
GL GL_JE_CATEGORIES_TL_U1 INDEX
GL GL_JE_CATEGORIES_TL TABLE
GL GL_JE_CATEGORIES_S SEQUENCE
APPS OKL_GL_JE_CATEGORIES_UV VIEW
APPS GL_JE_CATEGORIES_PKG PACKAGE BODY
APPS GL_JE_CATEGORIES_V VIEW
APPS GL_JE_CATEGORIES_VL VIEW
APPS GL_JE_CATEGORIES_PKG PACKAGE
APPS GL_JE_CATEGORIES SYNONYM
APPS GL_JE_CATEGORIES_TL SYNONYM
APPS GL_JE_CATEGORIES_S SYNONYM

13 rows selected.

SQL> grant select on APPS.GL_JE_CATEGORIES to ADAPTIVE_GRANTS;

Grant succeeded.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘%GL%SOURCES%’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
GL GL_JE_SOURCES_TL_U3 INDEX
GL GL_JE_SOURCES_TL_U2 INDEX
GL GL_JE_SOURCES_TL_U1 INDEX
GL GL_JE_SOURCES_TL TABLE
GL GL_JE_SOURCES_S SEQUENCE
APPS IGI_CBR_GL_JOURNAL_SOURCES_V VIEW
APPS GL_JE_SOURCES_PKG PACKAGE BODY
APPS GL_JE_SOURCES_V VIEW
APPS GL_INTERFACE_SOURCES_V VIEW
APPS GL_JE_SOURCES_VL VIEW
APPS GL_JE_SOURCES_PKG PACKAGE
APPS GL_JE_SOURCES SYNONYM
APPS GL_JE_SOURCES_TL SYNONYM
APPS GL_JE_SOURCES_S SYNONYM

14 rows selected.
SQL> grant select on APPS.GL_JE_SOURCES to ADAPTIVE_GRANTS;

Grant succeeded.
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where OBJECT_NAME like ‘PO_VENDORS’;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
APPS PO_VENDORS VIEW

SQL> grant select on APPS.PO_VENDORS to ADAPTIVE_GRANTS;

Grant succeeded.

SQL>
SQL> grant ADAPTIVE_GRANTS to ADAPTIVE_RO;

Grant succeeded.

SQL> select grantee, granted_role from dba_role_privs where grantee=’ADAPTIVE_RO’;

GRANTEE GRANTED_ROLE
—————————— ——————————
ADAPTIVE_RO RESOURCE
ADAPTIVE_RO CONNECT
ADAPTIVE_RO ADAPTIVE_GRANTS

SQL> conn adaptive_ro
Enter password:
Connected.
SQL> select count(*) from apps.fnd_user;
select count(*) from apps.fnd_user
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

SQL> alter user ADAPTIVE_RO default role ADAPTIVE_GRANTS, resource,connect;

User altered.
SQL> conn adaptive_ro/oracle123
Connected.
SQL> select count(*) From APPS.GL_JE_CATEGORIES ;

COUNT(*)
———-
177

Advertisements

SQL id and SQL_TEXT from concurrent request

SELECT fcr.request_id,vsq.sql_id,
fcp.user_concurrent_program_name,
vsq.sql_text

FROM fnd_concurrent_requests fcr,
v$process vp,
v$session vs,
v$sql vsq,
fnd_concurrent_programs_vl fcp
WHERE fcr.request_id = ”
AND fcr.oracle_process_id = vp.spid
AND vs.sql_address = vsq.address
AND vs.paddr = vp.addr
AND fcr.concurrent_program_id = fcp.concurrent_program_id;

Find concurrent request trace file location

SELECT ‘Request id: ‘||request_id ,
‘Trace id: ‘||oracle_Process_id,
‘Trace Flag: ‘||req.enable_trace,
‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ‘||prog.user_concurrent_program_name,
‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status : ‘||decode(phase_code,’R’,’Running’) ||’-‘||decode(status_code,’R’,’Normal’),
‘SID Serial: ‘||ses.sid||’,’|| ses.serial#, ‘Module : ‘||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where
req.request_id = ‘1726845’
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’
and dbnm.name=’db_name’
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

 

 

 

Hyperion Planning Application Deployment Error

Parsing Application Properties…Done
[Mar 10, 2016 1:07:26 PM]: Parsing Dimensions info…Done
[Mar 10, 2016 1:07:27 PM]: The database connection failed.[Mar 10, 2016 1:07:27 PM]: An Exception occurred during Application deployment.: The database connection failed.

SQL> select username, account_status from dba_users where username=’QCPLAN’;

USERNAME
——————————————————————————–
ACCOUNT_STATUS
——————————–
QCPLAN
EXPIRED
SQL> select password from user$ where name=’QCPLAN’;

PASSWORD
——————————————————————————–
*******

SQL> alter user QCPLAN identified by values ‘********’;

User altered.

SQL> select username, account_status from dba_users where username=’QCPLAN’;

USERNAME
——————————————————————————–
ACCOUNT_STATUS
——————————–
QCPLAN
OPEN

Retest Deployment.

 

Direct Login to forms in EBS

Direct Login to forms in eBusiness Suite
The usual url for loggin in to Oracle Application is
http://<server name>:<port>/
which most of the times gets directed to
http://<server name>:<port>/OA_HTML/AppsLocalLogin.jsp

But for some reason or for troubleshooting purpose we may need to login directly to forms.
Below are the urls for directly connecting forms.

For 11i :-
Socket mode:- http://<server name>:<port>/dev60cgi/f60cgi
Servlet mode:- http://<server name>:<port>/servlet/f60

For R12 :-
Servlet mode:- http://<server name>:<port>/forms/frmservlet
Socket mode:- http://<server name>:<port>/OA_HTML/frmservlet

You can see the forms opening but after entering login details you may not be able to login and find the error as “APP-FND-01542: This application server is not authorized to access this database”

The reason for this is, direct forms login is disabled. To check and change that check the value for context variable “s_appserverid_authentication”. You can make it ON or OFF to change the security level. SECURE doesn’t allow you to login directly and gives the message as above.

Below are possible modes.
ON :- Partial
SECURE :- activates full server security (SECURE mode)
OFF :- deactivates server security

Change the parameter and run autoconfig.

DB size

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”,
round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”,
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used,
(select sum(bytes) as p
from dba_free_space) free group by free.p;