Manage User Roles and Privileges on Autonomous AI Database

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

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

You can manage user roles for Autonomous AI 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 navigation icon next to Oracle Database Actions.

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

  2. Under Administration click Database Users.

  3. On the Database Users page, in the card for the user you want to modify click the Object submenu icon to open the context menu for the user, then select Edit. This shows the Edit User area with the User tab selected.

    Note: If you want to manage the user’s account settings, for example if you want to provide Web Access 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 area, 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.

    For each role, you can select 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. A new user is granted CONNECT and RESOURCE roles when Web Access is selected.

  6. Click Apply Changes.

See The Database Users Page for more information on Database Actions Database Users.

See Create Users on Autonomous AI Database with Database Actions for information on using Database Actions.

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

Autonomous AI Databases come with a predefined database role named DWROLE. This role provides the common privileges for Autonomous AI Database users. 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 Oracle SQL Developer with a Wallet (mTLS)).

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

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

      GRANT UNLIMITED TABLESPACE TO 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