| Previous | Table of Contents | Next |
by Joe Greene
In the days when card decks stored all data, it was easy to control access—you simply locked up the card decks. Since that era, however, computer people have struggled to balance the need to let people have access to the data needed to be productive against the need to protect certain sensitive data items. The Oracle RDBMS provides some pretty sophisticated control mechanisms to enable you to provide just the right amount of access to your database. However, getting that scheme set up requires a fair amount of work by someone. In most installations, there is not a separate security administrator (or at least there is not a security administrator who gets into the actual technical work of granting this access to that person). Therefore, this assignment usually falls on the database administrator.
This chapter is designed to provide an introduction to the first set of security mechanisms that Oracle provides to control access to data. The system privileges are designed to control overall database access permissions. Examples of these are permissions to create and delete tables. Next, you explore the Oracle object privileges with which you grant a particular user the permission to read data from a particular table on the system. Finally, Chapter 14, “Roles and Grants,” ties the concepts of the two types of privilege in with the idea of roles and the actual commands that can be used to set up access for a particular user. Taken together, these three chapters enable you to control access to your Oracle database effectively.
This chapter begins with an introduction to some of the concepts and purposes behind Oracle access privileges. Then you review the first type of Oracle access privileges—those related to the system as a whole. An entire section is devoted to the powerful (and therefore dangerous) group of system privileges that I refer to as the “any” privileges. Finally, several examples of combinations of privileges that I have run across in my travels are presented. This will be a starting point for you when you start working out the privileges that you will give to your various users.
Before we jump right into system privileges, let’s go over the reason Oracle has implemented this set of privileges. The quick and simple answer is that customers wanted it and Oracle wanted to sell its database product. There was a balancing act involved with making it simple to get small, nonsecure organizations working quickly and still provide detailed control for those that need it.
This chapter presents what the privileges are and which privileges are applicable to which objects. Some are obvious. You can execute a procedure but not a table. Some are not so obvious. You may wonder why you can’t just alter a view to which you want to add one column. This chapter reviews both of these points from the point of view of the DBA.
If everyone did exactly what they were told, never made any mistakes, and could be absolutely trusted, there would not be a need for security systems in database management systems. Now back to the real world. Certain people lack the knowledge or need to create and drop database objects such as tables and indexes. Additionally, most business organizations limit who can read or update certain types of information based on confidentiality laws, financial responsibility considerations, or management preferences.
In the era where computers were not accessible by mere mortals, the locked data center, combined with controlled batch jobs for update and report output, provided adequate security. Then information systems were made available throughout the organization to increase productivity. Although the vast majority of users violated no access rules or performed no accidental deletions, there were a few painful problems that received a lot of management attention. This caused businesses to tighten their security picture.
This was difficult in the flat-file era because the operating systems at first either allowed access or didn’t. Eventually, the operating systems became smart enough to distinguish between read and write privileges, so this at least controlled those who could not perform updates. When developers received a requirement to limit access to a certain portion of the data, they could always move that data to a new file and limit the operating system write access to that file.
The trend toward database management systems created a need for the database to control access to various tables internally. This came from the fact that businesses usually did not want to have a separate operating system file for each table in the database. Therefore, control mechanisms were created to limit various types of access to the tables. In addition, because the majority of database users had no need to create database objects, certain restrictions were developed to prevent such activities.
This brings us to the era of Oracle6. In this world, there are three classes of users. The connect users are allowed to access the system (read and write as controlled by grants to the individual objects), but they cannot create any new database objects. The resource users are allowed to read and write data as controlled by individual object grants, and they can also create new tables, indexes, and so forth. Finally, the DBAs have the keys to the kingdom and can access any of the objects on the system, create new objects, and also perform all the administrative functions that are needed to keep the database operational.
This was a functional system; however, it had some severe limitations for larger databases and information systems. One of the classic large-system problems was that the operators needed to have DBA privileges to perform functions such as database startup and shutdown. This was uncomfortable because even though these were trusted individuals, they usually lacked the training to have all the power of the DBA account. With only three categories, you also had no way of qualifying various types of developers or limiting their capability of creating certain types of database objects.
| Previous | Table of Contents | Next |