I asked support but all they stated was "Session table and view should be sufficient, we don't create any procedures. we do alter views while the app is running and alter tables on upgrades" which is far less then granting the CodeCollab user account "full access". I took a stab at what I perceived as the required grants and wanted to know from more seasoned users/dba what exactly is required.
As for your statement on Grant all privs on schema to user - this is not a valid statement. You grant rights (create, update,delete, etc) on schema objects - that is tables,views, etc.. You can "Grant all privileges to <user> which is overkill from a security perspective.
The above Grant all would give some 200 System Privileges. The user Norm is a new account that I created as a test.
GRANT UPDATE ANY CUBE DIMENSION TO NORM;
GRANT INSERT ANY MEASURE FOLDER TO NORM;
GRANT DROP ANY MEASURE FOLDER TO NORM;
GRANT CREATE MEASURE FOLDER TO NORM;
GRANT UPDATE ANY CUBE TO NORM;
GRANT CREATE ANY CUBE TO NORM;
GRANT ALTER ANY ASSEMBLY TO NORM;
GRANT CHANGE NOTIFICATION TO NORM;
GRANT ALTER ANY SQL PROFILE TO NORM;
GRANT DROP ANY SQL PROFILE TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_RULE,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT ADMINISTER DATABASE TRIGGER TO NORM;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'DEQUEUE_ANY',
GRANTEE => 'NORM',
ADMIN_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'ENQUEUE_ANY',
GRANTEE => 'NORM',
ADMIN_OPTION => FALSE);
END;
/
GRANT QUERY REWRITE TO NORM;
GRANT UNDER ANY VIEW TO NORM;
GRANT EXECUTE ANY OPERATOR TO NORM;
GRANT CREATE OPERATOR TO NORM;
GRANT ALTER ANY MATERIALIZED VIEW TO NORM;
GRANT GRANT ANY PRIVILEGE TO NORM;
GRANT ALTER PROFILE TO NORM;
GRANT ALTER DATABASE TO NORM;
GRANT AUDIT ANY TO NORM;
GRANT GRANT ANY ROLE TO NORM;
GRANT CREATE PUBLIC DATABASE LINK TO NORM;
GRANT ALTER TABLESPACE TO NORM;
GRANT CREATE TABLESPACE TO NORM;
GRANT RESTRICTED SESSION TO NORM;
GRANT ALTER SESSION TO NORM;
GRANT CREATE SESSION TO NORM;
GRANT FLASHBACK ARCHIVE ADMINISTER TO NORM;
GRANT SELECT ANY CUBE TO NORM;
GRANT SELECT ANY CUBE DIMENSION TO NORM;
GRANT INSERT ANY CUBE DIMENSION TO NORM;
GRANT EXECUTE ANY ASSEMBLY TO NORM;
GRANT CREATE ANY SQL PROFILE TO NORM;
GRANT MANAGE SCHEDULER TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT EXPORT FULL DATABASE TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_RULE_SET,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT GRANT ANY OBJECT PRIVILEGE TO NORM;
GRANT ON COMMIT REFRESH TO NORM;
GRANT CREATE ANY DIMENSION TO NORM;
GRANT EXECUTE ANY INDEXTYPE TO NORM;
GRANT DROP ANY OPERATOR TO NORM;
GRANT EXECUTE ANY TYPE TO NORM;
GRANT CREATE ANY TYPE TO NORM;
GRANT CREATE TYPE TO NORM;
GRANT CREATE ANY DIRECTORY TO NORM;
GRANT CREATE ANY MATERIALIZED VIEW TO NORM;
GRANT CREATE ANY TRIGGER TO NORM;
GRANT CREATE ROLE TO NORM;
GRANT CREATE SEQUENCE TO NORM;
GRANT DROP ANY INDEX TO NORM;
GRANT DELETE ANY TABLE TO NORM;
GRANT UPDATE ANY TABLE TO NORM;
GRANT LOCK ANY TABLE TO NORM;
GRANT CREATE TABLE TO NORM;
GRANT BECOME USER TO NORM;
GRANT UNLIMITED TABLESPACE TO NORM;
GRANT AUDIT SYSTEM TO NORM;
GRANT UPDATE ANY CUBE BUILD PROCESS TO NORM;
GRANT CREATE ANY CUBE BUILD PROCESS TO NORM;
GRANT CREATE ANY MEASURE FOLDER TO NORM;
GRANT DROP ANY CUBE DIMENSION TO NORM;
GRANT DELETE ANY CUBE DIMENSION TO NORM;
GRANT CREATE ANY CUBE DIMENSION TO NORM;
GRANT ALTER ANY MINING MODEL TO NORM;
GRANT CREATE ASSEMBLY TO NORM;
GRANT ALTER ANY EDITION TO NORM;
GRANT SELECT ANY TRANSACTION TO NORM;
GRANT CREATE ANY JOB TO NORM;
GRANT DEBUG CONNECT SESSION TO NORM;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'MANAGE_ANY',
GRANTEE => 'NORM',
ADMIN_OPTION => FALSE);
END;
/
GRANT ALTER ANY DIMENSION TO NORM;
GRANT DROP ANY INDEXTYPE TO NORM;
GRANT CREATE ANY INDEXTYPE TO NORM;
GRANT ALTER ANY LIBRARY TO NORM;
GRANT CREATE ANY LIBRARY TO NORM;
GRANT CREATE LIBRARY TO NORM;
GRANT ANALYZE ANY TO NORM;
GRANT CREATE TRIGGER TO NORM;
GRANT FORCE TRANSACTION TO NORM;
GRANT DROP ANY ROLE TO NORM;
GRANT ALTER ANY INDEX TO NORM;
GRANT CREATE ANY INDEX TO NORM;
GRANT ALTER ANY CLUSTER TO NORM;
GRANT COMMENT ANY TABLE TO NORM;
GRANT CREATE ROLLBACK SEGMENT TO NORM;
GRANT DROP TABLESPACE TO NORM;
GRANT DROP ANY CUBE BUILD PROCESS TO NORM;
GRANT CREATE CUBE BUILD PROCESS TO NORM;
GRANT DELETE ANY MEASURE FOLDER TO NORM;
GRANT CREATE ANY MINING MODEL TO NORM;
GRANT CREATE MINING MODEL TO NORM;
GRANT CREATE ANY ASSEMBLY TO NORM;
GRANT CREATE EXTERNAL JOB TO NORM;
GRANT ADMINISTER SQL TUNING SET TO NORM;
GRANT CREATE JOB TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUATION_CONTEXT,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT MERGE ANY VIEW TO NORM;
GRANT CREATE DIMENSION TO NORM;
GRANT ALTER ANY OPERATOR TO NORM;
GRANT CREATE MATERIALIZED VIEW TO NORM;
GRANT DROP PROFILE TO NORM;
GRANT EXECUTE ANY PROCEDURE TO NORM;
GRANT CREATE ANY PROCEDURE TO NORM;
GRANT CREATE PROCEDURE TO NORM;
GRANT FORCE ANY TRANSACTION TO NORM;
GRANT ALTER ANY ROLE TO NORM;
GRANT DROP ANY CLUSTER TO NORM;
GRANT SELECT ANY TABLE TO NORM;
GRANT BACKUP ANY TABLE TO NORM;
GRANT ALTER SYSTEM TO NORM;
GRANT ALTER ANY CUBE DIMENSION TO NORM;
GRANT EXECUTE ASSEMBLY TO NORM;
GRANT DROP ANY EDITION TO NORM;
GRANT READ ANY FILE GROUP TO NORM;
GRANT EXECUTE ANY PROGRAM TO NORM;
BEGIN
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
(GRANTEE_NAME => 'NORM',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/
GRANT DROP ANY CONTEXT TO NORM;
GRANT DROP ANY DIMENSION TO NORM;
GRANT ALTER ANY INDEXTYPE TO NORM;
GRANT CREATE INDEXTYPE TO NORM;
GRANT CREATE ANY OPERATOR TO NORM;
GRANT DROP ANY LIBRARY TO NORM;
GRANT ALTER ANY TYPE TO NORM;
GRANT ALTER RESOURCE COST TO NORM;
GRANT DROP ANY SEQUENCE TO NORM;
GRANT DROP ANY VIEW TO NORM;
GRANT CREATE ANY VIEW TO NORM;
GRANT DROP PUBLIC SYNONYM TO NORM;
GRANT DROP ANY SYNONYM TO NORM;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO NORM;
GRANT DROP ANY CUBE TO NORM;
GRANT DROP ANY ASSEMBLY TO NORM;
GRANT MANAGE ANY FILE GROUP TO NORM;
GRANT MANAGE FILE GROUP TO NORM;
GRANT ADMINISTER ANY SQL TUNING SET TO NORM;
GRANT ADVISOR TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT ALTER ANY OUTLINE TO NORM;
GRANT UNDER ANY TYPE TO NORM;
GRANT DROP ANY TYPE TO NORM;
GRANT DROP ANY MATERIALIZED VIEW TO NORM;
GRANT DROP ANY PROCEDURE TO NORM;
GRANT ALTER ANY PROCEDURE TO NORM;
GRANT CREATE ANY SEQUENCE TO NORM;
GRANT CREATE VIEW TO NORM;
GRANT CREATE ANY SYNONYM TO NORM;
GRANT CREATE ANY CLUSTER TO NORM;
GRANT INSERT ANY TABLE TO NORM;
GRANT DROP ANY TABLE TO NORM;
GRANT DROP USER TO NORM;
GRANT ALTER USER TO NORM;
GRANT ALTER ANY CUBE TO NORM;
GRANT CREATE CUBE TO NORM;
GRANT COMMENT ANY MINING MODEL TO NORM;
GRANT EXECUTE ANY CLASS TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT IMPORT FULL DATABASE TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_EVALUATION_CONTEXT,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT FLASHBACK ANY TABLE TO NORM;
GRANT RESUMABLE TO NORM;
GRANT DROP ANY OUTLINE TO NORM;
GRANT CREATE ANY OUTLINE TO NORM;
GRANT ALTER ANY TRIGGER TO NORM;
GRANT SELECT ANY SEQUENCE TO NORM;
GRANT CREATE SYNONYM TO NORM;
GRANT CREATE CLUSTER TO NORM;
GRANT ALTER ANY TABLE TO NORM;
GRANT CREATE ANY TABLE TO NORM;
GRANT DROP ROLLBACK SEGMENT TO NORM;
GRANT ALTER ROLLBACK SEGMENT TO NORM;
GRANT CREATE USER TO NORM;
GRANT MANAGE TABLESPACE TO NORM;
GRANT CREATE CUBE DIMENSION TO NORM;
GRANT SELECT ANY MINING MODEL TO NORM;
GRANT DROP ANY MINING MODEL TO NORM;
GRANT CREATE ANY EDITION TO NORM;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
GRANTEE => 'NORM',
GRANT_OPTION => FALSE);
END;
/
GRANT DEBUG ANY PROCEDURE TO NORM;
GRANT CREATE ANY CONTEXT TO NORM;
GRANT UNDER ANY TABLE TO NORM;
GRANT GLOBAL QUERY REWRITE TO NORM;
GRANT EXECUTE ANY LIBRARY TO NORM;
GRANT DROP ANY DIRECTORY TO NORM;
GRANT CREATE PROFILE TO NORM;
GRANT DROP ANY TRIGGER TO NORM;
GRANT DROP PUBLIC DATABASE LINK TO NORM;
GRANT CREATE DATABASE LINK TO NORM;
GRANT ALTER ANY SEQUENCE TO NORM;
GRANT CREATE PUBLIC SYNONYM TO NORM;
