Users and schemas

So far you have seen that there is a distinction between user accounts with administrative functions and rights and other database users. For example, you have used the SYSTEM account to create new users and to perform some administrative operations such as exporting a schema. To create a schema (ie a coherent set of tables), you have used an account such as HR or SPECIES.

The user account which owns a set of tables defines the schema: it collects the set of related tables together so that it can be treated as a single object. However, databases are designed to support multiple simultaneous users who access the same schema via an application interface. There is therefore a third category of user who does not own any tables, but accesses table owned by the schema account. This is the reason for the three standard roles in Oracle: DBA accounts such as SYSTEM are granted the DBA role, schema owners such as HR have the RESOURCE role, and other users of the HR application have the CONNECT role.

The system level privileges are not sufficient on their own however to provide access to the HR tables for the ordinary user. This level of security is delegated to the schema user account. Thus the HR user will be responsible for granting or revoking privileges on the database objects within the schema.

For example, say a new member of staff joins the HR department. The DBA will log into the database using the SYSTEM account to grant overall access a the new account with the CONNECT role and to provide a password. Then either the DBA or another administrator will log into the database as the HR user and grant access to all of the objects that the new account needs to successfully operate the HR application. The table below illustrates some of the object-level operations that could be performed.

Statement	Interpretation
GRANT SELECT ON employees TO bob;	Allow user bob to query the EMPLOYEES table
GRANT SELECT, UPDATE ON job_history TO bob;	Allow user bob to query and update the JOB_HISTORY table
GRANT ALL ON departments TO bob;	Grant all privileges on the DEPARTMENTS table to user bob
REVOKE UPDATE ON departments FROM bob;	Remove the privilege to update the DEPARTMENTS table from user bob

SQL Developer allows a user to manage the security of its objects by choosing the Grants option in the object browser as show below.

The graphic below shows the dialog used to grant privileges to the JOBS table. Note that the grantee is the user the privilege is given to; the schema owner is known as the grantor. In this example, the grantee is PUBLIC (1) which means "any user". The privileges selected from the left-hand pane can be seen in the right-hand pane (2) and these will be assigned to the selected user when the Apply button is clicked. With Grant Option (3) allows the grantee to grant access on this object to other users. This can be useful for delegating the management of parts of a large application to lower level managers, but opens up the possibility of additional security risks.

The two levels of security and the three different categories of user can be summarised in the following diagram. You should note that this is the model used by Oracle to manage access to schema objects. The mechanisms used by other database platforms such as SQL Server have variations of this arrangement.