Oracle Security

Roles

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.

Note

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 ...