At Your Service

In a world of ever-increasing access account security has become a major concern. More often than not accounts used for automated processes gather more and more privileges, on the assumption that they are used only by managed, automated processes and execute vetted code, The argument is that these automated processes can’t go rogue and do damage with these elevated privileges, which is true, sort of. In and of itself the database does not police where these account connections originate, which can pose a significant security risk should this vetted code, or passwords for these privileged accounts, become known.

Oracle has, for decades, provided after logon triggers, which can be utilized to ‘police’ accounts by reporting where they originate and, if necessary, disallowing such logons. Of course it will be necessary to record such connection ‘violations’ in a table so that they may be investigated. A “complete” script to do this is shown below:

—

— Create reference table of usernames and machines

create table db_audit.ip_restrict(
username varchar2(128),
machine varchar2(128))
tablespace db_audit_data;

alter table db_audit.ip_restrict add constraint ip_rest_pk primary key(username, machine) tablespace db_audit_data;

—

— Populate table

insert into db_audit.ip_restrict
select distinct username, machine
from v$session;

commit;

delete from db_audit.ip_restrict
where username hot like ‘%USER%’
and username like ‘%local’
and username not like ‘SPLUNK%’;

commit;

—

— Create access violations table

create table db_audit.access_violations(
username varchar2(128),
machine varchar2(128),
terminal varchar2(128),
violation_dt date)
tablespace db_audit_data;

alter table db_audit.access_violations add constraint acc_viol_pk primary key(username, terminal, violation_dt);

—

— Violating session info table

create table db_audit.violating_sessions
as select * from v$session
where 0 = 1;

And the trigger:

—
— Create after logon trigger to verify user can

— login to the database

— Application users can ONLY login from their configured

— machines

— Should prevent regular users from connecting with service

— accounts for security reasons

create or replace trigger ip_rest_trg
after logon on database
declare
v_user dba_users.username%type;
v_mach v$session.machine%type;
v_valid number:=0;
begin
select username, machine
into v_user, v_mach
from v$session
where sid = sys_context(‘USERENV’,’SID’);

select 1
into v_valid
from db_audit.ip_restrict
where username = v_user
and machine = v_mach
    and username in 
( <list accounts individually here>);


if v_valid <> 1 
and v_user in 
( <list accounts individually here>)
then
    insert into db_audit.access_violations
    select v_user, v_mach, sys_context('userenv','terminal'), sysdate from dual;

    insert into db_audit.violating_sessions
    select * from v$session
    where sid = sys_context('userenv','sid');

    raise_application_error(-20998, 'Invalid access, logon denied.');
end if;

end;
/

The process works because it us presumed that all current logons by these declared service accounts are originating from valid servers. The DBA must verify this information in the reference table prior to implementing this process. Once the account/server reference table data is accurate the trigger can be implemented and logons by these service accounts policed. Any violations will be recorded for later examination.

Notice that no service accounts are actually listed in this code; each database installation will have its own set of service accounts and these will be specifically listed in the trigger code where the placeholder is found. The format is:

(‘acct’,’acct’,’acct’,…)

For example if the accounts of interest are BOB, ED, FRED and ORVILLE that section of code would appear as:

…
select username, machine
into v_user, v_mach
from v$session
where sid = sys_context(‘USERENV’,’SID’);

select 1
into v_valid
from db_audit.ip_restrict
where username = v_user
and machine = v_mach
    and username in 
('BOB','ED','FRED','ORVILLE');

if v_valid <> 1 
and v_user in 
('BOB','ED','FRED','ORVILLE');
then
    insert into db_audit.access_violations
    select v_user, v_mach, sys_context('userenv','terminal'), sysdate from dual;

    insert into db_audit.violating_sessions
    select * from v$session
    where sid = sys_context('userenv','sid');

    raise_application_error(-20998, 'Invalid access, logon denied.');
end if;

end;
/

The reference table data does need regular maintenance since new service accounts can be created, logging in from new servers. Such additions should not be surprises, making such maintenance easier. As long as the reference table data is current all invalid logins should be captured and reported. Of course when new accounts are added the trigger code will need to be modified and recompiled to include those new entries. Accounts not specifically listed will pass through the trigger unscathed, so not including new service accounts will allow those accounts to connect from any machine and not be audited, so due diligence is required to maintain the audit process.

Once a violation is discovered it is recorded into the access_violations table and the current session information is inserted into the violating_sessions table before the rogue session is terminated. This provides the DBA team with all of the information required to trace the offending session to the user who tried to create it.

It may be that not all ‘violating’ sessions are actually suspicious — password changes for service accounts do need to be tested and connections using these accounts can therefore originate from servers not otherwise used for those logins. Password testing should be a known event and, when such changes occur, the trigger can be disabled for the period those tests are active. Another possibility is to temporarily add the service account/machine pair to the reference table; it is required that these temporary entries be immediately removed once testing is completed. And another possibility is to modify the reference table and the trigger code to utilize a VALID column, populated with Y and N, to indicate wihch rows in the table should be used for connection restriction. Setting VALID to Y For testing entries allows t hem to connect during the testing window; remember to reset those test entries to N once testing has successfully completed.

Security is not easy, but it is worth the effort. Preventing unauthorized logins by privileged accounts is necessary to deter hackers and those who want elevated privileges they do not normally possess. Service accounts are designed for automated processes that execute known, unchanging code on a regular schedule using passwords stored securely and retrieved with API calls to the calling application, preventing prying eyes from seeing these passwords. By the same token such API calls may also be hijacked and used to gain access to these supercharged accounts. These are the situations the trigger shown here is designed to thwart. As long as the DBA team stays vigilant the incidence of successful rogue privileged logins should be 0.

Monitoring access to a database by privileged accounts should be a must for any database system in current use. The trigger and code provided here should be of help to DBAs wanting to secure their systems from external as well as internal attacks. Not every threat to a database is from outside the enterprise; internal attacks are even more insidious as they are already inside the firewall which is one of the first lines of defense used to restrict access. Monitoring both sides of that barrier is the only way to truly capture and disable those attacks. Remember that such attacks are crimes of opportunity. The method provided here is one way to reduce those opportunities and guard the gate from intruders.

“ID, please.”