Manage Database Users

Create Database Users

To create users in your database, connect to the database as the ADMIN user using any SQL client tool.

As the ADMIN user run the following SQL statements:

CREATE USER new_user IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name;
GRANT CREATE SESSION TO new_user;

This creates new_user with connect privileges. This user can now connect to Autonomous Database and run queries. To grant additional privileges to users, see Manage Database User Privileges.

Note:

The administrator needs to provide the credentials wallet to the user new_user. See About Connecting to a Dedicated Autonomous Database.

Autonomous Database requires strong passwords; the password you specify must meet the default password complexity rules.

The password must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.

Note, the password limit is shown as 60 characters in some help tooltip popups. Limit passwords to a maximum of 30 characters.
The password cannot contain the username.
The password cannot be one of the last four passwords used for the same username.
The password cannot contain the double quote (") character.
The password must not be the same password that is set less than 24 hours ago.

To unlock a database user account, connect to your database as the ADMIN user and run the following command:

ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;

Remove Database Users

To remove users from your database, connect to the database as the ADMIN user using any SQL client tool.

As the ADMIN user run the following SQL statement:
```
DROP USER user_name CASCADE;
```

This removes user_name and the objects owned by that user.

Note:

This removes all user_name objects and the data owned by user_name is deleted.

Unlock or Change the ADMIN Database User Password

You change the ADMIN database user's password using the Oracle Cloud Infrastructure console.

Note:

If you have unsuccessfully attempted to authenticate with the ADMIN database user account multiple times, it may be locked out. Changing the ADMIN database user password unlocks the account automatically.

Go to the Details page of the dedicated Autonomous Database whose ADMIN user password you want to change.

For instructions, see View Details of a Dedicated Autonomous Database.
On the Details page, select More Actions and then select Administrator Password.
On the Administrator Password page enter the new password and confirm.

The password must meet the strong password complexity criteria based on Oracle Cloud security standards. For more information on the password complexity rules see Create Database Users.
Click Update.

Manage Database User Privileges

Autonomous Database come with a predefined database role named DWROLE. This role provides the privileges necessary for most database users. Here are the privileges it grants a user:

CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
ALTER SESSION
CREATE HIERARCHY
CREATE JOB
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

To grant the DWROLE role to a database user, connect to the database as the ADMIN user using any SQL client tool and then enter this SQL statement:

GRANT DWROLE TO user;

Instead of or in addition to granting DWROLE privileges, you can grant individual privileges to users with the GRANT command.

The DWROLE role does not allocate any tablespace quota to the user. If the user is going to be adding data or other objects, you need to grant the user tablespace quota in one of these ways:

Grant the user UNLIMITED TABLESPACE privileges:
```
GRANT UNLIMITED TABLESPACE TO user;
```
Grant the user quota to tablespaces individually; for example:
```
ALTER USER user QUOTA 500M ON data;
```

Note:

Granting the 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.