Required privilege

(1) CREATE USER

Log in to the database as a user with DBA role granted or as user granted with CREATE USER system privilege
Other Requirements to create users:

when the user is being created to own schema

(1) pre-create the required tablespaces that are to become the default tablespace and default temporary tablespace.
CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;

ALETR USER
———-

Examples

Changing User Identification: Example
The following statement changes the password of the user sidney (created in “Creating a Database User: Example”) second_2nd_pwd and default tablespace to the tablespace example:

ALTER USER sidney
IDENTIFIED BY second_2nd_pwd
DEFAULT TABLESPACE example;

The following statement assigns the new_profile profile (created in “Creating a Profile: Example”) to the sample user sh:

ALTER USER sh
PROFILE new_profile;

In subsequent sessions, sh is restricted by limits in the new_profile profile.

The following statement makes all roles granted directly to sh default roles, except the dw_manager role:

ALTER USER sh
DEFAULT ROLE ALL EXCEPT dw_manager;

At the beginning of sh’s next session, Oracle Database enables all roles granted directly to sh except the dw_manager role.

Changing User Authentication: Examples
The following statement changes the authentication mechanism of user app_user1 (created in “Creating a Database User: Example”):

ALTER USER app_user1 IDENTIFIED GLOBALLY AS ‘CN=tom,O=oracle,C=US’;

The following statement causes user sidney’s password to expire:

ALTER USER sidney PASSWORD EXPIRE;

If you cause a database user’s password to expire with PASSWORD EXPIRE, then the user (or the DBA) must change the password before attempting to log in to the database following the expiration. However, tools such as SQL*Plus allow the user to change the password on the first attempted login following the expiration.

Assigning a Tablespace Group: Example
The following statement assigns tbs_grp_01 (created in “Adding a Temporary Tablespace to a Tablespace Group: Example”) as the tablespace group for user sh:

ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;

Proxy Users: Examples

The following statement alters the user app_user1. The example permits the app_user1 to connect through the proxy user sh. The example also allows app_user1 to enable its warehouse_user role (created in “Creating a Role: Example”) when connected through the proxy sh:

ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;

To show basic syntax, this example uses the sample database Sales History user (sh) as the proxy. Normally a proxy user would be an application server or middle-tier entity. For information on creating the interface between an application user and a database by way of an application server, please refer to Oracle Call Interface Programmer’s Guide.
The following statement takes away the right of user app_user1 to connect through the proxy user sh:

ALTER USER app_user1 REVOKE CONNECT THROUGH sh;

The following hypothetical examples shows another method of proxy authentication:

ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD;

The following example exposes the user app_user1 to proxy use by enterprise users. The enterprise users cannot act on behalf of app_user1 until the Oracle Internet Directory administrator has granted them appropriate privileges:

ALTER USER app_user1 GRANT CONNECT THROUGH ENTERPRISE USERS;