Roles are actually rather easy. Assume for now that the TSEC package (discussed later in this chapter) has already been created. We will create a set of login roles with minimal privileges. For each role listed in the matrix, we create a set of functional roles with passwords. The statements for the functional roles are followed by an INSERT into the APP_ROLES table (whose purpose is described in the section "Using Password-Protected Roles"). Finally, we grant the object privileges to the functional roles.
| || |
The APP_ROLES table is not listed in the matrix for security reasons. It is not accessed directly by the users they know nothing about it. However, this table is an integral part of the security approach and will be discussed later in this chapter.
Figure 9.1 shows two users: sue has been granted the cdholder role and thus has access to the application as a valid card holder; cathy has not been granted any of the available application roles and cannot see any application data.
Figure 9.1. Levels of access control
In Figure 9.2, we see the steps involved when the user sue attempts to connect to the database. The database validates that sue has the appropriate privileges granted through the CDHOLDER role to interact with specific tables.
Figure 9.2. Row access control by application
doc Create one or more login roles. These are not password protected and will be the user's only default role. Note that execute privilege is granted on the package, and not the package.procedure. # CREATE ROLE cduser; GRANT create session TO cduser; GRANT execute ON tsec TO cduser; CREATE ROLE cdappruser; GRANT create session TO cdappruser; GRANT execute ON TSEC TO cdappruser; CREATE ROLE cdsysadmuser; GRANT create session TO cdsysadmuser; GRANT execute ON tsec TO cdsysadmuser; CREATE ROLE cdfinmonuser; GRANT create session TO cdfinmONuser; GRANT execute ON tsec TO cdsysadmuser; doc Create the functional roles with passwords. Also insert a row into the app_roles table for each functional, passworded role that is created. # -- create a general role for the view access. CREATE ROLE cdgenaccess; -- functional roles. CREATE ROLE cdholder IDENTIFIED BY apw_4u; INSERT INTO app_roles VALUES ('cdholder', 'orapgm', 'apw_4u', 'splash', null); CREATE ROLE cdappr IDENTIFIED BY apw_4u; INSERT INTO app_roles VALUES ('cdappr', 'orapgm', 'apw_4u', 'splash', null); etc.
All the passwords can be the same, although from a strict security perspective they should be different. These passwords will never be given to the users and will not be needed by the administrative users who must have these roles enabled at all times. The password is saved in clear, unencrypted form because it will be needed when the application runs the TSEC.SROLE program to enable the role. There is little risk in this approach since no users will have privileges on table APP_ROLES unless you grant SELECT ANY TABLE to a user. If this privilege is granted, the user can retrieve data from any table in the database even if that privilege has not been granted by the table owner.