Users

An Oracle user account is identified by a unique user name and consists of the user's password, privileges, roles, a default login tablespace into which user created objects are automatically stored, and a default temporary tablespace where transient data associated with currently executing queries is stored. Creating a new user is equivalent to creating a new schema. The schema has the same value as the user name and offers a way of referring to objects owned (created) by the user. Users may be assigned predefined roles such as 'CONNECT' (simply allows user to connect to and access a database) or 'RESOURCE' (allows a user to create certain database objects). Only a user with the 'DBA' role may create other users. User accounts such as 'SYSTEM' and 'SYS' are automatically created during installation. These accounts are used by Database Administrators (DBA) to perform administrative functions. The SYS account's schema hosts all the data dictionary related tables.

A user should always connect to the SYS account as SYSDBA (the most privileged oracle role). The following screenshot and command shows how the SYS account may be used to view all user accounts:

Fig 5a: View User Accounts

New database users are created and managed by the Database Administrator(s). Users are associated with a set of Object level privileges and a set of system privileges. Object level privileges consist of the user's level of access (read, write etc.) to specific objects (tableA, customers table etc.) and may extend to even defining the level of access a user has to columns within a table. System privileges have something to do with a broader type of access - they define groups of objects that the user may access (all tables, all views etc.).

The following example demonstrates creation of a new user account. An account called 'ENTRYOP' with the password 'dataentry' is created.

Fig 5b: New User Creation

 A user may log into his account by issuing a 'connect <user_name>@<SID>' command at the sql> prompt. Invoking sql*plus also automatically starts the user log in dialogue. The 'disconnect' statement logs the user out of the session.

The following screenshot demonstrates account password change and account deletion. The 'CASCADE' directive causes the delete to cascade to all associated objects; that is, the associated schema is also deleted.

Fig 5c: Cascade User Deletion

It is not enough to merely create a user; the use should be assigned the appropriate privileges. First, the 'CREATE SESSION' privilege should be granted to a user; only then will he or she be allowed to log into the Oracle server. In general, the 'GRANT' directive provides a privilege to a user while the 'REVOKE' directive may be used to remove a previously granted privilege. Let us recreate the 'ENTRYOP' user and test granting and revoking the session privilege.

CREATE USER ENTRYOP IDENTIFIED BY dataentry ACCOUNT UNLOCK;
GRANT CREATE SESSION TO ENTRYOP
REVOKE CREATE SESSION TO ENTRYOP

If you execute the above code in SQL plus, you will see user creation confirmation and 'succeeded' messages for GRANT and REVOKE. Test the finally issued REVOKE command by logging in as the new user with username 'entryop' and password 'dataentry'. You will get a message of the following sort:

Fig 5d: No Session Privilege

Now, log in as SYSDBA again and run the command 'GRANT CREATE SESSION TO ENTRYOP'. You will now be able to log in as ENTRYOP. The DBA may exercise control over user accounts by granting and revoking privileges and also by locking and unlocking the account itself. Locking an account is conceptually similar to locking a house; the user cannot 'get in' until the account is unlocked. In general, the 'ALTER' keyword is used to make changes to an account (e.g. password change, switch lock/unlock status etc.) Following are the commands used to lock and unlock the ENTRYOP account. You should see 'user altered' messages after you run each of these commands:

To Lock:
ALTER USER ENTRYOP ACCOUNT LOCK;
 
To Unlock:
ALTER USER ENTRYOP ACCOUNT UNLOCK;

Oracle assigns the 'SYSTEM' tablespace to new users by default. This behavior may be changed by issuing an appropriate CREATE DATABASE command. The UNLIMITED TABLESPACE privilege will allow a user to create tables in any tablespace. Otherwise, an 'ALTER' command may be issued to change the user's default tablespace. The following commands assign ENTRYOP to the USERS tablespace and assign 5 MB space for this user's objects. You should see 'User altered' messages upon issuing these commands.

ALTER USER ENTRYOP DEFAULT TABLESPACE USERS;
ALTER USER ENTRYOP QUOTA 5M ON USERS;

The CREATE TABLE privilege allows users to create tables and indexes within their own schemas. This privilege may be given through a GRANT command and revoked through the REVOKE command. CREATE ANY TABLE is an extended privilege; it allows users to create tables in any user's schema. The create table privilege carries the table deletion privilege with it; users who are allowed to create tables may also remove them (however, the CREATE ANY TABLE does not carry the DELETE ANY TABLE privilege implicitly). You should see a 'grant succeeded' message after running the following command:

GRANT CREATE TABLE TO ENTRYOP;

Now, change the connection and log in as the new user with username 'entryop' and password 'dataentry'. Try out the following commands. They create and drop a sample table 'sampletbl' with a single column called 'somefield' in ENTRYOP's schema after the default tablespace has been set to USERS. You should see 'table Created' and 'table Dropped' messages after running each command:

CREATE TABLE sampletbl (somefield NUMBER);
DROP TABLE sampletbl;

The 'CREATE VIEW' privilege allows a user to create and drop views. Users are allowed to access data within their own tables (i.e., tables within their schema) by default. Assigning the user the SELECT ANY TABLE privilege allows the user to view the contents of any user's schema. Logout and login as SYS and issue the following commands:

GRANT CREATE VIEW TO ENTRYOP;
GRANT SELECT ANY TABLE TO ENTRYOP;

You should see 'Grant succeeded' messages after each command. Create another new user sampleuser:

CREATE USER sampleuser IDENTIFIED BY sampleuser ACCOUNT UNLOCK;
GRANT CREATE SESSION TO sampleuser;
GRANT CREATE TABLE TO sampleuser;
ALTER USER sampleuser DEFAULT TABLESPACE USERS;
ALTER USER sampleuser QUOTA 5M ON USERS;

Disconnect and connect as sampleuser and create the following table:

create table sample (myid NUMBER);

Now, connect as ENTRYOP with password dataentry. Run the command 'select count(*) from sampleuser.sample'. This command queries the database for the number of rows in the 'sample' table in the 'sampleuser' schema. You should see the following output:

Fig 5e: View Tables From Another Schema

THE INSERT ANY TABLE and DELETE ANY TABLE privileges are analogous to SELECT ANY TABLE and CREATE ANY TABLE. They allow users who are granted the appropriate privilege to modify any table within any schema and delete any table in any schema. Oracle stores user privileges in the data dictionary. A list of all privileges assigned to users may be accessed through the views USER_SYS_PRIVS and DBA_SYS_PRIVS. The former shows the privileges of the current user while the later may be used by the DBA to view privileges of any user. The following commands and output show the output of these two views. Remember that you have to be logged on as SYSTEM or SYS to access the DBA_SYS_PRIVS view.

Fig 5f: Monitoring Users and Privileges