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

A user is either

• a local user,
• an external user, or
• a global user

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

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.

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).

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

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.

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.

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

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.

Users are created with create user.

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.

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.

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