Manage User Roles and Privileges on Autonomous Database

There are several ways to manage user privileges and roles on Autonomous Database. You can use Oracle Database Actions Database Users card or client-side tools to connect to the database to mange privileges and roles.

Autonomous Databases come with a predefined database role named DWROLE. This role provides the common privileges for Autonomous Database users. Depending on the usage requirements you may also need to grant individual privileges to users.

  • As the ADMIN user grant DWROLE. For example, the following command grants DWROLE to the user adb_user:
    GRANT DWROLE TO adb_user;
  • If a user needs to load data, do one of the following to add the privileges required to load data:
    • Add quota to a new user with CREATE USER or alter the quota for an existing user with ALTER USER. For example:
      CREATE USER sales 
           QUOTA 5M on DATA;
      ALTER USER sales 
           QUOTA 1G on DATA;
    • Grant UNLIMITED TABLESPACE privileges to a user. For example, the following command grants unlimited tablespace privileges to the user adb_user:

      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
    EXECUTE privilege on OCI PL/SQL SDK