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.

PrivilegeUsed duringWhy it is usedAlternative method

CREATE ANY DIRECTORY

DROP ANY DIRECTORY

EXP_FULL_DATABASE

IMP_FULL_DATABASE

Archiving

Restore Utility

The Archival/Restore privilege calls the Oracle Data pump utility through a stored procedure. These permissions are needed where:

  • Logical directories are created
  • Data pump creates archived data files in the directory

None.

You can grant the privilege before Archival/Restore and then revoke after Archival/Restore.

EXECUTE ON DBMS_LOBApplication 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_FILESHealth and Value dashboard reportThe Health and Value dashboard report query uses these tables to get details of DATA_FILES.None.
SELECT ON DBA_FREE_SPACEHealth and Value dashboard reportThe 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, etcApplication usage (ETL)To have enough space to load data through ETL.None.
CREATE PROCEDUREApplication usage (ETL) To create a standalone stored procedure or a call specification.None.
Was this page helpful? Yes No Submitting... Thank you

Comments