|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 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).
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
A user can be assigned (for example within the create user statement) a default object and a default temporary tablespace (with the
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.
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.
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.