Managing Database Users
This section describes administration tasks for managing database users on Autonomous Database.
- Create Database Users
You can create database users in Autonomous Database on Dedicated Exadata Infrastructure by connecting to the database as the ADMIN user using any SQL client tool. - Unlock or Change the ADMIN Database User Password
You change the ADMIN database user's password using the Oracle Cloud Infrastructure console. - Remove Database Users
To remove users from your database, connect to the database as the ADMIN user using any SQL client tool. - Manage Database User Privileges
Parent topic: Manage Users
Create Database Users
You can create database users in Autonomous Database on Dedicated Exadata Infrastructure by connecting to the database as the ADMIN user using any SQL client tool.
CREATE USER new_user
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name;
GRANT CREATE SESSION TO new_user;
IDENTIFIED EXTERNALLY
and IDENTIFIED BY VALUES
clauses are not supported by Autonomous Database on Dedicated Exadata Infrastructure with the CREATE USER
SQL statement.
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.
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;
Parent topic: Managing Database Users
Unlock or Change the ADMIN Database User Password
You change the ADMIN database user's password using the Oracle Cloud Infrastructure console.
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.
Parent topic: Managing Database Users
Remove Database Users
To remove users from your database, connect to the database as the ADMIN user using any SQL client tool.
This removes user_name
and the objects
owned by that user.
This removes all
user_name
objects and the data owned by user_name
is
deleted.
Parent topic: Managing Database Users
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;
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.
Parent topic: Managing Database Users