How do you protect your database against unauthorized access? If you use Oracle Database XE, you have a number of native security features on your site, as you'll learn in this ten-part series. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
If you’ve already begun creating end user applications in your PHP and Oracle Database XE environment, you’ve probably revisited the age-old question: do you enforce security at the database or in your PHP application? In Chapter 14, you explored many authentication methods, one of which includes storing your authentication information in a database table. However, you must also protect the table that has the authentication information. Using Oracle Database XE’s native security features has the key advantage of preventing unauthorized access regardless of the application you use to retrieve and modify data in the database, whether it be a PHP application, a Microsoft Access application using ODBC, or a dial-in user with SQL*Plus.
In this chapter, we take your security awareness a step further by explaining how to secure your database’s information assets by using the authentication and authorization methods available natively within Oracle Database XE. In addition to covering both authentication and authorization in great detail, we show you how to track user activity once the user has logged into the database by leveraging the many different levels of auditing available in Oracle Database XE.
Security Terminology Overview
To protect one of the most vital assets of a company—its data—DBAs must be keenly aware of how Oracle can protect corporate data from unauthorized access and how to use the different tools they have at their disposal. The Oracle-provided tools and mechanisms fall into three broad categories: authentication, authorization, and auditing.
Authentication includes methods used to identify who is accessing the database, ensuring that you are who you say you are, regardless of the resources you are requesting from the database. Even if you are merely trying to access the schedule of upcoming campus events, it is important that you identify yourself correctly to the database. If, for example, the PHP-based database application presents customized content based on the user account, you want to make sure you get the campus event menu for your branch office in Troy, Michigan, and not the one for the home office in Schaumburg, Illinois.
Authorization provides access to various objects in the database once you are authenticated by the database. Some users may be authorized to run a report against the daily sales table; some users may be developers and therefore need to create tables and reports, whereas others may only be allowed to see the daily campus event menu. Some users may never log in at all, but their schema may own a number of tables for a particular application, such as payroll or accounts receivable. Additional authorization methods are provided for database administrators, due to the extreme power that a database administrator has. Because a DBA can shut down and start up a database, Oracle provides an additional level of authorization.
Authorization goes well beyond simple access to a table or a report; it also includes the rights to use system resources in the database as well as privileges to perform certain actions in the database. A given database user might only be allowed to use 15 seconds of CPU time per session, or can only be idle for 5 minutes before he or she is disconnected from the database. You may grant another database user the privilege to create or drop tables in any other user’s schema, but not to create synonyms or view data dictionary tables. Fine-grained access control (FGAC) gives the DBA more control over how your users access database objects. For example, standard object privileges will either give a user access to an entire row of a table or not at all; using FGAC, a DBA can create a policy implemented by a stored procedure that restricts access based on time of day, where the request originates, which column of the table is being accessed, or all three.
Auditing in an Oracle database encompasses a number of different levels of monitoring in the database. At a high level, auditing can record both successful and unsuccessful attempts to log in, access an object, or perform an action. As of Oracle9i, not only can fine-grained auditing (FGA) record what objects are accessed, but also what columns of a table are accessed when an insert, an update, or a delete is being performed on the data in the column. FGA is to auditing what FGAC is to standard authorization: more precise control and information about the objects being accessed or actions being performed.
DBAs must use auditing judiciously so as not to be overwhelmed by audit records or create too much overhead by implementing continuous auditing. On the flip side, auditing can help to protect company assets by monitoring who is using what resource at what time and how often, as well as whether the access is successful or not. Therefore, auditing is another tool that the DBA should be using on a continuous basis to monitor the security health of the database.