Oracle Database Security - Expert Oracle and Java Security - page 17

SET ROLE appsec_role;
SET ROLE ALL;
The second command listed above will set the current session for appsec to enable ALL the roles that
have been granted to her, both default and non-default.
Create an Application User
As an example user for connecting to Oracle to run an application, we will create an application user,
appusr . For our purposes, appusr is not tied to a single person, but is what Oracle refers to as a “one big
application user” model user. In this model, numerous people will use an application, and the
application will connect all of them to Oracle as our one big application user. They do not need
individual person accounts and passwords for this access.
GRANT create_session_role TO appusr IDENTIFIED BY password;
 Caution Be sure to give a very complex password to this user—they can potentially select data and do updates
to application data. We only want that activity to take place from within the application. I hope you are getting used
to this warning about passwords and can even anticipate it.
INDIVIDUAL PERSON ACCOUNTS
We will have an opportunity to create Oracle user accounts that are specific to individual people; however,
the administrative requirement for maintaining those users (and granting, validating, and revoking the
required privileges) is a huge responsibility. I think there are better, less tedious and more secure ways to
recognize and authorize individual people in the database, and we will look at that in Chapter 8.
Create the HR View Role
We are going to begin securing access to the data in the Human Resources ( HR ) Sample Schema. We will
create a role named hrview_role . Through that role we will grant access to the data needed by a variety
of applications that we plan to build. At the outset, we only want folks who are on our internal network
to access this data, and only during our normal office hours of 7 AM to 7 PM.
To accomplish these constraints, we will create the role and require that it be verified by a
procedure. Oracle calls this a secure application role because that is its function - it is a role that gives
access to application data, but it is secured by some encoded constraints. This is the same thing we did
to secure secadm_role .
CREATE ROLE hrview_role IDENTIFIED USING appsec.p_check_hrview_access;