Users

Users let the database know who you are and what access authorizations you have. You must have the create user privilege to create a user. The user must have the create session privilege in order for the user to connect to the database.

When a user is created, the user is given a password, a default and a temporary tablespace. The user exists but the user cannot do anything, such as log on to the database. A user must be granted privileges to log on to the database and manipulate objects.

A username can have a maximum of 30 characters or numbers and contain no spaces, commas or special characters (!@#$%&*) "The username must begin with an alphabetic character unless you surround the name in double quotation marks ("). In general, Oracle recommends that you do not use the pound or dollar sign in a user name." [Carmichael, Theriault, Viscusi]

Users automatically created by Oracle when the database is created:

User-name	Purpose	Default password
SYS	The most powerful user in the database. Owns all of the internal objects in the database (the database cannot run with out them). Do not create or delete any objects owned by SYS.	CHANGE_ON_INSTALL
SYSTEM	"The original account from which you will do most of your object creation." [Carmichael, Theriault, Viscusi]	MANAGER
INTERNAL	Only used with Server Manager, which is a program used to perform most database administration tasks	ORACLE
PUBLIC	Only has connect privilege All objects owned by PUBLIC are accessible to all users within the database	N/A

How to Create a User with SQL*Plus

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.

In the Log On screen, type SYSTEM as the user name and MANAGER as the password. Then click okay.

Check that the user you wish to create does not already exist.

Access the dba_users view by typing:

SQL> desc dba_users

"DBA_USERS contains the list of all users who can access your database." [Carmichael, Theriault, Viscusi]

Select username, default_tablespace, and temporary_tablespace

SQL> select username,default_tablespace,temporary_tablespace

from dba_users;

Check this list to make sure the user you wish to create does not exist.

Check which tablespaces are available.

Access the dba_tablespaces view by typing:

SQL> desc dba_tablespaces

"The view DBA_TABLESPACES contains information and descriptions of all the tablespaces in the database." [Carmichael, Theriault, Viscusi]

Select tablespace_name and status. The status column shows the status of the tablespace, which is ONLINE, OFFLINE, or READ-ONLY. You should only use a tablespace that is ONLINE.
SQL> select tablespace_name,status

from dba_tablespaces;

Use the CREATE_USER command. The syntax for the CREATE_USER command:

CREATE USER username IDENTIFIED BY password

DEFAULT TABLESPACE tablespace_name

TEMPORARY TABLESPACE tablespace_name

QUOTA size ON tablespace_name

PROFILE profile_name

PASSWORD EXPIRE

ACCOUNT LOCK|UNLOCK

Option	Function
DEFAULT TABLESPACE	The tablespace "in which the user creates objects." You should always use the user_data tablespace for the default tablespace.
TEMPORARY TABLESPACE	The tablespace "in which temporary objects are created for the user’s operations." You should always use the temporary_data tablespace for the temporary tablespace.
QUOTA	"The amount of physical space within a tablespace that a user can allocate for objects."[Carmichael, Theriault, Viscusi]
PROFILE	"Assigns a named profile to the user to limit usage of the database resources."
PASSWORD EXPIRE	Makes the user’s password expire when they first log in. When you use this option, you will give the user an initial password, but after the user logs in the user will have to change his password.
ACCOUNT LOCK\|UNLOCK	Allows you to lock and unlock the user’s account. When the user’s account is locked, no one can use it until it is unlocked.

An Example of creating a user

To create a user named Tom with username and password the same, with 1MB space available in the user_data tablespace and 400K space available in the temporary_tablespace

SQL> create user tom identified by tom

default tablespace user_data

temporary tablespace temporary_data

quota 1M on user_data

quota 400K on temporary_data;

User created.

To check that the quotas were defined for Tom, use the DBA_TS_QUOTAS view. This view "gives you information about each tablespace and the users that have the ability to allocate space within that tablespace." [Carmichael, Theriault, Viscusi]

SQL> select tablespace_name,username,bytes,max_bytes

from dba_ts_quotas;

This view shows that Tom was given 1MB of available space in the user_data tablespace and 400K of available space in the temporary_data tablespace. The bytes column displays how much space has been used. The max_bytes column displays show much space is available. The negative one (-1) indicates that the user has unlimited space within the tablespace.

To allow Tom to connect to the database grant him the create session privilege.

At the SQL prompt type:

SQL> grant create session to tom;

Grant succeeded.

To use the PASSWORD EXPIRE option:

Use the alter user command to add the password expire option to Tom’s account. The syntax for the alter user command:

alter user user_name option

At the SQL prompt type:

SQL> alter user tom password expire;

User altered.

Use the connect command to log into the database as TOM, whose username and password are the same. Oracle will prompt TOM for a new password after he logs in. Make the new password TOM1.

SQL> connect

Enter user-name: tom

Enter password: ***

ERROR:

ORA-28001: the account has expired

Changing password for tom

Old password: ***

New password: ****

Retype new password: ****

Connected.

SQL>

To use the ACCOUNT LOCK|UNLOCK option:

Use the connect command to log on to the database as SYSTEM, whose password is MANAGER.

Use the alter user command to add the account lock option to Tom’s account. The account lock option will lock Tom's account. The syntax for the alter user command: alter user user_name option

At the SQL prompt type:

SQL> alter user tom account lock;

User altered.

Use the connect command to log on to the database as TOM, whose password is TOM1.

SQL> connect

Enter user-name: tom

Enter password: ****

ERROR:

ORA-2800: the account is locked

Warning: You are no longer connected to Oracle

Log into the database as the SYSTEM user to unlock Tom’s account using the alter user command

SQL> connect

Enter user-name: SYSTEM

Enter password: *******

Connected.

SQL> alter user tom account unlock;

User altered.

Log on to the database as TOM, whose password is TOM1, to check if TOM can access his account.

SQL> connect

Enter user-name: TOM

Enter password: ****

Connected.

SQL>

To drop a user

Use the drop user command

The syntax for the drop user command:

drop user user_name [CASCADE]

This command drops the user from the database.

The CASCADE option drops all objects in the user’s account before dropping the user, and you must use the CASCADE option if the user has any objects in the account.

Rules to follow when creating passwords:

A password must have a minimum of four characters or numbers.

A password can have a maximum of thirty characters or numbers and contain no spaces, commas, or special characters.

"A password must not match any word on an internal list of simple words, such as welcome." (Fieldhouse)

"A password must differ from the previous password by at least three characters." (Fieldhouse)

The password command allows you to change your password. The syntax for the password command is:

password user_name

The password command may be abbreviated to passw.

The user_name "is the user whose password you want to change. Usually only database administrators (DBA's) can change passwords for other users. You do not need to supply a username if you are changing your own password." [Gennick]

An example of TOM changing his password

Log on to the database as TOM, whose password is TOM1. In this example you will change TOM's password to TOM.

At the SQL prompt type in password.

SQL> password

Changing password for TOM

Old password: ****

New password: ***

Retype new password: ***

Password changed

SQL>

What is a profile?

"A profile is a set of specific resource limits that can be assigned to an Oracle username. Profiles give the DBA an easy and efficient way to manage resource usage within the database. Some of the resources that can be limited are:

Number of concurrent sessions the user can establish (not relevant here)

Amount of CPU time available to the user’s session

Amount of I/O time available to the user’s session

Account locking after unsuccessful log-in attempts

Password expiration

Password grace period

Password reuse and complexity restrictions" (Richard Fieldhouse)

Profile options applicable to a Personal Oracle8 database:

FAILED_LOGIN_ATTEMPTS: The maximum number of times a user can try to log in to the database with an incorrect log in. If the user exceeds the maximum, then his account is locked.

PASSWORD_LIFE_TIME: The maximum number of days the user can use his password before having to change it.

PASSWORD_REUSE_TIME: The minimum number of days before a password can be reused.

PASSWORD_REUSE_MAX: The minimum number of times the user has to change his password before being able to reuse a previous password.

PASSWORD_LOCK_TIME: How long the user’s account is locked after the user has exceeded the specified maximum number of failed login attempts.

PASSWORD_GRACE_TIME: The number of days the user can continue to use his password after it has expired. During this period, a warning message displays each time the user logs in to the database.

To create a profile:

Use the create profile command. The syntax for the create profile command:

create profile profile_name limit

An example of creating a profile

Log onto SQL*Plus as the SYSTEM user, whose password is MANAGER.
In this example, you will create a profile called SAM_USER and limit the number of failed login attempts, the lifetime of the password, and the grace period.

SQL> create profile sam_user limit

failed_login_attempts 2

password_life_time 60

password_grace_time 5;

Profile created.

To assign a profile to a username that already exists:

Use the alter user command.

An example

In this example, you will assign the profile sam_user to SAM. Because the user SAM already exists, use the alter user command.

At the SQL prompt type:

SQL> alter user sam profile sam_user;

User altered.

To assign a profile to a username who does not already exist:

In this example, you will create a new user with username SALLY and password GUEST. Then you will assign the profile sam_user to SALLY.

SQL> create user sally identified by guest

default tablespace user_data

temporary tablespace temporary_data

profile sam_user;

User created.

Grant the create session role to Sally so Sally can log on to the database.

SQL> grant create session to sally;

Grant succeeded.

To check that the profile was created:

Access the dba_profiles view. This view shows the settings for each profile. At the SQL prompt type:

SQL> select * from dba_profiles

where profile='sam_user';

To check the password settings in effect for a username:

You will access the USER_PASSWORD_LIMITS view, which displays the password settings in effect for the user who is currently logged into the database.

Use the connect command to log into the database as SAM, whose password is SAM.

SQL> connect

Enter user-name: sam

Enter password: ***

Connected.

Select all columns from the user_password_limits view. This view displays the password settings in effect for SAM.

SQL> select * from user_password_limits;

To view the profile assigned to each user in the database:

Log into the database as the SYSTEM user. (password: MANAGER)

At the SQL prompt type:

SQL> select username, account_status, default_tablespace,

temporary_tablespace, profile

from dba_users

order by username;

The dba_users view displays the user’s account status, assigned default tablespace and temporary tablespace, and profile.

To drop a profile:

Use the drop profile command. The syntax for the drop profile command:

drop profile profile_name

The drop profile command drops the specified profile from the database.

"You must have DROP PROFILE system privilege." (Koch, Looney)

What is a role?

"A role is a named group of privileges granted to a user or another role." (Fieldhouse) "When a role is first created, it has no privileges associated with it." (Koch, Loney)

Why use roles rather than directly grant privileges to users?

"Rather than grant privileges to users directly, you grant privileges to a role and then grant the role to users. Now, when you add a new object, you only have to grant access to the role, and all users who are granted that role automatically inherit that access." In sum, roles make managing privileges easier.[Carmichael, Theriault, Viscusi]

A note on privileges: Oracle distinguishes between object privileges and system privileges. Object privileges allow you to manipulate data. "Object privileges include the following rights:

Look at rows and columns in your tables and views (select)
Add new rows (insert)
Update existing rows (update)
Delete information (delete)
Execute stored programs (execute)"

System privileges allow you to perform actions that affect the structure of the database. System privileges include the following rights:

Create, drop or alter an object
Create a session in the database in order to connect to the database to perform work" [Carmichael, Theriault, Viscusi]

To view information about all the roles that have been defined in the database:

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
In the Log On screen, type SYSTEM as the user name and MANAGER as the password. Then click okay.
Query the dba_roles view:

SQL> select * from dba_roles;

Oracle creates 3 standard roles when the database is created:

Role name	Privileges
CONNECT	Log on to the database Query other users’ tables that the user has been permitted to access Insert, update, and delete rows in tables belonging to other users that the user has been permitted to access
RESOURCE	Create his own tables, sequences, indexes
DBA	Has all system privileges Grant all privileges to other users Create and alter tablespaces Perform database exports and imports (backup and recovery)

To view which roles are granted to each user or role in the database:

Log on to the database as the SYSTEM user in SQL*Plus

Query the dba_role_privs view

SQL> select grantee, granted_role

from dba_role_privs

order by grantee;

The grantee column is the username or role that has the ability to use this role.

To view the privileges enabled for the current session whether granted directly or via roles

Query the session_privs view

SQL> select * from SESSION_PRIVS;
order by privilege;

To view the roles enabled for the current session

Query the session_roles view

SQL> select * from SESSION_ROLES;
order by role;

Note: The session_privs and session_roles views are available to all users.

There are two ways to create and grant roles: using SQL*Plus or the Navigator

SQL*Plus method

To create a role using SQL*Plus:

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
In the Log On screen, log on as a user with the DBA role. In this example type SAM as the user name and SAM as the password. Then click okay.
Use the create role command

The syntax for the create role command:

create role role_name [not identified|identified [by password|externally]];

An example

In this example you will create two roles named ASSIST and MANAGER.

SQL> create role assist;

Role created.

SQL>create role manager;

Role created.

An example of granting object privileges to a role using SQL*Plus:

Remember that when a role is first created, it has no privileges associated with it.

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.

In the Log On screen, log on as a user with the DBA role. In this example type SAM as the user name and SAM as the password. Then click okay.

Use the grant command to grant the select privilege to the ASSIST role.

SQL> grant select on player to assist;

The privilege to select rows from the PLAYER table will now be available to any user who is granted the ASSIST role.

Use the grant command to grant the insert privilege to the ASSIST role.

SQL> grant insert on player to assist;

Use the grant command to grant the delete and update privileges to the manager role.

SQL> grant delete, update on player to manager;

To grant a role to another role using SQL*Plus:

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
In the Log On screen, log on as a user with the DBA role. In this example type SAM as the user name and SAM as the password. Then click okay.
Use the grant command to grant the ASSIST role to the MANAGER role.

SQL> grant assist to manager;

The MANAGER role will inherit the privileges that have been granted to the ASSIST role.

To grant a role to a user using SQL*Plus:

Start SQL*Plus:
click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.

In the Log On screen, log on as a user with the DBA role. In this example type SAM as the user name and SAM as the password. Then click okay.

Use the GRANT command to grant the MANAGER role to Sally.
SQL>grant manager to sally;
Now Sally will be able to insert, update, select, and delete rows from/to the PLAYER table.
Use the connect command to log on to the database as SALLY, whose password is GUEST, to test the MANAGER role.
SQL>connect sally/guest;
Select all rows from the player table. "The table name must be preceded by the username of the table's owner. Without this, ORACLE will say that the table does not exist." [Koch, Loney] The owner of the PLAYER table is SAM.
SQL>select * from sam.player;

To revoke a role from a user or another role:

Use the revoke command. The syntax:

revoke role_name from [user|role]

An example of revoking privileges from a role

In this example, you will revoke the delete privilege from the MANAGER role.

SQL>revoke delete on player from manager;
Revoke succeeded.

Now Sally will unable to delete rows from the PLAYER table.

To drop a role:

Use the DROP ROLE command. The syntax:

drop role role_name

This command drops the specified role.

"You must have drop any role system privilege." [Koch, Loney]

The Navigator method

To create a role using the Navigator:

Open the Navigator using the following path: Start|Programs| Oracle8 Personal Edition|Oracle8 Navigator.

Click on the '+' sign to the left of the Oracle8 Personal Edition folder.

Click on the '+' sign to the left of the Projects folder.

Right click on the Soccer folder. Click New and then Role.

The New Role Properties window will display. Click on the General tab. In the Name box type SAM_ROLE. Here is where you can specifiy a password to protect the role. "This means the user would need to give a password before using the specified role." (Fieldhouse)

Click on the Role/Privilege tab. Here is where you can assign roles and privileges to the new role.

Click on the Roles radio button. This window displays all of the available roles.

Click on the CONNECT role in the Remaining window. Then click the < button, which moves the role to the Granted window.

Click on the Privileges radio button. This window displays all of the available privileges.

Click on the CREATE ANY SYNONYM privilege in the Remaining window. Then click the < button, which moves the privilege to the Granted window.

Repeat step 10 to grant INSERT ANY TABLE, SELECT ANY TABLE, and UPDATE ANY TABLE privileges.

Click on the OK button to finish creating the role.

Return To Main