5.6 Using the Default Roles
As you've surely concluded by now, neither the CONNECT nor the RESOURCE role is appropriate for general users those who will use the applications or will use an interactive query tool such as SQL*Plus. Not only are these roles far too permissive in terms of system privileges, but they also do not convey any intelligence about the user, as we'll discuss in the following section.
5.6.1 Creating Roles with Meaningful Names
Suppose that we have Accounts Payable, Accounts Receivable, Personnel, and Organization logical databases, and a user has the CONNECT role. You would not be able to tell from the role which of these logical databases would be used. However, if you had defined roles such as AP_USER, AR_USER, PERS_USER, and ORG_USER, then you could tell rather quickly to which group the user belonged. These roles would only need one system privilege CREATE SESSION and any minimal table or procedure privileges required to get them started.
5.6.2 Advantages of Customized Roles
Another advantage of using roles in a specific manner is the ability to disable particular groups of users without impacting the remainder of the user community. Should it be necessary to block the personnel group, all the DBA would have to do would be revoke CREATE SESSION from the PERS_USER role. Since that role would be assigned to the personnel users as the default, the users would not be able to log in to the database. All other users would be unaffected and could continue their work. When the database or application work on the Personnel logical database was completed, granting CREATE SESSION to the PERS_USER role would re-enable the logins for those users.
| || |
The term " logical database" is used here to distinguish a group of objects (tables, views, etc.) that are associated with an application or schema area which is housed in a database. This is what the user generally means when referring to the "database." There may be many logical databases within a single physical database. The logical databases may be associated with only one application or may share tables between applications. For example, an employee table might be used by the HR application in its logical database as well as the Payroll application in its logical database.