user creation in oracle tips

User management:

DBA_USERS	describes all users of the database
DBA_TS_QUOTAS	describes tablespace quotas for all users
V$SESSION	lists session information for each current session

                   

                         All users need a default tablespace, this is where all objects created by the user will be stored and a temporary tablespace which is where they perform work such as sorting data during SQL execution. Make sure that you assign the tablespaces as on some systems they could end up using the system tablespace which is not a good idea.

Creating users:

Prerequisites

You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege.

Creating user syntax:

Sql> create user <username> identified by <password>

The above command creats a user with default tablespace as users and default temporary table space temp(in 10g and 11g), find the example bellow;

Sql> create user sample identified by sample;

For above user default tablespace will be default permanent tablespace of the database, temporary tablespace is TEMP and profile will be DEFAULT profile

To know database default tablespace:

SQL> select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

USERS

If we need to change database default tablespace execute following

SQL> alter database default tablespace users;

Database altered.

EXTERNALLY Clause

Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service, such as an operating system or a third-party service. In this case, Oracle Database relies on authentication by the operating system or third-party service to ensure that a specific external user has access to a specific database user.

DEFAULT TABLESPACE Clause and TEMPORARY TABLESPACE clause:

SQL>  create user sample identified by sample default tablespace test temporary tablespace temp;

User created.

if we want to check it

SQL> select default_tablespace,temporary_tablespace from dba_users where username='SAMPLE';

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

TEST                             TEMP

PROFILE clause:

Specify the profile you want to assign to the user. If you omit this clause, then Oracle Database assigns the DEFAULT profile to the user.

PASSWORD EXPIRE Clause:

Specify PASSWORD EXPIRE if you want the user's password to expire. This setting forces the user or the DBA to change the password before the user can log in to the database.

Ex:

SQL> create user sample identified by sample default tablespace test temporary tablespace temp profile test_profile password expire;

User created.

Tablespace quotas:

Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace. A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces. UNLIMITED lets the user allocate space in the tablespace without bound.

SQL> create user sample identified by sample default tablespace test temporary tablespace temp quota 50M on data;

User created.

Locking a user:

SQL> alter user sample account lock;

User altered.

Unlocking a locked user:

SQL> alter user sample account unlock;

User altered.