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
ERROR:
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
Connected.

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
ERROR:
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.


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