Manage Database User Privileges

Autonomous Transaction Processing databases come with a predefined database role named DWROLE. This role provides the common privileges for a database developer or data scientist to performance real-time analytics.

To grant DWROLE role to your developers, connect to the database as the ADMIN user using any SQL client tool.

  1. As the ADMIN user run the following SQL statement:
    GRANT DWROLE TO user;

The privileges in DWROLE are the following:

CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
ALTER SESSION
CREATE HIERARCHY
CREATE JOB
CREATE MINING MODEL
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
READ,WRITE ON directory DATA_PUMP_DIR

You can also grant individual privileges to users with the GRANT command instead of or in addition to granting DWROLE privileges. See Oracle Database SQL Language Reference.

Note:

Granting DWROLE to a user also grants UNLIMITED TABLESPACE to the user which means the user can use any amount of storage and this also overrides all explicit tablespace quotas for the user. If you want to limit the amount of storage for a user, do not grant DWROLE to that user.