Manage User Privileges with Autonomous Transaction Processing

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 perform real-time analytics. Depending on the usage requirements you may also need to grant individual privileges to users.

  1. To grant DWROLE role, connect to the database as ADMIN user using any SQL client tool. For example, connect using Oracle SQL Developer (see Connect with Oracle SQL Developer (18.2 or later)).
  2. As the ADMIN user grant DWROLE. For example, the following command grants DWROLE to the user ADBUSER:
    GRANT DWROLE TO adbuser;
  3. Grant individual privileges to users with the GRANT command instead of or in addition to granting DWROLE privileges. See Oracle Database SQL Language Reference.
  4. If a user needs to load data, do one of the following to add the privileges required to load data:
    • Use DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA to explicitly grant a quota to a user. See GRANT_TABLESPACE_QUOTA Procedure for more information.

    • Grant UNLIMITED TABLESPACE privileges to a user. For example, the following command grants unlimited tablespace privileges to the user ADBUSER:

      GRANT UNLIMITED TABLESPACE TO adbuser;

      This privilege overrides any quota that was granted using DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA.

    Note:

    Granting UNLIMITED TABLESPACE privilege allows a user to use all the allocated storage space. You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.

The privileges in DWROLE are the following:

CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
ALTER SESSION
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
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
EXECUTE privilege on the PL/SQL package DBMS_CLOUD