You’re Not Allowed

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
Norton Juster, The Phantom Tollbooth 

Recently a thread in an Oracle forum I frequent asked the following question:

“… is there a way to restrict the login to database only from the application which we have made.”

That, of course, is not an easy question to answer as several possibilities emerge with none of them foolproof. Let’s look at those options.

The issue is to restrict logins from one (or more) specific servers to only those from the in-house application. The first thought is a logon trigger using MACHINE and PROGRAM information found in V$SESSION along with other available details:

Create table app_ip_add(
	app_nm		varchar2(40),
	machine		varchar2(64),
	short_nm	varchar2(64),
	ip_address	varchar2(16)
);

create index app_nm_ip_idx on app_ip_add(app_nm, ip_address);

create table blocked_logon(
	block_dt	date,
	machine		varchar2(64),
	ip_address	varchar2(16),
	message		varchar2(200)
);

insert into app_ip_add values ('flump.exe','INFOCROSSING\E642-FITZJARREL','E642-Fitzjarrel','10.1.68.25');

commit;

CREATE OR REPLACE TRIGGER SYS.BLOCK_LOGON_TRG
after LOGON ON DATABASE
DECLARE

v_program	v$session.program%type;
v_machine	v$session.machine%type;
v_cip		varchar2(20);
v_usr		varchar2(35);
v_host		varchar2(100);
v_ip		varchar2(20);
v_logtme	date;
v_blk		number:=0;
blocked		exception;
pragma exception_init(blocked, -20001);

begin
select sys_context('USERENV','SESSION_USER') into v_usr from dual;
select sys_context('USERENV','HOST') into v_host from dual;
select sys_context('USERENV','MODULE') into v_program from dual;
select sys_context('USERENV','IP_ADDRESS') into v_cip from dual;
select SYSDATE into v_logtme from dual;


---- then your logic to check whom to restrict.
select 1 into v_blk from app_ip_add where ip_address = v_cip and app_nm  v_program;
dbms_output.put_line(v_blk);
-- and finally message to the user.

if v_blk = 1 then
  insert into blocked_logon                       -- this table tracks all access to the database.
  values
  (sysdate, v_machine, v_ip,'Blocked by Logon Trigger');
  raise blocked;
end if;


exception
	when blocked then
	    raise_application_error(-20001,v_usr||' - Blocked - please contact Admin');

end block_logon_trg;
/

SQL>

The trigger compiles successfully and populates the BLOCKED_LOGON table but it doesn’t throw the error and the login succeeds. Since a session cannot kill itself the trigger can’t be modified to execute a ‘alter system kill session’ command and, thus, it won’t do much except log that a particular session should have been blocked. Strike 1.

Another possibility is to configure SQL*Net to use tcp.validnode_checking along with tcp.invited_nodes:

tcp.validnode_checking = yes
tcp.invited_nodes = ()

When using IP Addresses the ‘*’ wildcard can be used to define a range of valid IPs, such as 1.2.3.* to allow any server from 1.2.3.0 through 1.2.3.255. Additional nodes can be invited by IP Address (again using wildcards) or hostname, each entry separated by a comma. A valid entry for three ranges of IP addresses would look like this:

tcp.invited_nodes = (1.2.3.*, 78.44.227.*, 121.217.*.*)

Such a configuration would be useful where application servers reside in several subnets, allowing you to configure all subnets to connect to the database. Unfortunately this option blocks connections from all ‘uninvited’ nodes and doesn’t disallow connections simply because the program trying to connect isn’t in a list of valid executables, so sqlplus can run as well as the application program since both are on the invited nodes. This was not the original intention as additional criteria should be met (what program the user is running) to ‘weed out’ the invalid connections.

Going the ‘other way’ we can also use tcp.excluded_nodes in the same way, which excludes access to the hostnames/IP addresses in the list:

tcp.excluded_nodes = (1.2.3.*, 78.44.227.*, 121.217.*.*)

Now the listed IP address ranges are excluded from connecting to the database. And it is possible to use both parameters, but remember that tcp.invited_nodes takes precedence over tcp.excluded_nodes and any conflicts would be resolved in favor of connection.

Try as we may it appears that even though we can restrict connections to a set of valid server names or IP addresses we cannot go further and restrict connections to a valid list of executables. Well, we probably could but a resourceful end user might make a copy of the ‘undesirable’ program and rename it to an ‘acceptable’ name, especially on client laptops. Thus the ‘security’ would be compromised since sqlplus.exe could be named frimmitz.exe, or gramp.exe or any number of other names that do not appear in the ‘no fly’ list. It seems the best we can do is to either allow connections from a small subset of servers or disallow connections from a small subset of servers.

It is expected that this would be in addition to the security policies and standards already in place with your employer such that you’re not trying to keep ‘unauthorized’ persons from accessing your databases, simply making an attempt to restrict the database tools the end-users have at their disposal. I imagine the goal is to prevent ‘runaway’ queries from consuming resources, and if that’s the case then Oracle Resource Manager would probably be a better option as Resource Groups can be established along with corrective actions to be taken when resource limits are exceeded. It’s usually best to use the tools the vendor supplies, rather than try to ‘rig’ other, less robust methods. Oracle provides sufficient tools for such purposes, and even though Resource Manager cannot reject logins it can keep users from stealing resources other applications and business processes need. And that, in my mind, is the end goal, to prevent any one user from hogging server resources like CPU and memory. There should be no reason to re-invent the wheel.

Now, have you seen my blindfolded octopus? He’s here somewhere…