|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Create user [Oracle SQL]
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.
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;
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.
A user can be created such that he needs to change his password when he logs on. This is achieved with the
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:
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: