Oracle application Audit files information


REM *
REM * PROGRAM:     rahc_sec_profiles.sql
REM * USAGE:       @rahc_sec_profiles.sql
REM *
REM * LANGUAGE:    SQL*Plus
REM *
REM * DESCRIPTION: Check FND security profile
REM *    Please pay attention in the following profile
REM *        Sign-On:Audit Level                   (A=None,B=User,C=Responsibility,D=Forms)
REM *        Sign-On:Notification                  Y/N       
REM *        Signon Password Custom                        custom function to encrypt/decrypt password
REM *        Signon Password Failure Limit              
REM *        Signon Password Length
REM *        Signon Password Nouse
REM *        Signon Password Hard to Guess           Y/N
REM *        Password case sensitive                          11.5.10.2
REM *
REM * EXAMPLES:
REM *   @rahc_sec_profiles.sql
REM *
REM * NOTES:      
REM *
REM * CHANGES:
REM *
REM * Version   Date         Who             Comments
REM * 1.0.0     02-May-07    Somchart L.     Initial version.
REM *

prompt
prompt ====================================================================================================================================
prompt Checking security profiles
prompt ====================================================================================================================================
prompt

CLEAR COLUMNS
CLEAR BREAKS

SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK OFF

SET LINESIZE 132
SET PAGESIZE 50

col profile_option_id format 999999 heading "Profile|Option ID"
col Pro_Level format a20 wrap
col user_name format a20 wrap
col Prof_Name format a40 wrap
col Prof_Value format a20 wrap

SET HEADING ON
TTITLE 'Security Profiles'

SELECT   v.profile_option_id
       , decode(v.level_id,10001,'Site'
                          ,10002,'Application'
                          ,10003,'Responsibility'
                          ,10004,'User'
                          ,v.level_id) Pro_Level
       , user_name
       , pot.user_profile_option_name prof_name
       , substr(v.profile_option_value, 1,45) prof_value
FROM  
         apps.fnd_profile_options o
       , apps.fnd_profile_options_tl pot
       , apps.fnd_profile_option_values v
       , apps.fnd_user fu
WHERE   
         o.profile_option_name = pot.profile_option_name
AND      o.profile_option_id = v.profile_option_id (+)
AND      o.application_id    = v.application_id (+)
AND      v.level_value = fu.user_id (+) 
AND      (pot.user_profile_option_name like 'Sign%assword%'
       or pot.user_profile_option_name like 'Sign%otific%'
       or pot.user_profile_option_name like 'Sign%udit%'
       or pot.user_profile_option_name like 'Utilities%Diag%'
       or pot.user_profile_option_name like 'ICX:Session Timeout')     
ORDER BY v.level_id, pot.user_profile_option_name
/

TTITLE OFF
prompt
prompt Note:
prompt Metalink Note 233436.1 recommends the following profiles
prompt -  SIGNON_PASSWORD_LENGTH        = 8
prompt -  SIGNON_PASSWORD_HARD_TO_GUESS = YES
prompt -  SIGNON_PASSWORD_NO_REUSE      = 180
prompt -  ICX_SESSION_TIMEOUT           = 30  (unless using SSO with OID)
prompt


exit

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Oracle Standby database

Shutting Down a Physical Standby Database Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.  If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If Redo Apply is running, cancel it as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database. SQL> SHUTDOWN IMMEDIATE; Start and mount the physical standby database: SQL> STARTUP MOUNT; Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE; ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select ...
Read more

SQL to find the SQL,SID , long running , CPU, I/O

Script – Find SQL being executed by a OS Process ID (PID) prompt "Please Enter The UNIX Process ID" set pagesize 50000 set linesize 30000 set long 500000 set head off select s.username su, substr(sa.sql_text,1,540) txt from v$process p, v$session s, v$sqlarea sa where p.addr=s.paddr and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+) and spid=&SPID; script used to find out cpu used by this session SELECT substr(a.sid,1,5) "Sid", substr(a.process,1,7) "Process", nvl(a.USERNAME,'ORACLE PROC') "user", v.value "CPU used by this session" FROM v$statname s, v$sesstat v, v$session a WHERE s.name = 'CPU used by this session' and v.statistic#=s.statistic# AND v.sid = a.sid and v.value > 0 ORDER BY v.value DESC; Top 10 cpu heavy SQLs set pagesize 999 set linesize 150 col cpu_time format 9999999999 heading "Cputime" col sql_text f...
Read more