René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Create user [Oracle SQL]

The statement create user creates a user.
In the most simple form, the create user statement is one of the following three:
create user alfredo identified by alfredos_secret;
create user alfredo identified externally;
create user alfredo identified globally as 'external_name';
The first one creates a local user, the second one creates an external user while the last one creates global user.

Default tablespaces

When a user is created, his default tablespace as well as his temporary tablespace can be specified.
create user          alfredo
identified by        alfredos_secret
default   tablespace ts_users
temporary tablespace ts_temp;

Locked users

A user can be created locked, that is, the user cannot connect to the database.
SQL> create user alfredo identified by passw0rd account lock;
The user is now created, he can be granted some rights, for example the right to connect to the database:
SQL> grant connect to alfredo;
Now, if the user tries to connect to the database, he will get ORA-28000:
SQL> connect alfredo/passw0rd
ORA-28000: the account is locked
The user can now be unlocked with an alter user statement:
SQL> alter user alfredo account unlock;
Which allows Alfredo to log on to the database now:
SQL> connect alfredo/passw0rd

Expiring password

A user can be created such that he needs to change his password when he logs on. This is achieved with the password expire option.
SQL> create user dilbert identified by tie password expire;
Now, Dilbert connecting:
SQL> connect dilbert/tie
ORA-28001: the password has expired

Changing password for dilbert
New password:

Assigning profiles

A user can be assigned a profile when (s)he is created.
create user berta profile appl_profile
The profile being assigned must be created

Displaying existing users

The dba_users view shows already created users.

Restrictions on passwords

The following restrictions apply to a password:
  • Its length must be between 1 and 30 characters
  • They are case insensitive
  • The only characters allowed are A-Z (a-z), 0-9, the underscore (_), the dollar sign ($), the hash symbol (#).
  • The first character must be one of A-Z or 0-9.
  • The password must not be a reserved oracle word (see v$reserved_words).

Public role

When a user is created, the role public is automatically assigned to this user. However, the role is not visible in dba_sys_privs nor session_roles.