10 Managing Users on Autonomous Transaction Processing

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

Create Users with Autonomous Transaction Processing

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

For example, connect using Oracle SQL Developer (see Connect with Oracle SQL Developer (earlier than Version 18.2)).

  1. As the ADMIN user run the following SQL statement:
    CREATE USER new_user IDENTIFIED BY password;
    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 User Privileges with Autonomous Transaction Processing.

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 SQL Language Reference.

See Provide SQL Developer Web Access to Database Users to add users for SQL Developer Web.

See Create Oracle Application Express Workspaces in Autonomous Transaction Processing for information on creating APEX workspaces.

See Create and Update User Accounts for Oracle Machine Learning to add user accounts for Oracle Machine Learning.

Remove Users with Autonomous Transaction Processing

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

For example, connect using Oracle SQL Developer (see Connect with Oracle SQL Developer (earlier than Version 18.2)).

  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 Administrator Account on Autonomous Transaction Processing

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

Change the Administrator Password in Autonomous Transaction Processing

From the service console, change the password for the ADMIN user by following these steps:

  1. On the Details page, from the Actions drop-down list, select Admin Password.
  2. On the Admin Password page enter the new password and confirm.
  3. Click Update.

The password for the default administrator account, ADMIN, has the same password complexity rules mentioned in the section Create Users with Autonomous Transaction Processing.

If your administrator password is locked due to too many failed login attempts, see Unlock the Administrator Account in Autonomous Transaction Processing for details on changing and unlocking the ADMIN password.

Unlock the Administrator Account in Autonomous Transaction Processing

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:

  • Sign in to your Oracle Cloud Account at cloud.oracle.com.

  • From the Oracle Cloud Infrastructure left navigation list click Autonomous Transaction Processing.

  • On the Autonomous Databases page select an Autonomous Transaction Processing instance from the links under the Name column.

See Signing in to Your Cloud Account in Getting Started with Oracle Cloud.

  1. On the Details page, from the Actions drop-down list, select Admin Password.
  2. On the Admin Password dialog box enter the new password and confirm.
  3. 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 Users with Autonomous Transaction Processing.

Manage User Privileges with Autonomous Transaction Processing

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. 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 with Oracle SQL Developer (18.2 or later)).
  2. As the ADMIN user grant DWROLE. For example, the following command grants DWROLE to the user ADBUSER:
    GRANT DWROLE TO adbuser;
  3. Grant individual privileges to users with the GRANT command instead of or in addition to granting DWROLE privileges. See Oracle 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:
    • Use DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA to explicitly grant a quota to a user. See GRANT_TABLESPACE_QUOTA Procedure for more information.

    • Grant UNLIMITED TABLESPACE privileges to a user. For example, the following command grants unlimited tablespace privileges to the user ADBUSER:

      GRANT UNLIMITED TABLESPACE TO adbuser;

      This privilege overrides any quota that was granted using DBMS_CLOUD_ADMIN.GRANT_TABLESPACE_QUOTA.

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

Create and Update User Accounts for Oracle Machine Learning

An administrator can add an existing database user account to Oracle Machine Learning or create a new user account and user credentials with the Oracle Machine Learning User Management interface.

Create User

An administrator creates a new user account and user credentials for Oracle Machine Learning in the User Management interface.

Note:

You must have the administrator role to access the Oracle Machine Learning User Management interface.

To create a user account:

  1. Select an Autonomous Transaction Processing instance and on the details page click Service Console.
  2. On the Service Console click Administration.
  3. Click Manage OML Users to open the Oracle Machine Learning User Administration page.
  4. Click Create on the Oracle Machine Learning User Administration page.
  5. In the Username field, enter a username for the account. Using the username, the user will log in to an Oracle Machine Learning instance.
  6. Enter a name in the First Name field.
  7. Enter a name in the Last Name field.
  8. In the Email Address field, enter the email ID of the user.
  9. Select the option Generate password and email account details to user. User will be required to reset the password on first sign in. to auto generate a temporary password and send an email with the account credentials to the user.
    If you select this option, you need not enter values in the Password and Confirm Password fields; the fields are grayed out.
  10. In the Password field, enter a password for the user, if you choose to create a password for the user.
    This option is disabled if you select the Generate password... option to auto generate a temporary password for the user.
  11. In the Confirm Password field, enter a password to confirm the value that you entered in the Password field.
    By doing so, you create the password for the user. The user can change the password when first logging in.
  12. Click Create.

This creates a new database user and grants the required privileges to use Oracle Machine Learning.

Note:

With a new database user, an administrator needs to issue grant commands on the database to grant table access to the new user for the tables associated with the user's Oracle Machine Learning notebooks.

Add Existing Database User Account to Oracle Machine Learning

An administrator adds an existing database user account for Oracle Machine Learning in the User Management interface.

Note:

You must have the administrator role to access the Oracle Machine Learning User Management interface.

To add an existing database user account:

  1. Select an Autonomous Transaction Processing instance and on the details page click Service Console.
  2. On the Service Console click Administration.
  3. Click Manage OML Users to add Oracle Machine Learning users.
  4. Click Show All Users to display the existing database users.

    Note:

    Initially, the Role field shows the role None for existing database users. After adding a user the role Developer is assigned to the user.
  5. Select a user. To select a user select a name in the User Name column. For example, select ANALYST1.
    Selecting the user shows the Oracle Machine Learning Edit User page.
  6. Enter a name in the First Name field. (Optional)
  7. Enter the last name of the user in the Last Name field. (Optional)
  8. In the Email Address field, enter the email ID of the user.
    Making any change on this page adds the existing database user with the required privileges as a Oracle Machine Learning user.
  9. Click Save.

This grants the required privileges to use the Oracle Machine Learning application. In Oracle Machine Learning this user can then access any tables the user has privileges to access in the database.