Database Reference
In-Depth Information
Testing the Trigger
While you are connected to Oracle database as
appsec
user, you can test the trigger. First execute the
following lines to insert an error log entry and commit it:
INSERT INTO appsec.v_appsec_errors (err_no, err_txt ) VALUES (1, 'DAVE' );
COMMIT;
Note that our autonomous procedure can only deal with data that exists independently. Our inserts
and updates do not exist in the database independently until we
COMMIT
the data.
Note also that we are depending on the default values for
msg_txt
and
update_ts
—those columns
are not part of our insert statement.
Query each of our tables, the error logs and the maintenance record, and observe that our previous
insert succeeded and that the Janitor procedure ran. Here's an example:
SELECT * FROM appsec.v_appsec_errors ORDER BY update_ts;
SELECT * FROM appsec.t_appsec_errors_maint ORDER BY update_ts;
Now insert an error log entry that pretends to be 60 days old (note the arithmetic with
SYSDATE
):
INSERT INTO appsec.v_appsec_errors (err_no, err_txt, msg_txt, update_ts)
VALUES (2, 'DAVE', 'NONE', SYSDATE - 60 );
COMMIT;
Again query each of our tables to assure that our insert worked and that our janitor procedure did
not run again (because it has already run on this day):
SELECT * FROM appsec.v_appsec_errors ORDER BY update_ts;
SELECT * FROM appsec.t_appsec_errors_maint ORDER BY update_ts;
Now change the data of our last janitor maintenance run date to yesterday (actually, 24 hours ago),
and assure that the change was effective (if you have more than one entry in this table, this
UPDATE
won't
work. The index on
UPDATE_TS
in this table is a
UNIQUE
index):
UPDATE appsec.t_appsec_errors_maint SET update_ts = SYSDATE-1;
COMMIT;
SELECT * FROM appsec.t_appsec_errors_maint ORDER BY update_ts;
And submit another record from today (default,
SYSDATE
):
INSERT INTO appsec.v_appsec_errors (err_no, err_txt ) VALUES (3, 'DAVE' );
COMMIT;
For the last time in these tests, query each of our tables to assure that our insert worked and that our
janitor procedure ran a second time, and that the mock-old record (the one with
err_no = 2
) was
deleted:
SELECT * FROM appsec.v_appsec_errors ORDER BY update_ts;
SELECT * FROM appsec.t_appsec_errors_maint ORDER BY update_ts;
Updating the Application Security Package
In Chapter 6, we had two procedures (
p_get_shared_passphrase
and
p_get_des_crypt_test_data
) and
one function (
f_show_algorithm
) that we described as being “temporary”. They were used for testing
only in Chapter 6, and we will remove them from
app_sec_pkg
in this chapter. The remainder of the
