Oracle Faq

Hi,

Today i am going to discuss another basic topic which is user creation and management.A database is useless if there are no users to access the database and hence user creation and management is again a part of DBA job.

By Default,when the database is created sys,system users are created .Sys as we know is granted with dba role which is administrator of the database and is the owner of all the data dictionary views  created in system tablespace which also automatically created during database creationwhile system is the owner of many inbuilt packages required for database management.

Besides these users it is necessary to create users and grant them appropiate privileges .Only a user with create user privilege can  create users.By default in sys users and imp_catalog_role are granted this privilege.In oracle 10g a user called sysman is also granted this privilege

The syntax for create user is as shown

Sql> Create user <username>

2  identified by <password> 

3 default tablespace < tablespace_name>  (If the user creates an object and does not specify tablespace name then the object will be created in his default tablespace)

4 temporary tablespace < Temp tablespace_name> (This specifies the tablespace used by users for sorting and other temporary activities which require space)

5 Quota unlimited on <tablespace_name>  (the amount of space allocated to user for object creation on a prticular tablespace)

6> password expire  (so that user can change password on first login)

7> account unlock;

8> Profile < Profile_name> (Default profile allocated unlimited resources to users)

for eg   Sql> create user pagowner identified by pagowner

                     Default tablespace USERS

                      Temporary tablespace TEMP

                       Quota 100M on USERS;

users created

         also after creating the user u need to grant him create session privilege so as to enable him to log in this can be done by  granting connect role

Sql> Grant connect to  pagowner;

Grant succeeded

Also in order to enable him to create objects it is necessary to give resource role

Sql> Grant resource to pagowner;

Grant succeeded

There are 2 basic types of privileges: system and object privileges.The object privileges are the privileges which is granted by owner of the objects to allow users to either query or perform DML,DDL operations on objects .

whereas the list of system privileges and the garntee can be obtained from dba_sys_privs view

Sql> select * from dba_sys_privs;  

The list of dictionary view which are related to roles and privileges are:

DBA_ROLES: To retrive all the roles in the database

DBA_ROLE_PRIVS:To determine all roles which are granted to users and also wheteher it is default role and is garnted with admin option or not

DBA_TAB_PRIVS: To determine all the object privileges given on the tables to the users along with the grantor.

There are other similar views which you can explore

SESSION_PRIVS

DBA_COL_PRIVS

ROLE_ROLE_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

This might be a very irrevelant post for an experienced DBA’s and Support engineers but its always good to refresh your knowledge