Skip to content

The schema owner

A schema is a related set of tables. Usually that means the complete set of tables needed for a particular model. The HR user is a good example of a schema owner: when you log into your database as HR you can see only those tables relevant to the HR model. In a later part of the module, you will also see how the schema owner can manage access to the schema by other users.

Whenever you create a schema in Oracle you should begin by setting up the user account which represents the schema owner.

Creating a new user

User management is a Database Administrator (DBA) function, so you will find the relevant options in the DBA explorer in SQL Developer. Expand the branch labelled Security, right-click on the Users option and choose Create New... from the pop-up menu. You will be presented with a tabbed dialogue like the one shown below. In the first tab, enter the username and password (1) and select the tablespaces that will be used to store the new user's data. Use SPECIES for both username and password, and select the tablespaces shown as (2) and (3) in the graphic. Tablespaces will be explained in more detail later in the module.

Create User 1

In a real multi-user environment where the data in the database may be sensitive or confidential, it will be important to ensure that the operations that a user can perform are restricted to prevent data loss or misuse. This type of security will be discussed in week 11. For the time being, we will just give this account some basic privileges. On the Granted Roles tab, check the Connect and Resource roles as shown below. This is all that is required for this exercise, so you can then click Apply to execute the user creation operation and Close when the operation completes.

Create User 2

If you now expand the Users branch in the DBA explorer you will see your new user in the list.

Before you can use SQL Developer to work with this account, you will need to set up a new database connection in the connections pane.