-- The following SQL will create a new "report only" account for use by EFT in Oracle.
-- 
-- You will need to edit the following text in the script as you see fit:
-- <SYSTEM_PASSWORD>     - change this to the SYSTEM user account password
-- <EFT_AUDIT_USERNAME>  - change this to the username for the EFT audit user account created during EFT installation
-- <EFT_AUDIT_PASSWORD>  - change this to the password for the EFT audit user account created during EFT installation
-- <EFT_REPORT_USERNAME> - change this to the desired username for the new EFT report user account
-- <EFT_REPORT_PASSWORD> - change this to the desired password for the new EFT report user account
--
-- This script may be run by either pasting it into the Oracle SQL Command Line interface, or by using the command
-- @C:\OracleReportUser.sql within the SQL Command Line interface (adjusting the file path as necessary)


-- Connect using the SYSTEM account
CONNECT SYSTEM/<SYSTEM_PASSWORD>

-- Create the new EFT report user database account
CREATE USER <EFT_REPORT_USERNAME> IDENTIFIED BY <EFT_REPORT_PASSWORD> DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS TEMPORARY TABLESPACE temp QUOTA 5M ON system;

-- Grant the privilege so the new account can connect
GRANT CREATE SESSION TO <EFT_REPORT_USERNAME>;

-- Create a trigger that executes when the new EFT report user logs in to his 
-- schema that automatically switches him over to use the EFT audit user's schema.
-- Note that this trigger is created in the EFT report user's schema.
CREATE OR REPLACE TRIGGER <EFT_REPORT_USERNAME>.set_default_schema
AFTER LOGON ON <EFT_REPORT_USERNAME>.schema
BEGIN
  EXECUTE IMMEDIATE 'alter session set current_schema=<EFT_AUDIT_USERNAME>';
END;
/

-- Connect as the original EFT audit account created by the EFT installer
CONNECT <EFT_AUDIT_USERNAME>/<EFT_AUDIT_PASSWORD>;

-- Grant SELECT privilege on all the tables available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT table_name FROM user_tables )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||i.table_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/

-- Grant SELECT privilege on all the views available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT view_name FROM user_views )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||i.view_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/

-- Grant SELECT privilege on all the synonyms available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT synonym_name FROM user_synonyms )
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||i.synonym_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/

-- Grant EXECUTE privilege on all the packages available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type='PACKAGE' )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/

-- Grant EXECUTE privilege on all the functions available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type='FUNCTION' )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/

-- Grant EXECUTE privilege on all the procedures available to the EFT audit user to the new EFT report user 
BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type='PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO <EFT_REPORT_USERNAME>';
END LOOP;
END;
/


-- Now test by connecting as the new EFT report user
CONNECT <EFT_REPORT_USERNAME>/<EFT_REPORT_PASSWORD>;

-- Ensure the active schema is the EFT audit users schema
SELECT sys_context( 'userenv', 'current_schema' ) FROM DUAL;

-- Test to see if the report user can access a table
DESC tbl_AdminActions;