Basics of Users and Schemas in Oracle 12c

By Chris Ruel, Michael Wessler

Users not only access data in Oracle 12c databases, but they own the objects that contain the data. The set of objects owned by a user is its schema. Not all users own objects, so schemas may be empty.

Other users can access or execute objects within a user’s schema after the schema owner grants privileges. It’s common practice to have one user own all of an application’s objects (tables, indexes, views, and so on) and then provide access to those objects to all the application users within the database. This is done via database grants, roles, and synonyms.

For example, assume you have the ACME application. You’d create a user called ACME_OWN and create all objects as ACME_OWN. Then you’d create a database role called ACME_USER and grant SELECT, UPDATE, EXECUTE for the objects in ACME_OWN’s schema to that role.

Application users would be granted the ACME_USER role so they could access the ACME_OWN’s objects. This way, one user owns the objects, but the actual database or application users access the data. This separation improves both security and manageability.

Users fall into one of two categories:

  • Application owners whose schemas contain multiple objects

  • Application users with few or no objects

The syntax for each user creation is the same, but grants and privileges for each are what separate the two categories.

Here’s the syntax for creating a user:

CREATE USER <USERNAME>
IDENTIFIED BY “<PASSWORD>”
TEMPORARY TABLESPACE <TEMPORARY TABLESPACE>
DEFAULT TABLESPACE <DEFAULT TABLSPACE>;

For username, use something descriptive (such as DATABASE TITLE_OWN) for the owner of objects for the application. If a connection pooled web user is going to access the application, a name appended with _WEB is appropriate. Normal application users should be descriptive, such as first name, last initial; an example is VICKYB.

The password for the user should have the following characteristics:

  • Be more than eight characters

  • Include numbers and special characters

  • Not be based on dictionary words

  • Use uppercase and lowercase characters

Placing the password in double quotation marks (“ ”) allows special characters without disrupting the Structured Query Language (SQL) syntax.

Two tablespaces need to be identified when creating a user: temporary and default:

  • The TEMPORARY tablespace is where temporary segments are created. TEMP is the standard.

  • The DEFAULT tablespace is where tablespace objects (such as tables or indexes) are created if you omit the TABLESPACE storage clause during the object create statement. Ideally, every table or index creation statement lists a tablespace. If a tablespace is missing, these objects go to the tablespace defined as DEFAULT. Generally, the USERS tablespace is defined as DEFAULT.

A user needs system privileges to be able to connect to the database and create objects. Granting the CREATE SESSION privilege or CONNECT role allows a user to log in to the database. Giving a user the RESOURCE role enables the user to create database objects.

In the following steps, you create a user with SQL*Plus and grant the necessary roles and privileges to connect to the database:

  1. In SQL*Plus, type the following to create a user:

    SYS@dev12c> create user acme_own
     2 identified by "acme_own2013!"
     3 temporary tablespace temp
     4 default tablespace users;
    User created.

    In this example, the user is schema owner ACME_OWN. The default tablespace is defined as USERS although the TABLESPACE storage clause is expected to specify ACME_DATA when objects are created.

  2. Grant the user CONNECT and RESOURCE roles so that the user can log in to the database and create objects:

    SYS@dev12c> grant connect to acme_own;
    Grant succeeded.
    SYS@dev12c> grant resource to acme_own;
    Grant succeeded.
  3. Create a new role:

    SYS@dev12c> create role acme_user;
    Role created.
    SYS@dev12c> grant create session to acme_user;
    Grant succeeded.

    In this example, ACME_USER is created. That user will receive object grants from the ACME_OWN account as objects are created.

  4. Grant the appropriate INSERT, UPDATE, DELETE, and EXECUTE privileges for each object to the second role.

    This lets you grant the role that has the grants to each application user. Each application user then has access to the ACME_OWN objects. This saves you from having to individually grant each user access to each object.

  5. Grant CREATE SESSION to the first role.

    When users receive the role, they can log in to the database.

You can create individual application users by using SQL*Plus. Use Enterprise Manager Database Express to create users:

  1. Choose Security→Users to get to the Create User screen.

    image0.jpg

  2. Enter the username, profile, and password.

    VICKYB has DEFAULT profile and password authentication. The password you type appears as asterisks; you have to enter it twice to ensure you don’t mistype it.

  3. Click the right arrow to go to the Tablespaces screen.

    Choose the new MY_DATA tablespace as VICKYB’s default tablespace.

    Accept the Temporary Tablespace — TEMP tablespace default.

    image1.jpg

  4. Click the right arrow to go to the next screen.

    The Privilege screen appears. On the left are system privileges or roles. Roles are denoted by a check mark. You can also see the new user with the CONNECT role.

    image2.jpg

  5. When you finish choosing roles and privileges, click OK.

    The user is created.

If you need more application users, follow these steps:

  1. From the main Security screen, select the user you want to use as a template.

  2. Click the CREATE LIKE button.

    This will take you back into the Create User wizard with the options already selected to reflect the user you are copying.

  3. Create a new user with the same roles and privileges but with a different username and password.

    At this point, you have an application schema owner account and a database role; grant object privileges to this role as you create objects. You also have an application user with a role. After the application objects are built and access has been granted to that role, the application user can access the objects.