5.8 Segmenting Authority in the Database In Chapter 7, we recommend that you use a spreadsheet approach to list the envisioned objects within an application. We tell you to identify the actions that would be permitted against each object and to group the actions together and assign them to a role. Let's look briefly here at how you might do this. For example, let's look at a piece of an application that will be used in a car dealership . The departments follow: When a new car is received, an entry is made into the database in the automobile information area detailing the car's vehicle identification number (VIN), make and model, color , wholesale price, accessories, etc., reflecting the information needed for the car's invoice. The dealership manager or sales manager might examine the car and decide to add a sunroof or other feature to improve the sales appeal . A work order will be written up and the body shop will perform the work. The car will go to the sales floor where it will be shown. A customer will come in, test-drive the vehicle, fall in love with it, and buy it. The sale will be finalized and the car delivered to the new owner. Assuming that the customer is loyal and stays in the dealership's area, the car will be returned to the dealership periodically for maintenance and repair work. Over the life of the car, several different areas of the dealership will interact with that car's records, but for different purposes. The body shop, parts department, and service department will need to be able to read the record and update it as work is performed on the car, while the sales force will only need to read it. The dealership manager might be the only person allowed to modify the price of the car. From this scenario, we can see that there are six potential actions involved with the car's record: -
Read only, with possible areas hidden -
Read and update specific areas of the record -
Read the complete record -
Create, read, and update the entire record -
Archive the record -
Delete the record from the system Since the management of security is easier and more effective with the use of roles, let's make a determination of how many roles are really needed in this situation. Since the deletion of the record will occur only once in the life of the car, a role to include this action is not necessary. Now, what do we mean by "archive the record"? We mean that the car record is saved somewhere before the record is deleted from the system. (Note that "archiving" here does not constitute normal backups performed to protect the data.) In this sense, the action of archiving the record would be performed only once. The other four action areas would be performed many times over the life of the car, so our list of actions of interest is down to four: -
Read only, with possible areas hidden -
Read and update specific areas of the record -
Read the complete record -
Create, read, and update the complete record Since the sales force and repair staff will perform the first action read only with possible areas hidden the role to create might be called the WORK_FORCE role. To prevent the work force from viewing areas of the record that are to be kept private, a view would be created and that view would be granted to the WORK_FORCE role with the SELECT privilege. The next set of actions is reserved for the department managers to perform and includes read and update to specific areas of the record. A DEPARTMENT_MANAGER role and views could be created and the SELECT and UPDATE privileges granted to the role DEPARTMENT_MANAGER. Since the administrative staff would probably be the ones to enter the record and update the cost areas, an ADMIN_ASSISTANT role could be created with the SELECT, INSERT, and UPDATE privileges assigned. Finally, a trigger could be created to ensure that specific areas of the record could not be modified except by the DEALERSHIP_MANAGER role. You could also accomplish the same thing through the use of views, grants, or triggers. We've now identified the following: -
WORK_FORCE -
DEPARTMENT_MANAGER -
ADMIN_ASSISTANT -
DEALERSHIP_MANAGER Without defining or formally creating any tables, we've outlined the roles and have made a start on tightening security for the application. You must strictly adhere to the naming conventions established in your organization's security policy as you define the roles. Since the security policy is a living document, these roles and the associated actions could now be placed in the appropriate area of the policy. (We'll say much more about security policies in Chapter 7.) With a large application, as tables are defined and created, you can place them in a matrix with the identification of who will perform what actions against them. The matrix would contain the table name , envisioned actions, and role in which those actions would be permitted. Once the tables and roles have been identified and the actions established, you might want to add a column for a notation of the backup approach appropriate for the objects involved. In this way, you help ensure that backup criteria are established on an object-by-object basis. (See Chapter 12 for more information about backups.)  | In the matrix, note that the heading of the first column, "Table Name/Role Name," is to be read as table names down and role names across. | | For the piece of the car dealership application, a matrix might look like that in Table 5.5. Table 5.5. Car Dealership Sample Matrix | Table Name/Role Name | dealership_manager | department_manager | admin_assistant | work_force | | auto_info | S, I, U, D | S, U | | | | work_order | S, I, U | S, I, U | S, I, U | S, U | | auto_parts | S, I, U, D | S, U | | S |  |
