Oracle7 Server Distributed Systems Volume I: Distributed Data

Contents Index Home Previous Next

Database Security in a Distributed System

Because a distributed system allows many users to access the same data, you will need some method of limiting access to certain types of data. For example, you would want employee salaries to be available only to those authorized to see them. You may also have multiple databases in your system and will need to control who, specifically, has access to the database as a whole.

Oracle allows you to limit user access on several levels through:

User Access

User authentication can be performed by identifying the user with a username/password registered with the database, by operating system authentication of the user's identity, or by network authentication.

Warning: In a distributed system, operating system authentication of the user's identity is very susceptible to security breach. Therefore, even though operating system authentication can be performed, it is strongly discouraged.

Privileges and Roles

Privileges control what a user can and cannot access or do while attached to a database. Privileges grant the right to a user to execute a particular type of SQL statement or the right to access another user's object. Oracle has two types of privileges: system privileges and object privileges.

Privileges are also defined by who they will be assigned to: the DBA or the user. For example, some DBA privileges might be:

Roles provide a way of assigning a predetermined group of privileges to users. A role groups several privileges so that they can be simultaneously granted to or revoked from users.

See the Oracle7 Server Administrator's Guide for a full explanation of privileges and roles and how they are created, altered, enabled, disabled, and dropped.

Additional Information: Server Manager can greatly simplify creating roles. See the Oracle Server Manager User's Guide for more information.

DBA Roles

There are approximately 80 different system privileges. So, in a large distributed system, it may be necessary to divide DBA responsibilities among several people. Such a division also limits potential security problems if a single DBA's account is compromised.

This can be accomplished by creating specialized DBA roles for, for example, backups, IMPORT/EXPORT, security, and so on.

The roles listed in Table 6 - 1 are automatically defined for Oracle databases. These roles are provided for backward compatibility to earlier versions of Oracle. You can grant and revoke privileges and roles to these predefined roles, as you can to any role you define.

Role Name Privileges Granted To Role
CONNECT1 ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE1,2 CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER
DBA1,3,4 All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE5 SELECT ANY TABLE, BACKUP ANY TABLE, INSERT, DELETE, AND UPDATE ON THE TABLES SYS.INCVID, SYS.INCFIL, AND SYS.INCEXP
IMP_FULL_DATABASE5 BECOME USER, WRITEDOWN6
Table 6 - 1. DBA Roles and their Privileges

1 created by SQL.BSQ

2 grantees of the RESOURCE role also receive the UNLIMITED TABLESPACE system privilege as an explicitly grant (not as part of the RESOURCE role)

3 grantees of the DBA role also receive the UNLIMITED TABLESPACE system privilege with the ADMIN OPTION as an explicit grant (not as part of the DBA role). Therefore when the DBA role is revoked, any explicit grant of UNLIMITED TABLESPACE is also revoked.

4 also includes the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles if CATEXP.SQL has been run

5 created by CATEXP.SQL

6 a Trusted ORACLE privilege only; see the Trusted ORACLE7 Server Administrator's Guide

Contents Index Home Previous Next