Secure Application Roles
Roles are a powerful method for managing privileges in the Oracle Database. Oracle first introduced database roles over a decade ago with Oracle7. Roles can be granted to users and other roles. Once granted to a user, roles can be set to be default roles, so that the privileges associated with it are active as soon as the user has successfully authenticated to the database. If a role is not set as default, it can be invoked using the "set_role" command.
Oracle9i introduced a powerful enhancement to database roles called "Secure Application Roles". Secure application roles are created by specifying a package name in the "create role" syntax:
SQL> create role acme_hr_role identified using approles_package
Once granted a secure application role, a user must have permission to execute the package associated with the role in order to activate it. In this example, the "approles_package" is defined by the database administrator or security officer using PL/SQL. The package can perform any number of security checks, including application specific parameters defined in the Oracle Application Context, making it difficult to circumvent.
This is the most secure way of protecting roles, since the decisions the database makes are based on the implementation of your security policies, and the definitions are stored in one central place, as opposed to storing them in all your applications. This provides additional benefits: Should the policy need to be updated, this is done only once in the database. Furthermore, no matter how the users connects to the database, the result is the same, since the policy is bound to the role.