The Oracle7 and Oracle8 privilege scheme was enhanced to enable a much greater degree of control over the privileges assigned to individuals. It broke up the three types of system privilege in Oracle6 (connect, resource, and DBA) into 78 specific privileges. If, for example, your organization allows operators to perform database startups, shutdowns, and backups, you have the fine control to grant just these privileges to the operators. Figure 13.1 illustrates this concept. Operators do not have the DBA’s privilege of deleting any data table in the system, for example.

Figure 13.1.  Comparison of Oracle6, 7, and 8 system privileges.

With this fine level of control came the burden of administering all these privileges over a large number of users. That is where the Oracle7 and Oracle8 concept of roles comes into play. The example given in the previous section showed the set of privileges required by computer operators. In most organizations, the DBA grants privileges based upon an individual’s function within that organization (computer operator, developer on the sales data warehouse project, manager of general ledger accounting, and so forth). Therefore, it is useful to be able to say that all people who have a certain job function have a common set of privileges. You assign a certain set of privileges to the role and then assign individuals to that role.

The next concept that ties in with system privileges is where the security is going to be implemented. In many traditional mainframe systems, the security was built into the applications. This enabled each organization to implement very specific data access rules for each application. However, this scheme required a lot of testing and maintenance of the security mechanisms that were put into place. Oracle’s concept has been that you buy most of the security mechanisms as part of the RDBMS product. You may have to do some administration, as is the case of creating roles and granting access to the users, but this is much less complex than writing and testing the basic security software. Only in a few specific cases that I have run across (mostly in areas that involve money, such as banks) is there a need to add locally-developed software to enhance the basic security mechanisms.

This becomes especially important as greater access is provided to the database, especially through client/server mechanisms. It is the classic tradeoff. You need to protect your data, but the data is useless if people cannot access it. The moment you provide general-purpose access tools to your users, such as SQL*Plus or Excel with a link to the Oracle database, you have given the users the means to bypass your application security (see Figure 13.2). The users can connect to the database with whatever privileges you assign them. They can then access or update data using tools that are designed to allow them to do whatever they want. Therefore, it is important to develop a sound security scheme for databases that provide this type of access. Some solutions to this problem will be found in Chapter 14, after the discussion of the security mechanisms is complete.

Figure 13.2.  Database versus application security.

It is important to understand that the access privileges comprise only half of the Oracle database security picture. These privileges control what you can do to the database as a whole. The other set of privileges, the object privileges, provide a fine level of control as to what a user can do with an individual database object (tables, views, and so forth). Not all objects are directly controllable. Indexes, for example, are used automatically whenever users have permission to access their associated tables. Figure 13.3 illustrates the relationship between these two types of Oracle privileges. You need the correct system privileges to create the objects within the Oracle database and tablespaces. You also need privileges to manipulate the structure of existing database objects. Finally, to access the objects for read, write, or execution, you need to have the correct object privileges given to you by the object owner.

Figure 13.3.  Oracle system and object privileges.