Maintaining security

7.10 Maintaining security

One should not forget the security of the data in a data warehouse. In many respects, warehouse data should probably be more secure than production data, because its value to your competitors could be enormous. Imagine if one of your users started earning spare cash by running reports against your data, or if an employee left the company with information from the data warehouse. If you are laughing at the last statement, please don't; although it is very rare, it actually does happen.

The level of security in your data warehouse will depend on what you think is appropriate for your system. Some sites may prefer to have the warehouse open for read-only queries, but restrict management tasks such as creating summary data to the administration team.

We have already seen in Chapter 2 how we can protect individual tables by specifying explicitly who may access the tables using the GRANT and REVOKE commands.

Another technique is to create a ROLE, assign privileges to that role, and then grant that role to a user. Roles can be created via SQL or by using the OEM Security Manager.

In Figure 7.49, a role called EASY_USER is created, and here we are specifying which tables in the database this user can access. Security isn't limited only to tables; it can be placed on a wide range of database objects. For those of you who do not want to use OEM, the SQL box at the bottom of Figure 7.50 shows the commands to implement this role.

Figure 7.49: Creating a role.

Figure 7.50: Allocating a role to a user.

Once the role has been defined, it can be allocated to a user, as shown in Figure 7.50. Roles are an extremely powerful feature in Oracle and can save an immense amount of time not having to allocate lots of individual privileges to a user.

Virtual private database

Applications that are deployed on the Internet and make information available to customers, suppliers, employees, or other users need to provide access control at a very fine level of granularity. An on-line banking system needs to ensure that customers can only see transactions for their accounts and not anyone else's account. A self-service human resources application may let employees see their own records and modify their marital status, address, and phone number, but not their salary. The same application may provide managers with the ability to read and modify the records of all employees who work for them.

As companies are increasing to focus on their core competencies, they may outsource other tasks, such as human resources, customer support, and payroll. When designing an application that provides hosting services, the data for each company must be kept separate and secure from each other.

Traditionally, access has been controlled at the object level. The data security policy determines which users have access to which schema objects and what types of actions are allowed for each object. For example, a user may be able to select from a table but not insert, update, or delete the rows in the table. If you wanted to allow a user access to a subset of rows in a table, a view could be created and the user granted access to the view. If you have a self-service application, where each employee in a large company has access to the rows containing his or her own personal data in the human resources database, each employee would have to have his or her own view. However, the number of views quickly becomes unwieldy. If you want to allow access to employee data for a certain group of users only through the human resources application, for example, and not for ad hoc queries, views do not give you this capability.

Another way to implement data security is with virtual private database (VPD) and fine-grained access control. VPD provides row-level security for all applications. Associating one or more security policies with a table or view creates the virtual private database. Any access to a table with an attached security policy causes the invocation of a function that implements the policy. The function returns an access condition in the form of a WHERE clause, which is appended to the user's SQL statement, thus dynamically modifying the user's data access. For example, in the EASYDW warehouse, we could allow each customer to see information about his or her own order history on line. When a customer issues SELECT * FROM PURCHASES, the function would add the customer_id to the WHERE clause, resulting in the following query.

 SELECT * FROM PURCHASES WHERE customer_id = 'AB123459' 

Often you want to control access based on some attributes about the user, such as the job code, department, location, or whether the user is a customer or partner. An application context is created to do this. Upon logging into the database, the application context is associated with the user's session. Each application can have its own application context, each having different attributes.

After creating the application context, the PL/SQL functions to implement the security policies are created. The function determines the WHERE clause to return, based on the user's application context.

The PL/SQL package DBMS_RLS is used to administer the security policies and apply them to the appropriate tables. Using this package, you can add, drop, enable, disable, and refresh the policies you create.

Many of the Oracle applications make use of VPD to provide fine-grain access control.