Personal Oracle accounts accessing APPS schema with min efford
                                                 Last update (2008-04-18 13:06:10)
                                                                                                                   Date added (2008-04-18 11:55:49)

Summary
When working with Oracle E-Business Suite it is often very common to deal with several developers working on the suite. All the application is designed to work from APPS schema, but you don't want to give the APPS password to dozens of developers.

So the solution is to:
Create personal accounts for each developer (so you can monitor them and build a secure enviroment) and have the same privileges and "view" on the application like APPS (using synonyms etc)

The steps are simple, but have a lot of work and administration ovehead. You must minimize that efford. Follow the guide how to....

1. Create the personal account (for example: KAPARELIS ) with a sox_profile
CREATE PROFILE SOX_PROFILE LIMIT
  SESSIONS_PER_USER 15
  CPU_PER_SESSION DEFAULT
  CPU_PER_CALL DEFAULT
  CONNECT_TIME DEFAULT
  IDLE_TIME 120
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL DEFAULT
  COMPOSITE_LIMIT DEFAULT
  PRIVATE_SGA DEFAULT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX 3
  PASSWORD_LOCK_TIME 2
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION Verify_Function;

CREATE USER KAPARELIS IDENTIFIED BY KAPARELIS PROFILE SOX_PROFILE;
GRANT CONNECT, RESOURCE TO KAPARELIS;
ALTER USER KAPARELIS DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
2. The schema created in above Step 1 will be given read only grants to objects in APPS. There will be cases where the grant command might fail. To monitor such failures create a table wich will capture the exceptions during Grants:
CREATE TABLE GRANTS_FAIL_APPS(object_name VARCHAR2(100), SQLERRM VARCHAR2(2000), creation_date DATE);

GRANT ALL ON SYS.GRANTS_FAIL_APPS TO APPS WITH GRANT OPTION;
GRANT SELECT ON SYS.GRANTS_FAIL_APPS TO KAPARELIS;
3. In this step we grant select on all the existing views and synonyms in APPS to KAPARELIS.
conn apps/apps;

PROMPT This can take upto 15-30 minutes
DECLARE
  v_error VARCHAR2(2000);
BEGIN

  FOR p_rec IN (SELECT * FROM   all_objects
                WHERE  owner = 'APPS'
                AND    object_type IN ('SYNONYM', 'VIEW')
                AND    object_name NOT LIKE '%_S')
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name || ' to kaparelis';
    EXCEPTION
      WHEN OTHERS THEN
        v_error := SUBSTR(SQLERRM, 1, 2000);
        INSERT INTO sys.GRANTS_FAIL_APPS (object_name, SQLERRM, creation_date)
        VALUES (p_rec.object_name, v_error, SYSDATE);
    END;
  END LOOP;
  COMMIT;
END;
/
4. Write a after logon trigger on KAPARELIS schema. The main purpose of this trigger is to alter the session to APPS schema, such that the CurrentSchema will be set to APPS for the session(whilst retaining KAPARELIS restrictions). In doing so your logon will retain the permissions of KAPARELIS schema(read_only). Howerver it will be able to reference the APPS objects with exactly the same name as does a direct connection to APPS schema.
conn apps/apps;
CREATE OR REPLACE TRIGGER KAPARELIS_APPS_TRG
  AFTER logon ON KAPARELIS.SCHEMA
DECLARE
BEGIN
  EXECUTE IMMEDIATE
          'declare begin ' ||
          'dbms_application_info.set_client_info ( 101 ); end;';
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/
5. Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that I am excluding grants for sequences. SELECT grants for views and synonyms will be provided to KAPARELIS as and when such objects are created in APPS. Please note that, all the APPS objects (views and synonyms) that existed in APPS schema prior to the implementation of this design, would have been granted read-only access to KAPARELIS in Step 2.
conn apps/apps;
CREATE OR REPLACE TRIGGER APPS_GRANTS_TRG
  AFTER CREATE ON APPS.SCHEMA
DECLARE
  l_str VARCHAR2(255);
  l_job NUMBER;
BEGIN
  IF (ora_dict_obj_type IN ('SYNONYM', 'VIEW'))
     AND (ora_dict_obj_name NOT LIKE '%_S')
  THEN
    l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to kaparelis";';
    dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
  END IF;
END;
/
Reviews
Filters
Search