Oracle Security

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.

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.

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