9.2 Preparing the Role-Object Matrix To control the levels of access different users will have in your system, you have to identify the specific privileges for each object. Database roles will be used as part of the security system, and you must associate these privileges with specific roles.  | It does not actually matter whether you determine the roles first or the table privileges first. However, we recommend you determine the roles first since that tends to make it easier to determine the privileges required for each objects. | | 9.2.1 Review the Security Plan The security plan (introduced in Chapter 7) for your application will include the definitions of the types of access for the system. We recommend you write a security plan even if the system being designed is not complex. By creating a security plan, you document the security approach that was intended. Should all of the people involved in the project leave, there will still be a record of the thinking behind the application implementation. The security plan serves two purposes: From the security plan and from interviews with the customers, you can determine what the user grouping will be, and you will be able to establish some role names to represent those groups. Once you have that, you can continue with the role-object access matrix. 9.2.2 Role-Object Access Matrix Access definitions from the security plan are used to determine role names. You can develop a matrix showing which roles will be given which type of access to which tables. This is the part of implementing a security system that everybody hates. In every attempt to establish a security system, all the participants secretly know that specific access rights have to be determined, and everybody tries to avoid determining them because it is a tiresome job. Unfortunately, you cannot have a successful and secure system without clearly specifying the roles and access rights. Table 9.1 illustrates one approach to building the matrix; this table is based on one used by an actual organization. If this type of approach works for your system, by all means use it. The specific format is not as important as the function. The objective is to determine the specific object grants to be given and which functional roles will be given these privileges. The matrix uses S for SELECT, I for INSERT, U for UPDATE, and D for DELETE; an X indicates EXECUTE and is only used for stored PL/SQL programs. The common name for these is "program units" (a term used in Oracle Developer Forms) since they may be created as packages or stand-alone procedures. There are no rules about how to put a matrix together. Do what works for you in your own matrix. The matrix uses the following enterprise tables: - EMPLOYEE
- EMP_POS
- POSITION
| | The matrix in Table 9.1 is not complete; we show just enough of it to give you an idea of what one can look like. We've included a few tables not discussed in this chapter to show you the types of tables you might want in an application. Tables not specifically associated with the system are listed at the end of the matrix, simply to keep the matrix as small as possible and to help make it easier to read and use. Generally, tables that are publicly available are not shown. 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. | | Table 9.1. Sample (Partial) Role-Object Privilege Matrix | Table Name/Role Name | CDHOLDER | CDAPPR | CDSYSADM | CDFINMON | | CARD_HOLDER_B | S | S,I,U | S,I,U | S | | DISCREP | S,I,U | S | S | S | | DISPUTE_CODE | S | S | S,I,U | S | | DISPUTE_SUB_CODE | S | S | S,I,U | S | | ERR_LOG | | | S | | | COMMENT_LOG | S,I,U,D | S,I,U,D | S,I,U,D | S,I,U,D | | CARD_TRANS_B | S | S | S | S | | CARD_TRANS_ITEM_B [1] | S,U | S | S | S | | DEF_COST_CENTER_B [2] | S | S | S,I,U,D | S | | | | | | | | Sequences | | | | | | CARD_SEQ | S | | S | | | TEMP_CARD_NBR_SEQ | | | S | | | | | | | | | Views | | | | | | APPROVERS_V | S | S | S | S | | CARD_HOLDER_V | | | | | | DEPT_DEF_CC_V | S | S | | | | | | | | | | Program Units (EXECUTE) | | | | | | TSEC.SROLE [3] | X | | | | [1] The UPDATE privilege on CARD_TRANS_ITEM_B is granted only on columns reconcile , receive_date, reconcile_date, parent_id, parent_item, and reconciled_by_emp. [2] One row is required in DEF_COST_CENTER_B and it must have item_no = 1. [3] The EXECUTE privilege on TSEC.SROLE is granted to the CDUSER role (described later in this chapter). This role will also have the CREATE_SESSION system privilege to allow the user to log in to the database. This role will never become disabled, so EXECUTE on the program is not granted to any other role. (The program is included in the matrix for documentation purposes.) | | There is one table that does not appear in the security plan or in the role-object matrix but is vital to the functionality of the sample application. This table, APP_ROLE, holds the passwords for each role in the system. It is intentionally excluded from the documentation to ensure the security of the system, but we'll discuss it in the section "Using Password-Protected Roles." | | 9.2.3 Naming Conventions The naming convention in the security plan for this application specifies that all tables will be named to indicate their contents. We recommend that you establish a naming convention before any applications are developed. It will be too late to try to implement changes in a naming convention after the application implementation has begun. The sample application adheres to the following conventions: -
A table accessed through an application or a view has a suffix of "_B", indicating that it is a base table. -
View names have a suffix of "_V". -
Sequences have a suffix of "_SEQ". -
All stored programs are created using packaged procedures and functions. To comply with these conventions, the TSEC package is created containing the SROLE procedure even though this is the only program within the package. Once you have completed the matrix, you can begin to create the views and roles and determine the appropriate privileges that will be granted to each role or to an individual user. |
