Creating Users   «Prev 

Users and Schemas

Access to the database is granted to a database account known as a user. A user may exist in the database without owning any objects. However, if the user creates and owns objects in the database, those objects are part of a schema that has the same name as the database user. A schema can own any type of object in the database:
  1. tables,
  2. indexes,
  3. sequences, and
  4. views

Schema Owner

The schema owner or DBA can grant access to these objects to other database users. The user always has full privileges and control over the objects in the user's schema. When a user is created by the DBA (or by any other user with the CREATE USER system privilege), a number of other characteristics can be assigned to the user, such as which tablespaces are available to the user for creating objects, and whether the password is preexpired. You can authenticate users in the database with three methods:
  1. database authentication,
  2. operating system authentication, and
  3. network authentication.
With database authentication, the encrypted password for the user is stored in the database. In contrast, operating system authentication makes an assumption that a user who is already authenticated by an operating system connection has the same privileges as a user with the same or similar name (depending on the value of the OS_AUTHENT_PREFIX initialization parameter). Network authentication uses solutions based on Public Key Infrastructure (PKI). These network authentication methods require Oracle 11g or 12c Enterprise Edition with the Oracle Advanced Security option.

Creating a User

CREATE USER coin_admin
  IDENTIFIED BY coin_admin
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default
  PASSWORD EXPIRE
  QUOTA 5000K ON users
  QUOTA 10M on tools
  QUOTA UNLIMITED ON temp;

  1. CREATE USER coin_admin: You are creating a new user named coin_admin.
  2. IDENTIFIED BY coin_admin: The initial password for this user will be the same as the username.
  3. DEFAULT TABLESPACE users: The default tablespace for this user will be the user's tablespace.
  4. TEMPORARY TABLESPACE temp: The user's temporary tablespace will be temp.
  5. PROFILE default: The user will be given the default profile. You will learn more about profiles later in this course.
  6. QUOTA 5000K ON users: The user will be allow to use 5000 kilobytes of disk space in the user's tablespace.
  7. QUOTA 10M ON tools: The user will be allowed to use 10 megabytes of disk space in the tools tablespace.
  8. QUOTA UNLIMITED ON temp: The keyword UNLIMITED allows the user to use any amount of disk in the temp tablespace.