René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Users in Oracle

In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database.
The objects a user owns are collectively called >schema. A schema, on its part, is always bound to exactly one user. Because there is obviously a 1 to 1 relationship between a user and a schema, these two terms are often used interchangeable.
In order to find out what users are created on the database, one can use dba_users

Types of users

A user is either
  • a local user,
  • an external user, or
  • a global user

Local users

A local user needs a password to log on to the database.

External users

An external user, unlike a local user, doesn't need a password to log on to the database, instead, an external service (such as the operating system) authenticates the user when (s)he logs on the database.

Global users

A global user, like an external user, doesn't need a password to log on to the database, instead, (s)he is authenticated by an enterprise directory service (such as X.509).

Database rights (Privileges)

Users can be assigned rights what they're allowed to do in a database and what not. These rights are called privileges. See Users, Roles and Privileges

Default tablespaces

A user can be assigned (for example within the create user statement) a default object and a default temporary tablespace (with the default tablespace and temporary tablespace clauses). These settings will overwrite default tablespaces that were specified for the database.

Default object tablespace

If a user creates an object (such as a table or an index) without explicitely specifying the tablespace in which it goes, Oracle will place the object in the default object tablespace.

Default temporary tablespace

If a user needs a temporary segment for an operation (such as a sort), Oracle will place the data in his default temporary tablespace.

Quotas on tablespaces

Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota...
Use dba_ts_quotas to find out a user's quota on a tablespace as well as how much he has already occupied.

Creation of users

Users are created with create user.

Passwords

A user needs a password to create a session.
The password is stated in the identified by password in the create user statement. In order to change the password, alter user SOME_USER identified by NEW_PASSWORD can be used. Alternatively, the password can also be changed with the SQL*Plus command password.
It's possible to create password limits with profiles.

Profiles

A user can be assigned a profile which limits the resources for a user or assigns password limists to a user.
See also on profiles.

Recycle bin

Each user has also his/her own recycle bin where dropped objects go. This makes it possible to recover from accidental drop statements.