A Typical Privilege Scheme

There probably is no such thing as a single typical privilege scheme. Instead, let’s look at several examples. The details do not matter as much as understanding the differences between the examples and the concepts behind them. If you capture this knowledge, you can create the privilege scheme that works for your environment. The keys to implementing this scheme are a sound knowledge of the Oracle privileges and an understanding of your user and application needs. Chapter 13, “Oracle System and Object Privileges,” should serve as a foundation for Oracle privilege knowledge. The understanding of your user and application needs is left to the student as an exercise.

Let’s examine three typical privilege schemes I have run across in my adventures in consulting. The first scheme is a typical online transaction processing system where different people can enter different bits of data. The second scheme is the data warehouse, where almost everyone accesses data and only a handful of accounts can actually write data. The last scheme is a sample security scheme for a development Oracle instance, where there are some developers who are trusted with more power than others.

The first is a fictitious accounting general ledger system. You research your user needs (or have the developers do this for you before you allow them to transition the application to production), and determine that the following rules apply to this system:

•  You have clerks who can enter transactions. They are not allowed to modify any data or control the lists of valid accounts or legal values. If they make a mistake, they have to make a correcting entry.

•  You have supervisors who can enter transactions and modify the lists of valid accounts and legal values. Not even supervisors can modify the data in the general ledger itself.

•  Data is downloaded nightly from electronic data interfaces with other vendors and customers. This data needs to be entered automatically by nightly batch processing routines. Again, this processing adds new records, it does not allow existing data to be updated or deleted.

Many of you may work in similar environments. Perhaps you are wondering why a few obvious considerations for security have not been mentioned. Perhaps you have some personal opinions as to how these things should be set up based on problems you have run across in the past. These are valid points to consider. Businesses are not homogenous. Every organization adapts its business processes to its particular business area, corporate culture, region, and country. A good application adapts to the business needs. These security rules are the part of the application that ensures that data is properly accessed and should be thought of in that light.

Based on the rules previously described, I would create the following roles in this database:

•  DBA

•  gen_led (an object owner account not associated with a person)

•  clerk

•  supervisor

•  EDI

Once the roles are established, it is time to grant system and object privileges to these roles. For simplicity, let’s group all the application tables into two groups—transactions and valid values. I have found that for transaction processing systems, as opposed to data warehouses and development environments, the main goal of security is to control who can do what with various types of data (insert, update, select, and delete). Of course, this would all have to be reviewed with users because they usually do not tell you all of the rules in your initial discussions. However, from the data I have presented, I would construct a role scheme as follows (see Figure 14.1).

•  DBA—The standard privileges granted to the default DBA role in Oracle.

•  gen_led—An object owner account. Only the DBA or project manager has access to this account to add new tables, modify existing tables, and provide grants to these database objects. These are the privileges for this account:

•  CREATE SESSION (an easy one to forget, but is needed)

•  CREATE TABLE

•  CREATE PROCEDURE

•  CREATE PUBLIC SYNONYM

•  DROP PUBLIC SYNONYM

•  CREATE SEQUENCE

•  CREATE SYNONYM

•  CREATE TABLE

•  CREATE TRIGGER

•  CREATE VIEW

•  unlimited tablespace (possibly, if used by the DBA)

•  clerk—The first line of users of the system. Their job is defined to enter data. For control reasons, you do not overwrite existing data (update or delete). Instead, you make a correcting entry that shows an audit trail. They are not permitted to modify the valid values tables, only the transaction tables. The following is a good scheme for the clerk role (or users if you have version 6):

•  SELECT, INSERT on transaction tables

•  SELECT on valid values tables

•  CREATE SESSION

•  supervisor—The supervisors in the accounting general ledger group are the highest level of users who interact with this system. In many locations they are allowed to modify transaction data; however, in this instance, they cannot because having a detailed audit trail of all entries made (even bad ones) is important for financial control. The supervisors are allowed to modify the valid value data (including adding new rows, modifying existing rows, and deleting rows).

•  CREATE SESSION

•  SELECT, INSERT on transaction tables

•  SELECT, INSERT, UPDATE, DELETE on valid values tables

•  EDI—This is a fictitious account used to record nightly data downloads from and prepare data uploads to other companies. Notice that even though these uploads and downloads are done through batch scripts run at night, the CREATE SESSION privilege is still needed to connect to the Oracle instance. Note that the following scheme is almost identical to the clerk role. A separate role is used for this radically different function to deal better with changes in privileges that may come up in the future (for example, users allowed to modify certain tables). This may be a consideration when you plan out your security scheme.

•  CREATE SESSION

•  SELECT, INSERT on transaction tables

•  SELECT on valid values tables

Figure 14.1.  Sample transaction processing system privilege scheme.