Method 1 - Using MFDBFH-specific roles

This method recommends that you create a number of roles that cover the distinct MFDBFH-related interactions with an Oracle database, and then assign users to the appropriate roles. Each role should contain the permissions to the functions/operations likely to be required for that role.

Note: When MFDBFH dynamically creates database tables, it grants the appropriate permissions it requires to the PUBLIC role for each table. For procedures, types, and functions, only the schema owner receives permission, unless the MFDBFH_GRANT_USER_PERMISSIONS environment variable is set; in which case, those users/roles are granted the appropriate permissions.

Install role

This role is appropriate for administrative users who are required to create and install datastores and region/cross-region databases. This role needs permissions to the following operations:

Function	Purpose
CREATE ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE/VIEW	to create all listed objects required for datastores and region/cross-region databases
CREATE SESSION	to connect to an Oracle database
CREATE USER	to create schemas for datastores and region\cross-region databases
DROP ANY TABLE	to drop schema.tables if there were any errors during the creation process
GRANT ANY OBJECT PRIVILEGE	to grant appropriate privileges on tables, data types, procedures and functions, to other users
SELECT on sys.v_$active_instances	to record and make decisions based on an Oracle RAC configuration
SELECT on sys.v_$instance	to record and make decisions based on a non-RAC Oracle configuration
SELECT on sys.v_$parameter	to record and make decisions based on the Oracle max_string_size configuration
SELECT ANY SEQUENCE	to create tables with identity columns for datastores and region/cross-region databases

Upgrade role

This role is appropriate for users required to perform datastore upgrades (using the dbfhadmin -upgrade command). This role needs permissions to the following operations:

Function	Purpose
ALTER ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE/VIEW	to modify listed objects required across datastores and region/cross-region databases
CREATE ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE/VIEW	to create listed new objects required across datastores and region/cross-region databases)
CREATE SESSION	to connect to an Oracle database
DROP ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE/VIEW	to drop listed old objects across datastores and region/cross-region databases
GRANT ANY OBJECT PRIVILEGE	(to grant appropriate privileges on new tables, data types, procedures and functions to other users
SELECT on sys.v_$active_instances	to record and make decisions based on an Oracle RAC configuration
SELECT on sys.v$instance	to record and make decisions based on a non-RAC Oracle configuration
SELECT on sys.v_$parameter	to record and make decisions based on the Oracle max_string_size configuration
SELECT ANY SEQUENCE	to create tables with identity columns for datastores and region/cross-region databases

Standard role

This role is appropriate for any user that handles data files that are stored in a database, and who are running in an environment where some of the operations are handled using a region or cross-region database. This role needs permissions to the following operations:

Tip: This role is also sufficient for those who are starting/restarting MFDBFH-enabled enterprise server regions. However, they will not be able to perform an upgrade, and so manual intervention is required if MFDBFH detects an upgrade is necessary; therefore, we recommend starting regions with a user who has both an install and upgrade role.

Function	Purpose
Table SELECT/INSERT/UPDATE/DELETE/ALTER	to maintain tables (These permissions should have been granted by the 'create' scripts for datastores and region/cross-region databases, or by the upgrade process.)
Type, stored procedure and function EXECUTE	to execute required procedures/functions (These permissions should have been granted by the 'create' scripts for datastores and region/cross-region databases, or by the upgrade process.)
GRANT ANY OBJECT PRIVILEGE	to grant appropriate privileges on new tables, data types, procedures and functions, to other users
CREATE ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE	to create all listed objects required for datastores and region/cross-region databases
DROP ANY TABLE/INDEX/SEQUENCE/TYPE/PROCEDURE	to drop listed old objects across datastores and region/cross-region databases
CREATE USER	to create schemas for datastores and region\cross-region databases
CREATE SESSION	to connect to an Oracle database
SELECT on sys.v_$active_instances	to record and make decisions based on an Oracle RAC configuration
SELECT on sys.v_$instance	to record and make decisions based on a non-RAC Oracle configuration
SELECT on sys.v_$parameter	to record and make decisions based on the Oracle max_string_size configuration
SELECT ANY SEQUENCE	to create tables with identity columns, for datastores and region/cross-region databases