Database permissions
Database Administrator privileges (sys account privileges) are required to create tablespaces and users for the data warehouse. Only a sys user can grant dbms_lob permission to a user and this permission is required for a data warehouse user. The following table lists the various Oracle database permissions that are required. 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 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:
| None. 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. |
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. |