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 User Management or client-side tools to connect to the database to mange privileges and roles.

Topics

• Manage Users and User Roles on Autonomous Database - Connecting with Database Actions

• Manage User Privileges on Autonomous Database - Connecting with a Client Tool

Manage Users and User Roles on Autonomous Database - Connecting with Database Actions

You can manage user roles for Autonomous Database users with Oracle Database Actions. The same steps also let you modify account settings for a user.

First, access Database Actions as the ADMIN user. See Access Database Actions as ADMIN for more information.

1. Click the top left next to Oracle Database Actions.

   This shows the Database Actions menu, including Actions and Administration.

2. Click Administration and then select User Management.
3. On the User Management page, in the All Users area for the user you want to modify click the to open the context menu for the user, then select Edit.

   This shows the Edit User dialog.

   Note:

   If you want to manage the user's account settings, for example if you want to REST enable the user to provide access to Database Actions, or if you want to lock the user's account, you can do this from the User tab.
4. In the Edit User dialog, click Granted Roles.

   This displays the Granted Roles tab with a list of available roles and selection boxes. For each role, you can check Granted to grant the role, Admin to permit the user to grant the role to other users, and Default to use the default settings for Granted and Admin.

5. Select the roles you want to grant to the user.

   For example, select CONNECT and DWROLE.

   
   Description of the illustration adb_databaseactions_granted_roles.png
6. Click Apply Changes.

   Database Actions shows a confirmation dialog.

7. Click OK.

See The User Management Page for more information on Database Actions User Management.

See Create Users on Autonomous Database - Connecting with Database Actions for information on using Database Actions to create Users.

Manage User Privileges on Autonomous Database - Connecting with a Client Tool

Autonomous 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 adb_user:

   GRANT DWROLE TO adb_user;

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 adb_user:

     GRANT UNLIMITED TABLESPACE TO adb_user;

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