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.
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.
- Click the top left next to Oracle Database Actions.
This shows the Database Actions menu, including Development and Administration.
- Under Administration click Database Users.
- On the Database Users page, in the card 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 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.
- In the Edit User area, click Granted
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.
- 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.
- Click Apply Changes.
See The Database Users Page for more information on Database Actions Database Users.
See Create Users on Autonomous Database with Database Actions for information on using Database Actions.
Manage User Privileges on Autonomous Database - Connecting with a Client Tool
Autonomous Databases come with a predefined database
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
- To grant
DWROLErole, 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)).
- As the ADMIN user grant
DWROLE. For example, the following command grants
DWROLEto the user
GRANT DWROLE TO adb_user;
- Grant individual privileges to users with the
GRANTcommand instead of or in addition to granting
DWROLEprivileges. See Oracle Database SQL Language Reference.
- If a user needs to load data, do one of the
following to add the privileges required to load
Add quota to a new user with
CREATE USERor 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;
UNLIMITED TABLESPACEprivileges to a user. For example, the following command grants unlimited tablespace privileges to the user
GRANT UNLIMITED TABLESPACE TO adb_user;
UNLIMITED TABLESPACEprivilege allows a user to use all the allocated storage space. You cannot selectively revoke tablespace access from a user with the
UNLIMITED TABLESPACEprivilege. 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