There are many approaches you can use to implement security on your system. In this chapter, we’re going to show you a sample application demonstrating one such approach. We’re not going to include every detail of every step that was taken to build the original application on which this example is based. Our goal is not to teach you how to implement only one security method. Instead, we’d like to provide you with ideas you can use to build your own system.
The sample application is a credit card review and certification system. To set the stage, we’ll provide a brief overview of the system’s functionality and present a discussion of the environment and requirements of the system. In Chapter 3, when we talked about database objects, we said that the tables in the database could be used by more than one application. This is the case with the credit card system. There will be references to objects (mostly tables) shared with other applications. We’ll refer to these objects as enterprise objects and the entire database as the enterprise database. Access to each object is controlled by the privileges that you give (or don’t give) to each user.
In this chapter we’ll use these steps in describing how the application was built:
Define the functionality of the application.
Describe the portions of the security plan that pertain to the application.
Explain how to build a role-object matrix (generally referred to as “the matrix”).
Show how to build the views defined in the matrix.
Describe how to build the roles that were defined in the matrix.
Show how to assign privileges to roles and users through the GRANT command.
Explain how to use password-protected roles to implement security on the system.
Discuss the way to use password-protected roles without the users needing to know the role’s password.
As you can see from this list of steps, the key to building the sample application is to first define and then use the role-object matrix. The matrix is then used to guide you through your application development cycle so you do not miss a functional area or privilege set.
We’ll use many of the terms and definitions that have been discussed elsewhere in this book (e.g., table, view, program, etc.). If you find you’re unfamiliar with any of these terms, we suggest that you review Chapter 2 through Chapter 6.
First, let’s take a look at the credit card review and certification application to see its purpose in life. The XYZ Corporation has issued corporate credit cards to many of its employees for various reasons:
Administrative assistants can order and pay for supplies for their areas.
Consultants use their cards to cover their expenses when they travel to customer sites.
Sales personnel entertain customers and hold business meetings outside of the corporate offices.
Company purchase order personnel use their cards for purchases under a specific dollar amount.
Many other employees have cards for other reasons.
As part of the corporate system of accountability, each employee with a card must review his or her statement electronically and certify that the changes are correct. This is the same action you probably take each month when you receive your personal statement. This application allows the certification to be done electronically. Once certified as accurate, the information can be posted to the corporate accounts payable tables. A manager might also want to verify her section’s spending to ensure that her area is not going over budget. The highest-level managers might want to confirm that employees are not abusing the system. A salesman, out of town on business, may want to verify that he has not exceeded his card limit. Each of these employees will require different forms of access and different privilege levels within the system.
To accommodate all the different needs of the various employees in the system, a credit card review and certification system has been developed. Of course, there is much more to the complete credit card system than the areas we’ll discuss. In the rest of the chapter, we’ll present the general development steps for a portion of the application.
As we said at the beginning of this chapter, there are several tables the credit card system uses that are not specifically part of the credit card system but must be used by the application. Let’s look at these tables first.
For our discussion, we’ll first examine part of an enterprise database that has many public tables and views outside the credit card review and certification system. Some points of interest about the system are the following:
Most of the reference, or lookup, data in the database is available to all the users.
The use of various query tools by the users is encouraged.
The credit card users are allowed to see their own credit purchase records, as well as many enterprise tables.
Supervisors can see records of all employees they manage.
Although most of the credit card purchase data tables can be accessed only through the applications, some of them are to be accessible for query at all times. All table data updates are to be performed only through the application.
The credit card system uses the following enterprise tables:
EMPLOYEE
POSITION
EMP_POS
Various lookup tables
Alookup table , otherwise referred to as a list of values, is used to simplify data storage. Say you have a set of job titles which cover the range of job possibilities for any employee. These titles might be fairly long. Wouldn’t it be easier and take up less storage space in the database to be able to store a one or two-digit number instead of the same titles over and over again? To accomplish this, create a table we will call TITLE_LOV with two columns — a number column and a title column. Entries in the table might look like this:
If you want to store employees’ titles in the employee table, all you need to do is store the number in the table. When you want to display the title, look the title up in the lookup table. You would use a SELECT statement like the following:
SELECT title FROM title_lov WHERE title_num = 3;
The value that would be returned in this example would be “Buyer.” In the enterprise system, the SELECT privilege has been granted to public on all lookup tables, so we won’t discuss them further.
A record in the EMPLOYEE table includes the employee’s database username and an employee number. The POSITION table contains all the jobs and the organizational entity to which each job belongs. An employee can be assigned to work in more than one area (i.e., organization). For this reason, we can’t put the organization code in the employee record. Moreover, we can’t put the employee’s id in the organization record because it contains only one record for each position type, and many employees can be assigned to one position type. This relationship between the EMPLOYEE table and the POSITION table is commonly called many-to-many .
A many-to-many relationship is resolved by using another table, usually referred to as an intersection or associative table, that will hold the employee number and the organization position code to which the employee is assigned. With this type of table, there can be many entries for each employee number, each with a different organization position code. Conversely, the table can have many entries of the same organization position code, each associated with a different employee. In the enterprise system, this is the EMP_POS table in which an employee number may occur many times and a position code may appear many times. The combination, however, must be unique — it doesn’t make sense to assign an employee to the same position more than once (even though we all work hard enough to deserve the extra pay)! For example, Mary Janes is listed as both a database administrator for Department A and a database designer for Department B. Mary occasionally does work for Department C as a systems administrator. To correctly show all Mary’s different positions, you need three separate entries. Using the tables listed here, you would put Mary’s personnel information in the EMPLOYEE table. You would have three entries in the EMP_POS table, one for each position Mary performs. The entries in the EMP_POS table would be numbers: Mary’s employee number and a number to represent each position Mary holds. Finally, in the POSITION table, you would have each position available within the company and a number to represent it, just as we had in the lookup table discussed earlier.
The EMPLOYEE table includes three fields that will be used in this system:
|
Field |
Description |
|---|---|
|
emp_no |
Employee number obtained from an enterprise-wide sequence generator when a record is created |
|
emp_db_name |
Database username for this employee. If blank, no access is allowed through this application |
|
job_title |
Employee job title (clerk, manager, department head, etc.). Validated against a standard list when a record is inserted or updated |
The POSITION table contains one record for each position within the organization. Each position record specifies how many employees may be assigned to that position. For example, there are four department managers, so the max_emp field in the position record for that record has “4” in it. Application and database code ensure that no more than four employees are assigned as department managers.
The POSITION table includes the following fields that will be required by the credit card system:
|
Field |
Description |
|---|---|
|
pos_no |
Position number obtained from an enterprise-wide sequence generator when a record is created |
|
pos_type |
Symbol for type of position (DH-department head, E-employee, etc.) |
The EMP_POS table is an associative or intersection table used to resolve the many-to-many relationship we examined earlier in this section.
The EMP_POS table includes the following fields:
|
Field |
Description |
|---|---|
|
emp_no |
Foreign key to the EMPLOYEE table emp_no field |
|
pos_no |
Foreign key to the POSITION table pos_no field |
These three enterprise tables will all be involved at various times during the implementation of the application.