Database Reference
In-Depth Information
:= sys.f_get_off
in our logon trigger procedure,
p_appver_logon
, Listing 10-14? It is a nice idea that
won't work. The
f_get_off
function shown in Listing 10-15 could accomplish the task for us; however,
Oracle Database does not allow us to kill the current session.
Listing 10-15.
Non-functional Kill Switch, f_get_off
CREATE OR REPLACE FUNCTION sys.f_get_off
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
p_sid v$session.SID%TYPE;
p_serial v$session.serial#%TYPE;
BEGIN
p_sid := SYS_CONTEXT( 'USERENV', 'SID' );
SELECT serial# INTO p_serial
FROM v$session
WHERE sid = p_sid;
EXECUTE IMMEDIATE
'ALTER SYSTEM KILL SESSION ''' ||
p_sid || ',' || p_serial || '''';
RETURN 'OFF';
END f_get_off;
GRANT EXECUTE ON sys.f_get_off TO appsec;
Note
You can find Listing 10-15's function in the file named
Chapter10/Sys.sql
.
The heart of the function is in the
EXECUTE IMMEDIATE
command. An alternative along these lines
that would work would be if we inserted the
SID
and
SERIAL#
to be killed into a table. Then using an
independent, scheduled task, read the table and kill the sessions, then delete the records from the table.
Anyway, manhandling in the most direct way is not going to work. Just as well, because our call in
p_appver_logon
to
RAISE_APPLICATION_ERROR
does the same thing. Because we are in a logon trigger,
when we raise an exception, the logon fails.
Function to Find Database User
There is a function that we have, for use in the
appver
logon trigger procedure,
f_is_user
, which tests
whether the OS user is also a database user. This is an important test, because it enforces a portion of
our SSO requirements—if we haven't created an Oracle user with the same name as this person's
operating system user name, then he cannot use our applications. This function, Listing 10-16, will be
added to the package,
appsec_only_pkg
.
