Security

How do I…

8.1 Grant system and object privileges?

8.2 Create a role?

8.3 Grant roles to users?

8.4 Enable and disable roles at runtime?

8.5 Determine the privileges associated with a role?

8.6 Determine a user’s access to a particular object?

8.7 Determine which users have access to a particular object?

8.8 Enable database auditing?

8.9 View audit information?

8.10 Delete audit information?

8.11 Manage passwords?

Security is a major issue in the development of multi-user Oracle applications. Advances in technology have created tools that can access Oracle databases in more sophisticated ways. With ad hoc query tools becoming easier to use, it is important to ensure that only approved applications can query and modify data. Also, the management of user accounts and roles is fundamental to the development and maintenance of multi-user database applications. Users should not share accounts, and each user account should have a password to protect the database from unauthorized access. A role helps this organization by relating privileges that can be granted to user accounts with a single command. The structure of roles within Oracle enables database security to be modeled around the organization. This chapter explores topics related to enhancing security within your Oracle applications.

8.1 Grant System and Object Privileges

As part of maintaining user accounts, it is necessary to manage both the system and object privileges. System privileges enable user accounts to manage objects and privileges, create new accounts, and export the database, among other tasks. Object privileges enable user accounts to INSERT, UPDATE, DELETE, SELECT, drop, and manipulate objects such as tables, sequences, and indexes. User accounts can be granted privileges directly or through roles. This How-To shows methods for granting system and object privileges to a user account or role.

8.2 Create a Role

A role defines a group of related privileges and assigns user accounts a collection of privileges in a single step. Instead of user accounts being granted access to each object individually, they can be granted access to a role defining the complete set of privileges. This How-To takes you through the process of creating a new role.

8.3 Grant Roles to Users

When a user account is created, it must be granted access to the database and the objects required by the application. How-To 8.1 describes how privileges can be granted directly to the user account or indirectly through database roles. This How-To takes you through the process of granting roles to user accounts.

8.4 Enable and Disable Roles at Runtime

Ad hoc query tools pose a complicated problem in an Oracle environment. It is sometimes necessary to restrict users from data used in their applications. Do you really want your payroll clerks running ad hoc queries against your payroll data with Microsoft Access? This How-To explores the process of protecting sensitive data from ad hoc query tools by enabling and disabling roles at runtime.

8.5 Determine the Privileges Associated with a Role

Granting a role to a user account gives the user account all privileges assigned to that role. This How-To covers the task of determining which system and object privileges are assigned to a role, and it provides SQL statements to determine these privileges.

8.6 Determine a User’s Access to a Particular Object

One of the many jobs of a database administrator is to determine which users have access to which objects. With proper access, a user account can INSERT, UPDATE, SELECT, or DELETE data for an object, even if the object is owned by another user account. This How-To describes how to determine the types of access, if any, that a user account has on a particular object.

8.7 Determine Which Users Have Access to a Particular Object

For any object in the database, each user account can have various privileges to modify the object or view data within the object. With proper access, a user account can ALTER, INSERT, UPDATE, SELECT, or DELETE for an object. Also, the user account can have SELECT privileges on a sequence and EXECUTE privileges on a package, procedure, or function. This How-To describes how to determine which users have access to a particular object, and their types of privileges.

8.8 Enable Database Auditing

Database auditing is a powerful method of monitoring activity within a database. Auditing can provide information on dozens of types of activities. It can monitor when a table is modified, altered, selected from, or granted to, along with a myriad of possibilities. Auditing can monitor when and how often a particular user logs in. Auditing can record every time a particular error occurs in the database, such as permission problems. This How-To describes often-used auditing options and how to enable database auditing.

8.9 View Audit Information

After database auditing has been enabled, and auditing data has been collected, it is important to know how to retrieve data from the audit trail. This How-To describes the technique to view the audit trail for the desired information of database activity.

8.10 Delete Audit Information

If database auditing has been enabled, data is entered into the audit trail each time an auditing event has transpired. Eventually, the audit data can take up significant space or might no longer be necessary to store. This How-To describes how to delete audit information.

8.11 Manage Passwords

Oracle8 provides a suite of password management and control that greatly improves the security of Oracle databases. The new capabilities ensure that passwords conform to company standards. This includes such options as forcing users to change their passwords every 30 days, locking users out of the database after a specified number of failed attempts, ensuring a password is a certain length, and other options. This How-To shows how to assign and change password enforcement to manage passwords.