Thursday, October 02, 2014

User with Read only Access

CREATE USER scott_user IDENTIFIED BY scott_user 
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
-- From Role Privileges
GRANT COMPANY_RDONLY_ROLE TO scott_user;
GRANT COMPANY_CONNECT TOscott_user;

CREATE OR REPLACE TRIGGER scott_user.after_logon_trg
AFTER LOGON ON scott_user.SCHEMA
BEGIN
  DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCOTT_OWN';
END;
/