-- Sample script that shows the permissions necessary for the Trust Protection Platform
-- to work with an Oracle Synonym User. This script needs to be run as two different users
-- please read the script carefully before running.
--
-- Please consult with your DBA to determine the best way to set this up in your 
-- environment.  

-- =========================== To create a non-owner DB user ===========================
-- As System or DBA user
-- =====================================================================================

-- Create Synonym User
create user DIRECTOR_RW identified by password;   --Replace with a more secure password
grant create session to DIRECTOR_RW;
grant create synonym to DIRECTOR_RW;
alter user DIRECTOR_RW quota unlimited on VED; 
commit;
/

-- Grant Access to Tables
grant select, delete, update, insert on DIRECTOR.CONFIG_ATTRIBUTES to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_CLASSES to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_CONTAINS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_LOCKS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_OBJECTS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_RIGHTS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_OBJECT_RIGHTS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_OBJECT_RELS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_POLICIES to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_RELATIONS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_REVISIONS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.DISCOVERY_JOBS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.DISCOVERY_RESULTS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.LOG to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.RIGHTS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.STORE_ACCESS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.STORE_ASSOCIATIONS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.STORE_VAULT to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_ENVVAR to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_ENVVAR_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_GROUP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_GROUP_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_IDENTITY to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_IDENTITY_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_MACHINE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_MANUFACTURER to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_NETWORK to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_NETWORK_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_OS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_PRODUCT to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_PRODUCT_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_ASSOCIATION to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_ASSOCIATION_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_ENVVAR to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_GROUP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_IDENTITY to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_PRODUCT to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CLIENT_USER_STATUS to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIGOBJECTRELSLIST_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIGOBJECTRELS_DELETE_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIGOBJECTRELS_INSERT_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.APPLICABLERIGHTS_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.CONFIG_ENTRIES_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.POLICYCLASSATTRIBUTES_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.POLICYASSIGNMENTS_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.EPOLICYASSIGNMENTS_TEMP to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_USER_SESSION to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_ACCOUNT to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_KEY to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_KEY_ARCHIVE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_KEY_POLICY_VIOLATION to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_KEY_STORAGE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_TRUST_CACHE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_TRUST_CACHE_ATTRIBUTE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_TRUST_CACHE_DEVICE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_QUEUE_ITEM to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.SSH_TRUST_ROTATION_STAGE to DIRECTOR_RW;
grant select, delete, update, insert on DIRECTOR.ssh_encrypted_private_key to DIRECTOR_RW;

-- Grant Access to Sequences
grant select on DIRECTOR.CONFIG_LOCKS_ID_SEQ to DIRECTOR_RW;
grant select on DIRECTOR.CONFIG_CLASSES_ID to DIRECTOR_RW;
grant select on DIRECTOR.DISCOVERY_JOBS_ID_SEQ to DIRECTOR_RW;
grant select on DIRECTOR.DISCOVERY_RESULTS_ID_SEQ to DIRECTOR_RW;
grant select on DIRECTOR.STORE_SEED to DIRECTOR_RW;
grant select on DIRECTOR.CONFIG_OBJECTS_ID to DIRECTOR_RW;
grant select on DIRECTOR.CONFIG_RIGHTS_ID to DIRECTOR_RW;

-- Grant Execute to Procedures
grant execute on DIRECTOR.RefreshConfigObjectRels to DIRECTOR_RW;
commit;
/

-- =====================================================================================
-- As DIRECTOR_RW  (synonym user)
-- =====================================================================================

