Database Reference
In-Depth Information
•
Again, we send encrypted data to Oracle database for insert/update, and we
succeed. Now the salary is 9700.75 (now my wife is dreaming).
Querying Employees to See Updates
You can see the status of the record we inserted / updated by connecting to Oracle database as the
HR
user and executing the following command:
SELECT * FROM employees WHERE employee_id=300;
Packaging Template to Implement Encryption
I know that folks find the cost of security, like the cost of quality, to be unpalatable; but the potential cost
of insecurity is inordinately higher. Our security structures are only valuable if put to use. We will add
some value in the next few chapters, but as it stands now, getting folks to use our security structures may
be like pulling teeth. This is not one of those “if you build it, they will come” moments.
We are going to lower the threshold of entrance into these security structures by providing
application developers with templates that they can use to quickly implement the back end Oracle
structures for their application, and the front end Java calls to those structures.
Template for Oracle Application Security Structures
The first file that we will give to developers provides the code for an Oracle security package that they
will need to implement in their application schema. In the file, there are generic names for the
application schema, the package, procedures, tables/views and columns. Developers should search and
replace those names with the actual names they will use.
Note
You can this file at
Chapter7/AppPkgTemplate.sql
.
Your security administrator,
secadm
, will need to grant execute on the
appsec.app_sec_pkg
to the
developers' application schema user so the application can use the
app_sec_pkg
procedures and
functions.
In order to create the application package, the developer will need the
CREATE PROCEDURE
system
privilege. Also, in order for the application user(s) to use the application package, all application users
will need the
EXECUTE
object privilege on the package granted to them.
We have four template procedures in the package:
p_get_shared_passphrase
p_select_APPTABLE_sensitive
p_select_APPTABLE_by_COLUMN1_sens
p_update_APPTABLE_sensitive
There is nothing new being introduced to you here—you have seen all these structures used in the
HR
schema.
