Oracle database permissions
Database Administrator (sys account) privileges are required to create tablespaces and users for the data warehouse. The following table lists the various Oracle database permissions that are either required or recommended. The table also lists alternatives to granting the permissions, if available.
|Privilege||Used during||Why it is used||Alternative method|
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
The Archival/Restore privilege calls the Oracle Data pump utility through a stored procedure. These permissions are needed where:
You can grant the privilege before Archival/Restore and then revoke after Archival/Restore.
|EXECUTE ON DBMS_LOB||Application usage (For example, ETL, reports)||The Warehouse database schema has a few tables with the CLOB datatype column. Both extract, transform, and load (ETL) and reports require DBMS_LOB privileges to access and manipulate specific part of Lobs.||None.|
|SELECT ON DBA_DATA_FILES||Health and Value dashboard report||The Health and Value dashboard report query uses these tables to get details of DATA_FILES.||None.|
|SELECT ON DBA_FREE_SPACE||Health and Value dashboard report||The Health and Value dashboard report query uses these tables to get details of free space.||None.|
|GRANT UNLIMITED TABLESPACE TO BSARA_DATA,BSARA_INDEX,BSARA_ETL_STAGE, etc||Application usage (ETL)||To have enough space to load data through ETL.||None.|
|CREATE PROCEDURE||Application usage (ETL)||To create a standalone stored procedure or a call specification.||None.|