Data Encryption in Transit - Expert Oracle and Java Security - page 110

Granting More System Privileges to the Application Security User
In order to succeed at setting up the error logging table, we need to allow appsec to store data in a
tablespace. The default tablespace is “USERS,” and that will suffice for us. We need to specify how much
space appsec may use, a quota . We'll start out permitting two megabytes of space. Execute the following
as appsec :
ALTER USER appsec DEFAULT TABLESPACE USERS QUOTA 2M ON USERS;
Additionally, for appsec to be able to create a trigger, we need to grant the CREATE TRIGGER system
privilege. We will grant it to her non-default role (she'll only need it occasionally):
GRANT CREATE TRIGGER TO appsec_role;
Permitting Users to Execute Packages in Other Schemas
We want HR to execute the appsec security structures. We'd like to create a role to which we might grant
execute on a package, and then grant that role to whomever needs it. However, let's examine why this
approach does not always work. Consider the following statements, which you should not execute:
--CREATE ROLE appsec_user_role NOT IDENTIFIED;
--GRANT EXECUTE ON appsec.app_sec_pkg TO appsec_user_role;
--GRANT appsec_user_role TO hr;
Specifically, the approach illustrated here does not work when procedures, functions, and packages
call procedures, functions, and packages in other schemas. To get a bit ahead of ourselves, we will be
creating procedures in the HR schema that we want to have execute the app_sec_pkg package (you know,
we want HR to call functions to encrypt data).
The problem is that the HR procedures, functions, and packages cannot gain privileges from a role.
This is a restriction (based on the dependency model) designed to keep the HR procedures from getting
invalidated every time we logout or set role. We remedy the restriction by granting execute on the
app_sec_pkg package directly to HR user. For example, execute the following code:
GRANT EXECUTE ON appsec.app_sec_pkg TO hr;
By way of stark contrast, our application user, appusr , and other application users will be calling the
procedures, functions, and packages in the HR schema directly, as needed. We do not imagine appusr
calling our procedures from their own procedures. So we can grant access to our (yet to be configured)
HR Security Package, hr_sec_pkg , to a role that appusr has, the hrview_role . The following is the GRANT
statement, which we will execute later after creating the hr.hr_sec_pkg package:
--GRANT EXECUTE ON hr.hr_sec_pkg TO hrview_role;
Later we will execute a script named HR.sql . This script creates the hr.hr_sec_pkg package, and also
executes the preceding GRANT statement.
Application Security User Activities
We are going to create an error log table, and an insert trigger, and we will also be adding procedures to
do logging to app_sec_pkg package.