You should use the least-privileged security model, and not require more access than needed.
For the DBA, we do NOT require “dba role” (aka “root" access” on a complete database server)
The process for IM/IG/IP/Jasper/SSO or most applications, for Oracle db, may be simplified to the five (5) lines below:
drop user <idm_db_user> cascade;
create user <idm_db_user> identified by <Password01>;
grant connect, resource to <idm_db_user>;
grant create table, create view, create session, create trigger, create sequence to <idm_db_user>;
grant create tablespace, drop tablespace, manage tablespace, unlimited tablespace to <idm_db_user>;
Also, regarding Oracle NEW SERVICE ID & ACCESS:
- Run twice to validate; must be able to drop and add back the new service ID.
- Create a different service ID for each solution
The IM/IP (Identity Manager/Identity Portal) solutions will auto-recreate their database tables upon restart of the J2EE server.
This activity may be monitored with the Oracle SQL Developer GUI or via 3rd party Database Tools.