Database Reference
In-Depth Information
we do not want to originate the secret password key.) It is mandatory that developers understand that
the client MUST get the secret password key from the Oracle database first, and only then may use it to
encrypt data for insert or update to Oracle. No harm will come if developers fail to follow that guideline,
but their code will not work.
Oracle Structures for Secret Password Encryption
In the last chapter, we created an Oracle function and a procedure to demonstrate use of our RSA public
key encryption in a client/server environment. Now we will create an Oracle package with multiple
functions and procedures, including Java stored procedures, to handle our secret password encryption.
The package will be placed in the Application Security, appsec schema. As the appsec user, first set
your role to the non-default role, appsec_role with this command:
SET ROLE appsec_role;
Note You can find a script of the following commands in the file named Chapter6/AppSec.sql .
As you read through this section, you can follow along in the referenced code file, and see in context
the code that's discussed in this text. Also, you can execute the code to create the Oracle structures as
you read about them. We will use these structures when we run tests at the end of the chapter.
Package to Get Secret Password Artifacts and Encrypted Data
A package in Oracle database is a set of functions and procedures that can be configured as a group.
Permissions to access the functions and procedures in a package are granted by granting executable
permission on the package. We will learn one additional benefit of Oracle packages when we get to
Chapter 7: we can define new data types and use them in Oracle packages.
An Oracle package has two parts, the specification and the body. The specification provides the
signature for each procedure or function, but the actual code is only included in the body. This two-part
identity for packages allows PL/SQL programmers to share the functionality (the specification) without
sharing the code (the body). You might do this for separation of duty, security through obfuscation, or
simply to protect your intellectual property. C programmers will recognize this approach as being
analogous to having a header file for each code file.
Application Security Package Specification
An Oracle package specification merely defines the functions and procedures, listing the expected
parameters and the return type for functions. Listing 6-10 shows the app_sec_pkg package specification.
Execute this as appsec user.
Listing 6-10. Package Specification for Secret Password Encryption
CREATE OR REPLACE PACKAGE appsec.app_sec_pkg IS
-- For Chapter 6 testing only - move to app in later versions of this package
PROCEDURE p_get_shared_passphrase(
 
Search WWH ::




Custom Search