Permissions and Requirements Analysis

In the first course in this series, you looked at requirements analysis in the context of initial database design culminating in the creation of a normalized (to 1NF) ER diagram.
Requirements analysis ^[1] does not stop there. It is an ongoing process, which includes determining the smallest set of permissions users need to do their job. The best way to define which permissions users need is to sit down with their supervisors and review their job descriptions. Determine what information they will need to use, what information (if any) they will need to change, and what information (if any) they will need to delete. If users need to analyze table data independently, then they may require the Reference permission.

• CRC card: Acronym for class responsibility collaborator card, which is a custom development business architecture design document consisting of a large index card that is used to document the responsibilities and collaborators of a type while architecting the business model, the requirements analysis of the application domain model, the class during software design, and the programming class comments.

There are three good reasons to include views in the design of a database:

1. As mentioned earlier, views provide a significant security mechanism by restricting users from viewing portions of a schema to which they should not have access.
2. Views can simplify the design of a database for technologically unsophisticated users.
3. Because views are stored as named queries, they can be used to store frequently used, complex queries. The queries can then be executed by using the name of the view in a simple query.

Like other structural elements in a relational database, views can be created and destroyed at any time. However, because views do not contain stored data but only specification of a query that will generate a virtual table, adding or removing view definitions has no impact on base tables or the data they contain. Removing a view will create problems only when that view is used in an application program and the program is not modified to work with a different view or base table.

1. Do your customer service reps need the All permission?
2. Do customer service reps need to look up existing customer records and orders?
3. Your customer service reps will need to look up existing records, so they need the Select permission.
4. Your customer service reps probably don't need every permission to do their job.
5. Do customer service reps need to add new customer and order records?
6. Your customer service reps will need to enter new customer information and orders, so the Insert permission is required.
7. You assigned customer service reps the following permissions: Select, Insert, and Update. These permissions are enough for your customer service reps to do their job, though you might want to limit the reps' ability to update order records.
8. Do customer service reps need the References permission?
9. Do customer service reps need the Delete permission?
10. Do customer service reps need to update existing records?
11. Do customer service reps need the Delete permission?
12. Do customer service reps need the References permission?
13. You assigned customer service reps the following permissions: Select, Insert, Update, and References. The Select, Insert, and Update permissions are all necessary, though you may want to limit the reps' ability to change existing orders. The reps won't need to create new tables based on existing data, so the References permission is not necessary. You should consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy. This concludes the Analysis.
14. Do customer service reps need the References permission?
15. You assigned customer service reps the following permissions: Select, Insert, and Delete. You should probably also assign users the Update permission so they can update customer records, though you may want to limit the reps' ability to change existing orders. You should consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy.
16. Do customer service reps need the References permission?
17. You assigned customer service reps the following permissions: Select, Insert, and References. The Select and Insert permissions are all necessary, though you may want to limit the reps' ability to change existing orders. Your reps should also have the Update permission so they can change existing customer records, though you may want to limit their ability to change orders.
    The reps won't need to create new tables based on existing data, so the References permission is not necessary.
    You should consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy.
18. You assigned customer service reps the following permissions: Select, Insert, Update, and Delete. These permissions are enough for your customer service reps to do their job, though you might want to limit the reps' ability to update order records.
    You should also consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy.
19. You assigned customer service reps the following permissions: Select, Insert, Update, Delete, and References. The Select, Insert, and Update permissions are enough for your customer service reps to do their job, though you might want to limit the reps' ability to update order records.
    You should also consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy. The reps won't need to create new tables based on existing data, so the References permission is not necessary.
    
20. You assigned customer service reps the following permissions: Select, Insert, Delete, and References. You should probably also assign users the Update permission so they can update customer records, though you may want to limit the reps' ability to change existing orders.
    You should consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy. The reps will not need to create new tables based on existing data, so the References permission is not necessary.
21. You assigned customer service reps the following permissions: Select and Insert. You should probably also assign users the Update permission so they can update customer records, though you may want to limit the reps' ability to change existing orders.
    You should consider whether the customer service staff should be able to delete existing orders or customer records, though that decision will reflect company policy. The reps won't need to create new tables based on existing data, so the References permission is not necessary.

The next lesson wraps up this module.

[1]Requirements analysis: The stage in the database design cycle when designers find out everything they can about the data the client needs to store in the database and the conditions under which that data needs to be accessed.