RelationalDBDesignRelationalDBDesign




Database Analysis   «Prev  Next»
Lesson 8 Permissions
Objective Explain the role of permissions in database design.

Role of Permissions in Database Design

In addition to creating views, relational database management systems also allow the database administrator to assign permissions to authorized users. Permissions are database tasks that a user or group of users is allowed to carry out. There are several types of permissions the administrator can grant:
  1. Select: The user can retrieve data from a table or view.
  2. Insert: The user can create new records in a table or view. The user may also be limited to inserting values into particular fields.
  3. Update: The user can modify existing values in a table or view, again for an entire table or specific columns.
  4. Delete: The user can remove existing records from a table or view.
  5. References: The user can use a field in an existing table or view a field as a foreign key in a table that the user creates. This permission may be limited to particular fields.
  6. All: The user has all of the permissions listed above.


Never grant anyone "All permissions"

You should probably never grant anyone "All permissions". You should be careful granting Delete permissions. Determining which users should receive which permissions is the goal ofrequirements analysis.
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.
The next lesson describes the role of requirements analysis in assigning permissions.

Using an Authorization Matrix

Whenever a user makes a request to the DBMS to manipulate data, the DBMS first consults the authorization matrix to determine whether the user has the right to perform the requested action. If the DBMS cannot find a row with a matching user ID and table or view identifier, then the user has no right at all to the database element. If a row with a matching user ID and table identifier exists, then the DBMS checks for the specific rights that the user has to the table or view and either permits or disallows the requested database access. Because all database access begins with at least one search of a data dictionary table, we say that relational database management systems are data dictionary driven.