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.
Manage Users and User Roles on Autonomous Database - Connecting with SQL Developer Web
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 Actions and Administration.
- Click Administration and then select User Management.
- 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 SQL Developer Web (Database Actions), or if you want to lock the user's account, you can do this from the User tab.
- In the Edit User dialog, 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.
- Click Apply Changes.
Database Actions shows a confirmation dialog.
- Click OK.
See The User Management Page for more information on Database Actions User Management.
See Create Users on Autonomous Database - Connecting with SQL Developer Web for information on using SQL Developer Web to create Users.
Manage User Privileges on Autonomous Database - Connecting with a Client Tool
Autonomous Database comes with a predefined database
DWROLE. This role
provides the common privileges for the data warehouse
developer or for a data scientist to perform
real-time analytics. Depending on the usage
requirements you may also need to grant individual
privileges to users.
- To grant
DWROLErole, 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)).
- As the ADMIN user grant
DWROLE. For example, the following command grants
DWROLEto the user
GRANT DWROLE TO adbuser;
- 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
DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTAto explicitly grant a quota to a user. See GRANT_TABLESPACE_QUOTA Procedure for more information.
UNLIMITED TABLESPACEprivileges to a user. For example, the following command grants unlimited tablespace privileges to the user
GRANT UNLIMITED TABLESPACE TO adbuser;
This privilege overrides any quota that was granted using
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