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

 Note You can find a script of the following commands in the file named Chapter7/AppSec.sql .
Connect to Oracle database as appsec user, and set your role to the non-default role, appsec_role :
SET ROLE appsec_role;
Creating a Table for Error Logging
Next, create a table for error logging. Probably, you want to pull out your DBA skills or get a DBA to help
you define this table, setting its performance parameters and estimating initial storage and growth
plans, none of which is defined here. Execute the code in Listing 7-1 to create the table using the
defaults.
Listing 7-1. Create the Application Security Error Log Table, t_appsec_errors
CREATE TABLE appsec.t_appsec_errors (
err_no NUMBER,
err_txt VARCHAR2(2000),
msg_txt VARCHAR2(4000) DEFAULT NULL,
update_ts DATE DEFAULT SYSDATE
);
We are going to capture the Oracle error number, err_no , and text, err_txt , and provide ourselves
with another field, msg_txt , for helpful information (e.g., method name or stack trace). We will also
capture the time of the error, update_ts , which helps in two ways: first, we want to know when things
happened, or what is happening; second, we want to throw away log records when they are too old to be
helpful.
Note that the last two column definitions in the listing specify default values using the keyword
DEFAULT . To insert a record, you only need to insert the first two fields. The third will default to NULL , and
the fourth will default to the current date and time on the Oracle database, SYSDATE . In fact, we do not
want to insert a date in UPDATE_TS . We want to accept the default. Because appsec is the only one who will
be entering data in this table (this is an error log for application security use, not for general use), we do
not need to enforce the default UPDATE_TS .
In order to accomplish our sorting and selection of records by date, we are going to set up an index
on the UPDATE_TS column. Execute the code in Listing 7-2 to create the index.
Listing 7-2. Index for the Application Security Error Log Table, t_appsec_errors
CREATE INDEX i_appsec_errors00 ON appsec.t_appsec_errors (
update_ts
);
Indexes, once created, are maintained automatically as you insert or update rows. Also, they are
automatically used when you select records from the table. Which index is used is a logical choice made
by Oracle database that can be overridden by a hint, if desired. One thing to be aware of is that a select
query that does not mention the leading column of an index will not benefit directly from the index. For
example, if we selected all rows from the t_appsec_errors table where the msg_txt column contains the
string “Exception,” we would not directly use the index we just created. If we created an index on
columns ( update_ts, msg_txt ), that index would not benefit our query directly either. To get the direct