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

Test for Normal Business Hours
The second test in p_check_hrview_access , shown in Listing 2-2, contains the statement TO_CHAR
(SYSDATE, 'HH24') BETWEEN 7 AND 18 . Let's take a minute to break this down. SYSDATE is the name for
the current time and date on the Oracle database. You can SELECT (request) SYSDATE from the server, and
you can set date values to be equal to SYSDATE when you update data in Oracle. In this case, we are
requesting SYSDATE and using the TO_CHAR function to format just the hour ( HH24 ) portion the date into a
character string using the 24-hour format. In this format, when it is 7AM, the TO_CHAR function will return
7; and when it is 7PM, 19. So we check that the hour is BETWEEN 7 and 18 (at 6:59PM, this test will still be
true; but at 7PM, false.) We have determined that our normal business hours are between 7AM and 7PM,
so these tests are valid.
 Caution There is no code here to disconnect the user from Oracle when 7PM arrives. This code only prevents
further connections from being established after 7PM.
Permit Application User to Acquire HR View Role
Every user that needs to get access to the HR data will have to execute the p_check_hrview_access
procedure to have the hrview_role set. It is only the hrview_role that has access to the data, and the only
way to set that role is by executing the procedure. We will grant the EXECUTE privilege for this procedure
to our application user, appusr . As secadm , execute this:
GRANT EXECUTE ON appsec.p_check_hrview_access TO appusr;
We will have to grant EXECUTE on this procedure to all users who need access to the data.
Alternatively, we could grant the EXECUTE privilege to PUBLIC , but we would only want to do that if every
user of the database required access to the HR data. Do not grant privileges to PUBLIC without considering
the implications.
Audit Changes to Security Administrator Procedures
We will close out this part of the chapter by establishing some additional auditing. Because we are
working as SECADM , the default structures for which we define auditing are in the SECADM schema. We will
audit any change to procedures in our schema, because they are security related. We need to assure that
any changes have been vetted.
AUDIT ALTER ANY PROCEDURE BY ACCESS;
This is actually one of the privileges that is audited by default when the secconf.sql script is run as
part of the database creation.
Audit Failed Attempts to Access HR Data
Our first audit on access to the HR data is really an audit on attempted execution of the
p_check_hrview_access procedure, which sets the hrview_role . We don't want to know when this
procedure succeeds, but we would like to know when invalid access is attempted, so we use the
keywords WHENEVER NOT SUCCESSFUL .