Tuesday 30 December 2014

how to enable form and table audit in oracle

General Description
The Audit Trail is an Oracle inbuilt functionality that lets audit specific columns belonging to selected tables. This implementation enhances the security of the system.
Purpose
Purpose of this document is to instruct the user to setup the Oracle AuditTrail on the following tables:
• FND_PROFILE_OPTION_VALUES (Profile Option Updates)
• FND_USER (New User Creations or Updates)
• WF_LOCAL_USER_ROLES (Responsibility Assignments)
Getting Started
For each audited table (i.e. FND_USER), the system will create a shadow table named tablename_A (i.e. FND_USER_A). The maximum size of the shadow table name is 26 characters.
The columns to be audited should be selected carefully to avoid an impact on the performances of the system.
Configure the Profile Options
To enable the Audit Trail, it’s required to set 2 profile options at site level:
System Administrator -> Profile -> System
1. Profile option ‘Sign-On:Audit Level’ set to ‘FORM’


2. Profile Option ‘AuditTrail:Activate’ set to ‘YES’


Enable Audit on the Table Owner
According to which table we want to audit, there can be a different table owner on which the auditing should be enabled.
To table owner can be checked running the following query:

SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME = 'FND_PROFILE_OPTION_VALUES'
OR TABLE_NAME = 'FND_USER'
OR TABLE_NAME = 'WF_LOCAL_USER_ROLES'

The query above will show that the owner of all the tables we want to audit is the user ‘APPLSYS’.
To auditing on the user ‘APPLSYS’ can be enabled in the following way:
System Administrator -> Security -> AuditTrail -> Install
Querying the username ‘APPLSYS’ and making sure that the Audit Checkbox is enabled.


Create and Audit Group
The Audit Group will contain the list of the tables to be audited for our purpose.
System Administrator -> Security -> AuditTrail -> Groups
The Group State should be set to ‘Enable Requested’
Then we create the table list adding the following User Table Names:
• FND_PROFILE_OPTION_VALUES
• Define an Application User
• WF_LOCAL_USER_ROLES
The User Table Name of the FND_USER table is ‘Define an Application User’.


Selecting the Columns to Audit in FND_PROFILE_OPTION_VALUES
The columns can be set opening this form:
System Administrator -> Security -> AuditTrail -> Tables
First Table to query is FND_PROFILE_OPTION_VALUES. The columns to audit are in the image below.


• LEVEL_VALUE_APPLICATION_ID – Set by Default
• LEVEL_VALUE – Set by Default
• LEVEL_ID – Set by Default
• PROFILE_OPTION_ID – Set by Default
• APPLICATION_ID – Set by Default
• PROFILE_OPTION_VALUE – Needed to log the old profile option value
• LAST_UPDATE_DATE – Needed to log Date and Time of when the profile option value was updated
The other columns are not relevant for the auditing purpose.
Selecting the Columns to Audit in FND_USER
Second Table to query is FND_USER. The columns to audit are in the image below.


• USER_ID – Set by Default
• USER_NAME – Needed to log the username updates
• ENCRYPTED_FOUNDATION_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• ENCRYPTED_USER_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• DESCRIPTION – Needed to log the accounts description updates
• END_DATE – Needed to log the updates of the accounts end-date
• START_DATE – Needed to log the updates of the accounts start-date
• PASSWORD_LIFESPAN_DAYS – Needed to log the updates of the accounts lifespan
• EMPLOYEE_ID – Needed to log the updates of the accounts lifespan
• EMAIL_ADDRESS – Needed to log the updates of the accounts email address
The other columns are optional or not relevant for the auditing purpose.
• (*) LAST_LOGON_DATE – Not to be Audited to avoid logging not needed records
Selecting the Columns to Audit in WF_LOCAL_USER_ROLES
Third Table to query is WF_LOCAL_USER_ROLES. The columns to audit are in the image below


• USER_NAME – Set by Default
• ROLE_NAME – Set by Default
• EXPIRATION_DATE – Needed to log the updates of the responsibility end-date
• START_DATE – Needed to log the updates of the responsibility start-date
• ASSIGNMENT_REASON – Needed to log the updates of the responsibility assignment justification
The other columns are optional or not relevant for the auditing purpose.
Concurrent Program AuditTrail Update Tables
After selecting the column we can run a concurrent that will enable the auditing.
System Administrator – Requests -> Run -> Single Request -> ‘AuditTrail Update Tables’ -> Submit


Testing the AuditTrail Setup
After enabling the AuditTrail it’s needed to test that everything is working properly. The test can be performed executing the following actions:
• Create a new user ‘TEST01’
• Edit the description of the user ‘TEST01’
• Assign a responsibility to the user ‘TEST01’
• Set a profile option for the user ‘TEST01’
Running the following queries, the output should contain relevant information related to what was done.

SELECT * FROM APPLSYS.FND_USER_A
SELECT * FROM APPLSYS.WF_LOCAL_USER_ROLES_A
SELECT * FROM APPLSYS.FND_PROFILE_OPTION_VALUE_A

Purging the Auditing Tables
It would be wise to create some policy establishing how often the auditing tables should be purged and where and how the data should be archived.
To Purge the auditing table it’s enough to change the ‘Group State’ of the Audit Group setting the value ‘Disable – Purge Table’
System Administrator -> Security -> AuditTrail -> Groups


Then the concurrent program ‘AuditTrail Update Tables’ should be executed again.
If you have found this post helpful don’t forget your comment ;)

Monday 29 December 2014

How to take .lst file in oracle


LST stands for Spool file (Oracle)

Log in SQL Plus



Type :-

spool echo_off_output_2.lst
SQL>@E:\xxxxx.sql
spool off

spool echo_off_output_2.lst
@D:\Suguna\MasTec\Incidents\220743\Change_datafix\afchrchk.sql
spool off

How to attach the Request Group for Responsibility in oracle apps



DECLARE
   CURSOR c1
   IS
      SELECT   fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.VERSION,
               fr.responsibility_key
        FROM   fnd_responsibility_tl frt, fnd_responsibility fr
       WHERE   UPPER (frt.responsibility_name) IN
                     ( Responsibility Name)
               AND frt.LANGUAGE = 'US'
               AND frt.application_id = fr.application_id
               AND fr.responsibility_id = frt.responsibility_id;

   l_reg_group_id   NUMBER;
BEGIN
   SELECT   request_group_id
     INTO   l_reg_group_id
     FROM   fnd_request_groups frg
    WHERE   1 = 1 AND request_group_name = (Request Group Name);

   FOR i IN c1
   LOOP
      BEGIN
         fnd_responsibility_pkg.update_row (
            x_responsibility_id           => i.responsibility_id,
            x_application_id              => i.application_id,
            x_web_host_name               => i.web_host_name,
            x_web_agent_name              => i.web_agent_name,
            x_data_group_application_id   => i.data_group_application_id,
            x_data_group_id               => i.data_group_id,
            x_menu_id                     => i.menu_id,
            x_start_date                  => i.start_date,
            x_end_date                    => SYSDATE - 1,
            x_group_application_id        => i.group_application_id,
            x_request_group_id            => l_reg_group_id,
            x_version                     => i.VERSION,
            x_responsibility_key          => i.responsibility_key,
            x_responsibility_name         => i.responsibility_name,
            x_description                 => i.description,
            x_last_update_date            => SYSDATE,
            x_last_updated_by             => -1,
            x_last_update_login           => 0
         );
         COMMIT;
         DBMS_OUTPUT.put_line (
            i.responsibility_name || ' has been updated !!!'
         );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;