Create Oracle user for Red

RedOracleUser.sql

-- USER SQL
CREATE USER <UserName> IDENTIFIED BY <password> 
DEFAULT TABLESPACE "RED_METADATA"
TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "RESOURCE" TO <UserName> ;
ALTER USER <UserName> DEFAULT ROLE "RESOURCE";

Note: All alpha characters in the user name should be in caps


-- SYSTEM PRIVILEGES
GRANT CREATE SEQUENCE TO <UserName>;
GRANT CREATE TABLE TO <UserName>;
GRANT CREATE VIEW TO <UserName>;
GRANT SELECT ANY TABLE TO <UserName>;
GRANT CREATE SESSION TO <UserName>;
GRANT QUERY REWRITE TO <UserName>;
GRANT CREATE SNAPSHOT TO <UserName>;
GRANT ALTER SYSTEM TO <UserName>;
GRANT CREATE PROCEDURE TO <UserName>;
GRANT CREATE DATABASE LINK TO <UserName>;
GRANT ALTER SESSION TO <UserName>;


-- need these to be able run scheduler
GRANT SELECT ON sys.v_$session TO <UserName> ;
GRANT EXECUTE ON sys.dbms_lock TO  <UserName> ;

-- do this before creating metadata repository (if a repository already exists in the same db)
-- remember to run the GRANTs above again after the new repository has been created
REVOKE SELECT ANY TABLE FROM <UserName> ;


Drop User
-- Disconnect all sessions and then drop the user:
SELECT sid, serial# FROM v$session WHERE username  =  '<UserName>';

ALTER SYSTEM KILL SESSION 'sid,serial';

DROP USER <UserName> CASCADE