-- Create synonyms for tables
create synonym CONFIG_ATTRIBUTES for DIRECTOR.CONFIG_ATTRIBUTES ;
create synonym CONFIG_CLASSES for DIRECTOR.CONFIG_CLASSES ;
create synonym CONFIG_CONTAINS for DIRECTOR.CONFIG_CONTAINS ;
create synonym CONFIG_LOCKS for DIRECTOR.CONFIG_LOCKS ;
create synonym CONFIG_OBJECTS for DIRECTOR.CONFIG_OBJECTS ;
create synonym CONFIG_RIGHTS for DIRECTOR.CONFIG_RIGHTS ;
create synonym CONFIG_OBJECT_RIGHTS for DIRECTOR.CONFIG_OBJECT_RIGHTS ;
create synonym CONFIG_OBJECT_RELS for DIRECTOR.CONFIG_OBJECT_RELS ;
create synonym CONFIG_POLICIES for DIRECTOR.CONFIG_POLICIES ;
create synonym CONFIG_RELATIONS for DIRECTOR.CONFIG_RELATIONS ;
create synonym CONFIG_REVISIONS for DIRECTOR.CONFIG_REVISIONS ;
create synonym DISCOVERY_JOBS for DIRECTOR.DISCOVERY_JOBS ;
create synonym DISCOVERY_RESULTS for DIRECTOR.DISCOVERY_RESULTS ;
create synonym DISCOVERY_SURVEY for DIRECTOR.DISCOVERY_SURVEY ;
create synonym DISCOVERY_WORK for DIRECTOR.DISCOVERY_WORK ;
create synonym LOG for DIRECTOR.LOG ;
create synonym RIGHTS for DIRECTOR.RIGHTS ;
create synonym STORE_ACCESS for DIRECTOR.STORE_ACCESS ;
create synonym STORE_ASSOCIATIONS for DIRECTOR.STORE_ASSOCIATIONS ;
create synonym STORE_VAULT for DIRECTOR.STORE_VAULT ;
create synonym CLIENT_ENVVAR for DIRECTOR.CLIENT_ENVVAR;
create synonym CLIENT_ENVVAR_TEMP for DIRECTOR.CLIENT_ENVVAR_TEMP;
create synonym CLIENT_GROUP for DIRECTOR.CLIENT_GROUP;
create synonym CLIENT_GROUP_TEMP for DIRECTOR.CLIENT_GROUP_TEMP;
create synonym CLIENT_IDENTITY for DIRECTOR.CLIENT_IDENTITY;
create synonym CLIENT_IDENTITY_TEMP for DIRECTOR.CLIENT_IDENTITY_TEMP;
create synonym CLIENT_MACHINE for DIRECTOR.CLIENT_MACHINE;
create synonym CLIENT_MANUFACTURER for DIRECTOR.CLIENT_MANUFACTURER;
create synonym CLIENT_NETWORK for DIRECTOR.CLIENT_NETWORK;
create synonym CLIENT_NETWORK_TEMP for DIRECTOR.CLIENT_NETWORK_TEMP;
create synonym CLIENT_OS for DIRECTOR.CLIENT_OS;
create synonym CLIENT_PRODUCT for DIRECTOR.CLIENT_PRODUCT;
create synonym CLIENT_PRODUCT_TEMP for DIRECTOR.CLIENT_PRODUCT_TEMP;
create synonym CLIENT_USER for DIRECTOR.CLIENT_USER;
create synonym CLIENT_USER_ASSOCIATION for DIRECTOR.CLIENT_USER_ASSOCIATION;
create synonym CLIENT_USER_ASSOCIATION_TEMP for DIRECTOR.CLIENT_USER_ASSOCIATION_TEMP;
create synonym CLIENT_USER_ENVVAR for DIRECTOR.CLIENT_USER_ENVVAR;
create synonym CLIENT_USER_GROUP for DIRECTOR.CLIENT_USER_GROUP;
create synonym CLIENT_USER_IDENTITY for DIRECTOR.CLIENT_USER_IDENTITY;
create synonym CLIENT_USER_PRODUCT for DIRECTOR.CLIENT_USER_PRODUCT;
create synonym CLIENT_USER_STATUS for DIRECTOR.CLIENT_USER_STATUS;
create synonym CONFIGOBJECTRELSLIST_TEMP for DIRECTOR.CONFIGOBJECTRELSLIST_TEMP;
create synonym CONFIGOBJECTRELS_DELETE_TEMP for DIRECTOR.CONFIGOBJECTRELS_DELETE_TEMP;
create synonym CONFIGOBJECTRELS_INSERT_TEMP for DIRECTOR.CONFIGOBJECTRELS_INSERT_TEMP;
create synonym APPLICABLERIGHTS_TEMP for DIRECTOR.APPLICABLERIGHTS_TEMP;
create synonym CONFIG_ENTRIES_TEMP for DIRECTOR.CONFIG_ENTRIES_TEMP;
create synonym POLICYCLASSATTRIBUTES_TEMP for DIRECTOR.POLICYCLASSATTRIBUTES_TEMP;
create synonym POLICYASSIGNMENTS_TEMP for DIRECTOR.POLICYASSIGNMENTS_TEMP;
create synonym EPOLICYASSIGNMENTS_TEMP for DIRECTOR.EPOLICYASSIGNMENTS_TEMP;
create synonym SSH_USER_SESSION for DIRECTOR.SSH_USER_SESSION;
create synonym SSH_ACCOUNT for DIRECTOR.SSH_ACCOUNT;
create synonym SSH_KEY for DIRECTOR.SSH_KEY;
create synonym SSH_KEY_ARCHIVE for DIRECTOR.SSH_KEY_ARCHIVE;
create synonym SSH_KEY_POLICY_VIOLATION for DIRECTOR.SSH_KEY_POLICY_VIOLATION;
create synonym SSH_KEY_STORAGE for DIRECTOR.SSH_KEY_STORAGE;
create synonym SSH_TRUST_CACHE for DIRECTOR.SSH_TRUST_CACHE;
create synonym SSH_TRUST_CACHE_ATTRIBUTE for DIRECTOR.SSH_TRUST_CACHE_ATTRIBUTE;
create synonym SSH_TRUST_CACHE_DEVICE for DIRECTOR.SSH_TRUST_CACHE_DEVICE;
create synonym SSH_QUEUE_ITEM for DIRECTOR.SSH_QUEUE_ITEM;
create synonym SSH_TRUST_ROTATION_STAGE for DIRECTOR.SSH_TRUST_ROTATION_STAGE;
create synonym ssh_encrypted_private_key for DIRECTOR.ssh_encrypted_private_key;

-- Create synonyms for sequences
create synonym CONFIG_LOCKS_ID_SEQ for DIRECTOR.CONFIG_LOCKS_ID_SEQ;
create synonym CONFIG_CLASSES_ID for DIRECTOR.CONFIG_CLASSES_ID;
create synonym DISCOVERY_JOBS_ID_SEQ for DIRECTOR.DISCOVERY_JOBS_ID_SEQ;
create synonym DISCOVERY_RESULTS_ID_SEQ for DIRECTOR.DISCOVERY_RESULTS_ID_SEQ;
create synonym STORE_SEED for DIRECTOR.STORE_SEED;
create synonym CONFIG_OBJECTS_ID for DIRECTOR.CONFIG_OBJECTS_ID;
create synonym CONFIG_RIGHTS_ID for DIRECTOR.CONFIG_RIGHTS_ID;

-- Create synonyms for Procedures
create synonym RefreshConfigObjectRels for DIRECTOR.RefreshConfigObjectRels;
commit;
/

-- =====================================================================================
-- As System or DBA user
-- =====================================================================================
revoke create synonym from DIRECTOR_RW;
commit;
/