5 Managing Database Users

This section describes administration tasks for managing database users on Autonomous Transaction Processing.

Create Database Users

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

  1. 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 Transaction Processing 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 Connecting to Autonomous Transaction Processing.

Autonomous Transaction Processing 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.

The database user accounts in Autonomous Transaction Processing are locked for 24 hours after ten (10) failed login attempts for security purposes. To unlock an account, connect to your database as the ADMIN user and run the following command:

ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;

For more information about the ALTER USER command, see Oracle Database SQL Language Reference.

Remove Database Users

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

  1. 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.

Manage the ADMIN Database User

You can change the administrator user password and when locked unlock the administrator user account on Autonomous Transaction Processing.

Change the ADMIN Database User Password

From the Oracle Cloud Infrastructure console, change the password for the ADMIN user by following these steps:

  1. Sign in to your Oracle Cloud Account at cloud.oracle.com.
    See Signing in to Your Cloud Account in Getting Started with Oracle Cloud.
  2. From the Oracle Cloud Infrastructure left navigation list click Autonomous Transaction Processing.
  3. On the Autonomous Databases page select an Autonomous Transaction Processing instance from the links under the Name column.
  4. On the Details page, from the Actions drop-down list, select Admin Password.
  5. On the Admin Password page enter the new password and confirm.
  6. Click Update.

The password for the default administrator account, ADMIN, has the same password complexity rules mentioned in the section Create Database Users.

If your administrator password is locked due to too many failed login attempts, see Unlock the ADMIN Database User Account for details on changing and unlocking the ADMIN password.

Unlock the ADMIN Database User Account

Like other user accounts the ADMIN user account gets locked for 24 hours after 10 failed login attempts.

If you cannot wait for 24 hours before the ADMIN user account is automatically unlocked or if you cannot remember the ADMIN user password, you need to change the ADMIN password. Use the following steps to change the ADMIN password and unlock the account:

  1. Sign in to your Oracle Cloud Account at cloud.oracle.com.
    See Signing in to Your Cloud Account in Getting Started with Oracle Cloud.
  2. From the Oracle Cloud Infrastructure left navigation list click Autonomous Transaction Processing.
  3. On the Autonomous Databases page select an Autonomous Transaction Processing instance from the links under the Name column.
  4. On the Details page, from the Actions drop-down list, select Admin Password.
  5. On the Admin Password dialog box enter the new password and confirm.
  6. Click Update.

This operation also unlocks the ADMIN account if it was locked.

Note:

The password for the default administrator account, ADMIN, has the same password complexity rules mentioned in the section Create Database Users.

Manage Database User Privileges

Autonomous Transaction Processing databases come with a predefined database role named DWROLE. This role provides the common privileges for a database developer or data scientist to performance real-time analytics.

To grant DWROLE role to your developers, connect to the database as the ADMIN user using any SQL client tool.

  1. As the ADMIN user run the following SQL statement:
    GRANT DWROLE TO user;

The privileges in DWROLE are the following:

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

You can also grant individual privileges to users with the GRANT command instead of or in addition to granting DWROLE privileges. See Oracle Database SQL Language Reference.

Note:

Granting DWROLE to a user also grants UNLIMITED TABLESPACE to the user which means the user can use any amount of storage and this also overrides all explicit tablespace quotas for the user. If you want to limit the amount of storage for a user, do not grant DWROLE to that user.