SecurityHow do I…
8.1 Grant system and object privileges?
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.10 Delete audit information?
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.