How do i prevent end users from connecting to the database other than my application?", version 8.1.5
You Asked (Jump to Tom's latest followup)
Hi Tom,
Thanks for your help.
We have a client/Server and a web interface. We want our end users
to connect to the database only through our applications. How
can we prevent the user from connecting to the database using
SQL*PLUS or Microsoft acess using ODBC, or other third party tool.
We have been restricting them through roles, but the way our
user requirements are, there needs to be atleast one role that
is always enabled which i see as a potential security flaw. Any
help, pointers is appreciated.
and we said...
The best method, in my opinion, is to put your application in the database. In
that fashion -- your users never have access to any of the base tables -- only
your procedures and functions (your application). In this fashion -- even if
they get into the database -- they can only run your application.
Short of that, you might look at fine grained access control (FGAC). See
for some info on that.
For example, we used FGAC in an online review system we built. We made it so
that if a specific security context was not set up -- all queries return 0 rows
for all selects, updates and deletes and prevented the users from inserting any
data. So, if I log into sqlplus -- the security context was not setup (the
correct procedure with the correct inputs was not executed to set the security
context) -- hence the tables appear "empty".
There is no enforcible way to restrict access to the database by "program". For
example -- if your program was "foo.exe" and you only wanted foo.exe to connect
-- all i would have to do is:
rename foo.exe tmp.exe
copy sqlplus.exe foo.exe
run foo.exe (aka sqlplus) and be done with it.... I'm right in...
Reviews
GOTO a page to Bookmark Review | Bottom | Top
March 21, 2002
Reviewer: Mark from Ca
When you say 'There is no enforcible way to restrict access to the database by
"program"', are you inferring that there is something intrinsically bad about
the method. I was thinking of doing the following:
create or replace trigger catch_violators_on_<table_name> as
BEFORE INSERT, update, delete ON <table_name> FOR EACH ROW
BEGIN
select 'ok' into v1
from v$session sess, ops$oracle.runtime_exemptions re
where sess.audsid = userenv('sessionid') and
(not userenv('isdba') or
sess.schemaname in (<acceptable nondba schemas>) or
sess.program in (<acceptable program names>));
exception when no_data_found then
-- either capture violation information and continue or
-- write out using utlfile and stop insert,update,delete action
insert into runtime_violations
select schemaname, program, osuser, terminal, machine, sysdate, ...
from v$session where sess.audsid = userenv('sessionid') ;
END;
I know this is not pretty, but it is fast to implement and seems to do the job.
The application software is old and unsupported and the original developers
didn't seem to have a high priority on implementing a solid security process in
the app. This is on 8.0.5. Other than the trigger hit on performance, is there
another reason this may not be wise?
Followup:
Try this
copy sqlplus.exe somethingelse.exe
and see what happens.
here i took the time to look this article up and ended up reading right by the
point of the article...thanks tom...next time i will be a little more observant
GOTO a page to Bookmark Review | Bottom | Top
what about non-default roles with passwords? March 22, 2002
Reviewer: Alexander Rakhalski from Moscow, Russia
Hi again, Tom!
I'm regularly reading your forum and believe it is most productive way to raise
my skills in Oracle. Thank you very much. Now some my thoughts on question.
1. It seems not very good idea to rely on granting access only to executable
routines (not tables), because a lot of security-related logic may be enforced
in client. So, if I say: "You can execute any of my routines in any sequence and
with any parameters you wish" it is not same,if I say: "You can access database
through my application only".
2. What if I grant privileges (including CREATE SESSION) to users through
non-default roles with passwords? Role's passwords unknown by users, but are
ENABLEd during application startup. Here appears other problem - how can I hide
role's passwords within application, but maybe, such approach has some value?
Followup:
1) I do not believe any security should be enforced in the client -- that is the
entire goal of FGAC -- to put it in the server, where (IMHO) it belongs.
If the client does the security - you had better erase all third party ad-hoc
tools, including sqlplus, brio reports, discoverer, anything.
I myself put the application in the database...
2) it would take me about 2 seconds to defeat your approach, maybe a little
longer if you used advanced security option with encryption (but not too much
longer).
1. If it would take you about 2 seconds to defeat my approach, maybe you agree
to spend 2 second + (some seconds for writing)?
2. I already pointed out above my doubts regarding "granting only EXECUTE on
server routines" approach. Now, if we examine "fine grained access control"
approach, it still rely on "some magic actions, hidden in the client and
executed at startup". If in my approach it is some calls to DBMS_SESSION, in
your it is some calls to application context package. I can (potentially) log on
with SQL*Plus and execute some routines in application context package (like it
do my client application). Some security is left on client.
Followup:
1) turn on sqlnet tracing (done on the client) and run your application. The
trace file will have your password in it. Bamm -- I'm in, thank you very much.
2) FGAC does not rely on some magic actions, hidden in the client and executed
at startup. It is 100% server contained -- 100%. There is NO security needed
in the client. If you design it that way -- we have no way of protecting you.
The preferred mode of setting an application context is during the ON LOGON
trigger -- before the application can even do anything in the database.
I don't understand, how can ON-LOGON trigger determine, is connection made
trough application or SQL*Plus?
Followup:
I didn't say it could (in 9i with ntier proxy authentication we do have
APPLICATION specific VPD, thats new). Nor did I say that it has to. The
on-logon trigger could tell if the app server was being used to connect or not.
If not, no data. If so, data.
It doesn't have to. The data is secured in the same fashion through SQLPlus as
anything. That is my entire point. If the application security is REMOVED from
the application and put back with the data (where in my opinion is rightly
belongs) you can safely access the data from ANY CLIENT, ANY WHERE, ANY TIME.
If you lock the security in the client, you can only access the data via that
client. You have totally locked yourself in. You know, when this web thing
became exciting, the hardest thing for people was supporting this new "paradigm"
on top of their existing systems. How could you build new apps on top of an
existing system where the security was buried in tons of legacy code. Most
people still don't do that (build on top of their existing systems) due to this
-- they build an ENTIRELY new system, pump data into that then run that data
through the existing applications (like a batch system). If they had the
security with the data, they wouldn't need to glue systems together in a
piecemeal approach.
How can you prevent a User connecting to the Database Using ODBC? March 26, 2002
Reviewer: Senthil Kumar from Qatar
Hi Tom
You mean to say that we cannot prevent a user from connecting to the database
using ODBC? oracle should come out with a solution for this. I hope you agree
with me
Followup:
How could we. ODBC looks no different to us then any other connection. ODBC is
just an API on top of OCI (Oracle's call interface). There is no way for the
database to notice the difference between an ODBC client and sqlplus -- they
look EXACTLY the same to us.
That aside, if you put the security in the database, where it belongs (IMHO)....
It quite simply *does not matter* if you connect via odbc, oci, jdbc, etc, etc,
etc. If the data is always protected (and not just protected by some EXTERNAL
logic hidden in an application somewhere), it is always protected. You no
longer CARE what people use to connect.
Hi all,
Suppose you have a situation like this:
Consider a software (say .. SQL*Plus) connects to a database and perform some
action.
Suppose it uses TEST as an Oracle User ID to connect with the database.
Its quiet obvious that user TEST could connect to database using say
TOAD/VB/VC++ etc.
Now task is to restrict any software other than SQL*Plus to connect to Oracle
database.
Oracle Job scheduler (dbms_job) uses the following procedure to acheive this
task.
Thought of sharing this with you all. Hope its usefull !
/* Oracle Job Scheduler DBMS_JOB */
create or replace procedure p_jobscheduler is
begin
/* Submit a job to DBMS_JOB as below */
/* jobno is a bind variable you need to declare before running this block*/
/* SYSDATE + 1/18000 makes the job run after every 5 seconds */
DBMS_JOB.SUBMIT (:jobno,'P_SCHEDULE;', SYSDATE, 'SYSDATE+1/18000');
end;
/
/* Procedure should run in INTERNAL/SYS/SYSTEM in order to function*/
/* Author : Srirama Kiran */
CREATE OR REPLACE PROCEDURE P_SCHEDULE IS
CURSOR C_SESSION IS SELECT SID,SERIAL#,USERNAME,MODULE
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND USERNAME NOT IN ('SYS','SYSTEM','INTERNAL');
/* Users SYS,SYSTEM,INTERNAL have been allowed to connect via any software */
/* Retrieve all the relevant columns from v$session*/
BEGIN
FOR C_KS IN C_SESSION LOOP
/* Cursor For loop */
IF NOT C_KS.MODULE = 'SQL*Plus' THEN
dbms_output.put_line(C_KS.MODULE);
/* You should be in Oracle 8i and above to make this statement work. */
EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C_KS.SID || ','
|| C_KS.SERIAL# || '''');
END IF;
END LOOP;
EXCEPTION
/* Oops..something went wrong !! Have a look ... turn on your serveroutput */
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED' || SQLERRM);
END;
/
Please inform me if there needs to be some enhancement needs to be done.
Thanks & Regards,
Kiran Srirama
Followup:
all I need to do is issue:
dbms_application_info.set_module( 'SQL*Plus', '' );
and you are defeated. This is the holy grail -- "only let this application
connect to my database". Well, that particular holy grail is not achievable
(prior to 8i with ntier authentication and 9i with ntier in java
authentication).
The only way it's achieveable before that is to put the application in the
database (packages), grant execute on the application in the database and let
anyone connect via any method they want. Now, the only thing they can do is run
your application.
Hi,
dbms_application_info.set_module( 'SQL*Plus', '' );
does not offer a flexibility of avoiding SYS/SYSTEM/INTERNAL or infact any other
user to be unconstrained.
Guess your solution is defeated here !!
Anyway, your solution is good enough but not so flexible.
Regards
Kiran Srirama
[email protected]> grant create session to comm identified by comm;
Grant succeeded.
[email protected]>
[email protected]>
[email protected]> @conn comm/comm
[email protected]>
[email protected]>
-- In 5 seconds I could execute this at least 5 times ;)
[email protected]>
[email protected]> exec dbms_application_info.set_module( 'MyApp*Plux', '' );
PL/SQL procedure successfully completed.
[In another session where I have permission to see v$session]
[email protected]> select username, rpad(program, 20) program, rpad(module, 20)
module
2 from v$session;
USERNAME PROGRAM MODULE
------------------------------ -------------------- --------------------
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
COMM SQLPLUS.EXE MyApp*Plux <<----
OPS$MPORTES SQLPLUS.EXE SQL*Plus
OPS$MPORTES SQLPLUSW.EXE SQL*Plus
13 rows selected.
[back a mortal user]
[email protected]> exec dbms_application_info.set_module( 'SQL*Plux', '' );
PL/SQL procedure successfully completed.
[go see v$session]
[email protected]> /
USERNAME PROGRAM MODULE
------------------------------ -------------------- --------------------
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
ORACLE.EXE
COMM SQLPLUS.EXE SQL*Plux <<----
OPS$MPORTES SQLPLUS.EXE SQL*Plus
OPS$MPORTES SQLPLUSW.EXE SQL*Plus
13 rows selected.
[email protected]>
See -- Your app was broken and any mortal user could do that ;)
GOTO a page to Bookmark Review | Bottom | Top
does userenv('isdba') work? December 28, 2003
Reviewer: A reader
Hi
I am not sure if it's my problem or this is how it works, I am trying to
determine if my user has dba privileges using userenv('isdba') and getting FALSE
when my user has DBA
select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
PLUSTRACE
select userenv('isdba') from dual;
USEREN
------
FALSE
It only works for SYS, only SYS returns TRUE. But the documentation says this is
how we determine if we have dba role enabled or not. I even tried
sys_context('userenv', 'isdba') and getting same result
Any comments?
Followup:
it is not for that purpose. is should probably be labeled "issysdba"
it tells you about "sysdba", not the dba role.
you would query session_roles (as you did) to see what roles are enabled.
How to allow only forms server and support staff access? September 23, 2004
Reviewer: Andy Hardy from Leicestershire, England
Hi,
We have an old Forms client application accessing a central company database.
All Forms users have a logon to the database, the 'application' schema has
public synonyms and any 'security' is currently limited to the Forms menus.
We're currently running on Oracle 8, but will be upgrading to 9i.
The company structure is changing, some of it will become a new/competitor
company and we need to implement some data security!
To help facilitate this, the application is being upgraded to a central Forms
server with web access. Therefore all access to the application database should
be through the Forms server.
However, we still need to allow access to the database 'internals' to various
development and dba staff. It would seem that a simple firewall would secure the
database from the 'external' world *but* to complicate matters, the external
company may share the internal network...
So, how can we only allow database access to 'anyone using the forms application
or specified people'?
Followup:
you can look into secure application roles, roles which may only be enabled by
invoking a stored procedure, the stored procedure can do whatever checks it
wants
(eg: your forms servers run on IP's A, B, and C -- use
sys_context('userenv','ip_address') in ( 'A','B','C') then enable role else
raise error)
so, use secure application roles (9i feature) and have the roles be enabled via
a stored procedure and have the stored procedure do whatever checks you deem
necessary (query v$session to see the program, look at the ip address, verify
the username is 'valid' maybe have the forms (which you secure on a middle tier)
pass some token you validate, whatever)
Tom, wouldn't this be even better for Andy Hardy? September 24, 2004
Reviewer: Bill from Vermont (USA - Ski season is almost here folks!)
It seems that OLS is exactly what Andy needs to protect his data. Wasn't it
designed in order to allow conditional data access? Might take a little longer
to set up than secure app roles, but in the end it would be a better solution
(imho).
Followup:
seemed to me he wanted to protect
"How to allow only forms server and support staff access? "
that a program (forms, from a known set of servers) and support staff ( people)
could access the data (either all of the data or none or the data).
roles do that.
OLS is for row level -- userA can see "rows 1..10", userB can see "rows 5..15"
This is quite interesting
I just wanted to know
how can i log some details about a user like username, program, osuser,
terminal, machine, sysdate etc to a table.
if they are NOT connected thru sqlplus.exe. Does the log on trigger (after logon
on database) can help me?
Followup:
yes, it can. just create an after logon on database and you can log whatever
you like.
This is quite interesting
I just wanted to know
how can i log some details about a user like username, program, osuser,
terminal, machine, sysdate etc to a table.
if they are NOT connected thru sqlplus.exe. Does the log on trigger (after logon
on database) can help me?
GOTO a page to Bookmark Review | Bottom | Top
Is it possible to prevent user from connecting database with different user in SQL Plus ? October 18, 2004
Reviewer: Parag Jayant Patankar from India
Hi Tom,
I am forcing user to connect as one user ( by rsh in AIX ). Now I do not want
that user to connect by another user using connect command in SQL plus e.g
connect parag/[email protected] . Is it possible ?
Is it also possbile to prevent user to connect database again with different
user without any trigger, procedure or function ?
thanks & regards
pjp
Followup:
no, there is no reasonable, reliable way.
not sure what you would hope to achieve by such a thing.
to the database there is quite simply no such concept as "connect again", they
are all just connections.
Is it possible to Use ALTER SYSTEM DISCONNECT SESSION ON LOGON DB Trigger ?? October 26, 2004
Reviewer: Khandaker Anwar from Dhaka, Bangladesh
Dear Tom,
Thanks for your support. Can you please tell me is it possible to kill or
disconnect any session ON LOGON Database Trigger?
Thanks again.
Anwar
Followup:
Never tried it -- interesting concept. but no
[email protected]> !oerr ora 27
00027, 00000, "cannot kill current session"
// *Cause: Attempted to use ALTER SYSTEM KILL SESSION to kill the current
// session.
// *Action: None.
however, for all NON-DBA accounts, all it would take is
"raise_application_error( ....)" - just raise an error.
DBA's should be allowed in regardless.
... to keep 'SCOTT' out ...
create or replace trigger SYSADM_TRG_AL
after logon on database
declare
EXC_CONN_TERMINATED exception ;
pragma exception_init( EXC_CONN_TERMINATED, -03113 ) ;
begin
if SYS_Context( 'UserEnv', 'Session_User' ) = 'SCOTT' then
raise EXC_CONN_TERMINATED ;
end if ;
end ;
/
Followup:
that'll work as long as scott is not a dba, just like raise_application_error.
it'll fail with "unhandled user defined exception" as the error message to
scott, instead of something like:
ORA-20001: Scott, you lose big time -- Bill
(raise_application_error lets you set the message)
and as mentioned right below, if goal is to keep scott out without any other
checks, locking account would suffice (even for dba)
to keep scott out
alter user scott account lock;
No way he can get in then - why write code when you dont have to
GOTO a page to Bookmark Review | Bottom | Top
to Dave October 28, 2004
Reviewer: A reader
i just thought of some kind of a "conditional" account lock ;o)
GOTO a page to Bookmark Review | Bottom | Top
INTERESTING FINDINGS FROM V$SESSION TO PROTECT UNWANTED ACCESS TO DATABASE October 30, 2004
Reviewer: Khandaker Anwar from Dhaka, Bangladesh
Dear Tom,
I got some interesting result which is shown bellow. I'm using Oracle 9i R2 and
my front end client on Developer 6i.
As you said earlier that anyone can change the program name to anything else but
what you think about module_hash?
see the result no one can change module_hash value... isn't it true?????
In my senario i don't want to give any one access to my database other than my
client. If i connect database via Developer
client, in this case PROAGRAM and MODULE both are null. so if i create a trigger
on logon database which will check
PROGRAM and MODULE both are NULL or NOT ... then i think it should work (My
people here not that expert:)).
See different result:
SELECT SID, SERIAL#, AUDSID, USERNAME, PROGRAM, MODULE, MODULE_HASH FROM
V$SESSION WHERE TYPE != 'BACKGROUND'
SID|SERIAL#|AUDSID|USERNAME|PROGRAM|MODULE|MODULE_HASH
9|3|498|ANWAR|TOAD.exe|TOAD 7.6.0.11|3091199043 ---> CONNECTED VIA TOAD
10|30|499|MLM|tomlplusw.exe|SQL*Plus|3669949024 ---> CONNECTED VIA TOMPLUS WHICH
IS THE RENAMED COPY OF SQL PLUS
12|15|0|SYS|sqlplus.exe|sqlplus.exe|0 -------------> CONNECTED VIA SQL PLUS
CLIENT OF ORACLE DB
13|3|500|OPU|null|SQL*Plus|3669949024 -------------> CONNECTED VIA SQL PLUS
CLIENT OF DEVELOPER 6i
14|11|501|SYSTEM|null|SQL*Plus|3669949024 ---------> CONNECTED VIA CONNECED VIA
SQL PLUS CLIENT OF DEVELOPER 6i
15|6|502|ANWAR|null|null|0 ------------------------> CONNECTED VIA DEVELOPER
RUNTIME (IFRUN60.EXE)
16|8|0|SYS|jrew.exe|jrew.exe|0 --------------------> CONNECTED VIA ORACLE
ENTERPRISE MANAGER
17|30|503|SYSTEM|TOAD.exe|TOAD 7.6.0.11|3091199043-> CONNECTED VIA TOAD
But i got some problem ... on logon database trigger can't retrive module name
:(
i wrote a trigger like:
CREATE OR REPLACE TRIGGER SYS.DENYUSER
AFTER LOGON
ON DATABASE
DECLARE
X VARCHAR2(10);
BEGIN
SELECT 'TRUE'INTO X FROM V_$SESSION
WHERE PROGRAM IS NOT NULL AND MODULE IS NOT NULL AND AUDSID=
USERENV('SESSIONID');
raise_application_error( -20001, 'ACCESS DENIED' );
EXCEPTION
WHEN TOO_MANY_ROWS THEN
raise_application_error( -20001, 'ACCESS DENIED' );
WHEN NO_DATA_FOUND THEN
NULL;
END;
IT WORKS WHEN ANYONE CONNECT FROM TOAD 7.6 OR LATER OR SQLPLUS ORACLE RELEASED
BUT NOT WORKING WITH SQLPLUS
WHICH IS FROM DEVELOPER AND TOAD OLD VERSION WHEATHER I CAN SEE THE MODULE NAME
IS NOT NULL.
Please Help Me how can i protect.
Thanks
Anwar
Followup:
I can set program to *anything* I want.
Likewise I can set module to *anything* I want.
as I've said dozens of times -- you cannot stop me from using sqlplus using this
technique.
But -- not really sure why you say "it doesn't work", everything sets things as
they wish. You should really be looking explicity for things "you want to let
in", not things you don't (as the set of what you DON'T want in is infinite,
whereas the set of programs you desire to have access is small)
So, check for valid "program" names, don't try to look for invalid ones
"as I've said dozens of times -- you cannot stop me from using sqlplus using
this
technique."
Tom, maybe i have lost something here but how can I stop users using tools that
not are our client app. (sqlplus, excel, toad, ...), I'm using 9iR2.
Followup:
what is your client application, and what is your application architecture, that
is extremely relevant in order to get an answer for a question like this ;)
I'm using Oracle 9iR2, W2K and my client application on C# and VB6, dblink
access. (os authentication)
Develop guys are coding a new app. and it will be used by everyone (including
cod guys), so I don't want to deal with "expert users" accessing my production
DB through tools like TOAD, sqlplus, excel, ... I can create a generic user for
those end users or validate access by triggers asking for module or program but
everyone still can access using no client app.
I thought about the set role command, but it works only on current session or
set role default yes/no but how to know when I have to set role default yes/no?
Can you give a hand?
Followup:
what is the reference to "os authentication" about? and dblink access?
securing access to a single client server application is excessively problematic
(you have zero control). Pretty much any scheme you or I come up with will be
defeated by a simple "xcopy toad.exe your_program_name.exe"
are you well set on client server or have you considered a more manageable
tiered implementation where we have lots more options?
Tom - how do you feel about using an ON LOGON trigger to enforce that users
connect through specific os user/ip address combinations? It appears a simple
cross reference using sys_context 'ip_address' and 'os_user' against a trusted
list (custom table) would allow me to enforce this.
For example - only allow SCOTT connections from production host XYZ (via
ip_address 192.169.100.100) via unix account SOME_OS_USER.
Any glaring issues from your perspective?
Great discussion by the way...
Followup:
for ip address, that is a configuration parameter in the network files on the
server.
as for os user, what OS user would you like me to be? I'll become anyone you
want me to! anyone! (it is really easy) If I have access to that unix
server...
(it won't work for DBA accounts by the way, a failed logon trigger does not fail
them logging in)
but yes, it would be easy enough to set up -- it has some holes, but probably is
workable.
Agreed - I'm sure some malicious user with ill intent and the technical know-how
can hack this scheme, but what we're trying to do is put up a reasonable barrier
(with logging) that handles the majority of connection cases. We have a
trustworthy user base, but SO-X compliance dictates that trust isn't enough. We
are required to implement barriers and auditing for our revenue reporting
systems so that users cannot connect from other than a trusted application
(again, IP address, OS user combo). For whatever reason SSL and wallet manager
didn't work out in our POC.
GOTO a page to Bookmark Review | Bottom | Top
A Not too bad method April 07, 2005
Reviewer: Colin
A idea I had that will stop most people is, to allow users to log in without the
privileges to access the data. Write a function in the database that uses some
details of the session to generate a key. This key could depend on things like
the session id, serial# or time of day. The client program could call this
function and get the key. The client program would transform the key using a
fairly complicated algorithm such as shifting bits around. Then the program
would call a PL/SQL procedure that would verify that the key was transformed
correctly and then enable a password protected database role that gives them
access.
I know this wouldn't stop everyone but will stop a lot of people as they would
need to either see the source for the program or disassemble the code.
Followup:
or just enable sqlnet tracing which is a one line addition to a file on the
client...
Unless you were using ASO (advanced security option) in which case that data
would be encrypted.
Sql tracing wouldn't help them much. April 10, 2005
Reviewer: Colin from Australia
Tom,
if the transformation of the key is complex enough turning on sqltracing
wouldn't help them much. Consider, as a simple example where the key being used
was a simple text string such as the sessionid, serial number, and the logon
time. An algorithm that jumbled the characters around such as
instr := '127,5036,12Jan051845';
for c in 1..(length(instr) - 1) loop
ch := substr(instr, c, 1);
ch1 := substr(instr, c+1, 1);
if ((ascii(ch) + ascii(ch)) mod 2) = 1 then
outstr := outstr || ch;
else
outstr := outstr || ch1;
end if;
end loop;
Then outstr would be 127,503,,1JJa001144
(PL/SQLish) Psuedo code for the client application.
key := get_logon_key(); -- Call a database side function to get the key
transkey := localfunction(key); -- Client side implementation of the
transformation algorithm
verifykey(key, transkey); -- Call a database side PL/SQL procedure to verify the
key belongs to this session and if the transformation was done correctly then
enable database access
Although they could see the strings being passed about they would not easily get
the same session id, serial# and logon time when they tried to trick the system
from another client program. Using numbers instead of strings would make the
transformations less obvious.
Basically the client application provides evidence that it is the expected
program because it can transform the key correctly.
Of course you need control of the application source to implement this.
Thanking you for listening,
Colin.
Followup:
Not as they described the process above:
...
The client program would transform the key using a
fairly complicated algorithm such as shifting bits around. Then the program
would call a PL/SQL procedure that would verify that the key was transformed
correctly and then enable a password protected database role that gives them
access.
.......
the client would transform the key.
the client would send the transformed key (the keys to the city if you will)
over the network back to the database to a stored procedure
I only need capture that and I'm in.
Yes I agree the original description was flawed April 10, 2005
Reviewer: Colin
I agree the original passage should have read something like.
Then the program
would call a PL/SQL procedure that would verify that the key belongs to the
current session and that the key was transformed
correctly and then enable a password protected database role that gives them
access.
This is similar to the comment in the psuedo code.
This should make a key used during an application session unusable in another
session. They would need to be able to guess the transformation to get in.
Colin..
Followup:
but I was responding to what they wrote, not what we might envision they could
write.
I'd still say, all I need to know is the CONVERSATION and I can replay it in
sqlplus. You cannot tell if sqlplus is calling your plsql or if your
application is calling plsql, that is the point.
The point is they wouldn't get in because the verify procedure would not grant
them access because the key does not match their session details even though
they have transformed it correctly.
Colin..
Followup:
all i need know is the protocol (and I can see it all)
If your procedure in the database says "if client says right things" all I need
know are the right things to say.
session details, what are those? describe the entire conversation and we'll see
what we see (not saying you are not right, just that after I see the
conversation, I can typically figure out how to have that conversation myself
with sqlplus)
Thank you for your time.
The conversation from the client application could be as follows.
***
Client connects to the database using a username and password creating a
session.
Client calls a server side function to obtain a key from the database server -
no parameters
Database responds '123,546,12Jan051735' -- This information happens to match the
session, serial#, logontime
Client uses a local function to transform the key and calculates
'127,503,,1JJa001144' -- an example transformation
Client request that key and tranformation be verified providing parameters
key='127,5036,12Jan051845', transkey = '127,503,,1JJa001144'
Database verifies that the key belongs to the session and that the
transformation is the expected one, it then enables a database role granting
access.
***
Trying to reproduce the conversation in another session will fail because the
key is derived from the database session and includes the logon time.
Using sqlplus it would be possible to call the get key function to obtain a key
but you would need to be able to do the transformation.
If instead, the sqlplus session called the procedure to verify the key and the
transformation directly (with information from a trace file) the key would not
match the sqlplus session.
The database and the application program need to have an identical
transformation function. i.e. They need to be able to modify the application
Followup:
yes, that might work. as long as the technique for mangling the key was kep "a
secret". Fairly secure.
Database Challenge/Client Response security April 10, 2005
Reviewer: gary from Sydney, Aus
I think I see the logic.
The database server challenges the client with a "one-time only unique
identifier". The client hashes this using a complex algorithm and passes that
response to the database server. If it passes validation there, the client is
deemed to be safe and the database role is enabled.
The 'conversation' cannot be replayed because the server side 'question' is
never the same and so the client side 'answer' would never again be correct.
One flaw is :
You start your 'conversation' with the database from your pirate client, and get
its initial 'challenge' (You may need to get the 'safe' client to request the
challege from the 'pirate' client instead of the real database if that request
needs validation).
Your pirate client passes the challenge to the 'safe' client and receives the
'safe' clients reponse and then the pirate client can pass that back to the
database.
The benefit is that, if the role(s) enabled by the mechanism only limit the
session to perform transactions that would be open to that 'safe' client anyway,
nothing has really been lost. While the 'safe' client has been bypassed, it
hasn't allowed the user to do anything that they couldn't have done through the
'safe' client anyway. The 'safe' client must have been available at the time,
and any audit should point to that client at that time.
Potentially you could expand the concept to securing individual transactions
with a challenge to verify that a safe client has generated it.
The 'conversation' would be on the lines of
Client : I want to do a customer update on client 1234
Database : This is your unique one-time only key for your next customer update
(eg a sequence number that the database records was requested for a customer
update to 1234)
Client [after Hashing key using database sn and client number 1234] : This is my
customer update and the hashed key verifying that I generated it.
Database : I have checked that a safe client has requested an update to customer
1234. Change accepted.
Whether coding all that is cost-effective is a different matter. It does smack
of being a massive headache of updating clients if the security mechanism is
ever broken and needs to be amended.
GOTO a page to Bookmark Review | Bottom | Top
The one flaw. April 14, 2005
Reviewer: Colin from Australia
Gary,
the flaw you described is possible but I feel sufficiently difficult to exploit.
Because, the client program only does exactly what it is programmed to do you
would need to create a bogus database for it to communicate with and write a
version of the PL/SQL get_key function to provide values.
That sort of effort (i.e. creating a database and writing a PL/SQL function) to
crack the system would be difficult for most people, except for programmers and
dba's. Since the method can be cracked anyway by disassembling the client
program I don't see that as too much of an extra vulnerability.
However, I would suggest that the following would help strengthen the security.
1. Time expire the offer. If the validate_key procedure is called after more
than say 10-20 seconds after logging on don't enable access. (slow networks
might be a problem but I wouldn't suggest running client server apps on slow
networks).
2. During the verify, check the client program name as well.
3. Log any failures. It is unlikely that they will get the solution right the
first time. If they are detected, it would be difficult for them to suggest they
didn't know it wasn't allowed.
The method you suggest of having multiple challenges would work well. As you
also suggested the additional effort required may make this not worth pursuing.
GOTO a page to Bookmark Review | Bottom | Top
What about client machine/terminal? November 01, 2005
Reviewer: A reader
Tom,
So program/module/os user can all be spoofed. What about the machine and
terminal name? Can they be spoofed as well? If yes, then you're really saying we
can not rely on the info in v$session/v$sql to establish who logged on to do
what?
Thanks!
Followup:
I would assume that machine and terminal could be "spoofed", sure. It is just
information provided by the client information to the database.
OS User is not 'spoofed' so much as you have allowed someone to create users in
your security domain somewhere. You have the actual OS User - you just might
not have any idea who the user behind the OS user really is.
You can rely on the WHO logged in part (database user). Not sure why you said
that?
You can rely on the WHAT they are doing as well.
Tom,
Can you please provide some comments and sugestion regarding the new worm aimed
to attack Oracle database? Can you provide some useful tips on this. Thank
you!
http://news.zdnet.com/2100-1009_22-5926641.html
Followup:
already did....
advice:
change default passwords for accounts???!?!??!
that is, don't let strangers just log into your database???
system/manager -> bad....
scott/tiger -> bad....
sort of like having guest/guest as a user/password on your system?
Tom,
I have oracle 8i and 9i databases. How can I know that ASO has been installed on
these?
Thanks.
GOTO a page to Bookmark Review | Bottom | Top
December 18, 2005
Reviewer: Nishith Pandey from India
Hi Tom
We have a big problem. We have Forms6i/Reports6i and Oracle Database 10gR2. We
want to enforce our users to run the latest forms(6i) or reports(6i) provided by
us. Can we prevent the selection/insertion/updation/deltion through Older forms
or running the older reports?
One way i thought was that we set the module=form/report name and
client_info=version number in our new forms/reports through
dbms_application_info. Then through some trigger in database(10g), we match the
client_info(version) of that module(form/report) with the records from a table
containg the form/report name and its latest version. if mismatched, we prevent
the select/DML. How to do this? Can we disconnect the session in this situation
in any way?
Please provide us the appropriate solution. Thanks in advance!
Followup:
You "could" do that, but if I wanted to - I would just rename the form.
Sounds like you've put lots of "data logic" (rules, security, etc) into the form
:( And now if people just run old forms, they'll corrupt your data.
You could use secure application roles. Your form will call a procedure that
will enable roles after verifing the the client has the right version burnt into
it.
That way - if the form doesn't call this procedure (legacy clients), it won't
work (the form won't, no privileges).
And new forms will only work if the procedure in the database is happy with the
version they say they are.
revoking privileges from the owner December 19, 2005
Reviewer: Nishith Pandey from India
Very Good Idea ! Thanks a lot Tom :)
Did you mean that applicaton_role(for select/DML) should not granted by default
here? Only after the user login through application, and we match the version
from the table through database stored procedure, then only the
application_role be granted!
We also want that even if the USER is the owner of the table, if his version is
not correct, he should not be able to select/DML on his own table too!
Can we also revoke the select/DML/DDL on the objects(tables, etc.) from the
owner of the object(directly or via a role)?
Followup:
you grant the privileges to a role.
the role is secured by a procedure (secure application role), the only way to
get role enabled is to run procedure.
The user should NOT be the owner of the table - it is that simple. That schema
shouldn't be used, they can do anything they want. It is a matter of securing
your application and running things as the schema owner is the last thing you
want to do.
Hi Tom
I must say that AskTom is really saving hours of bad practices and frustations
of the developers/DBAs. Our affection with Oracle is increasing with every page
viewed on this site and also by quick followups we receive from you.
Thank You So Much for being there for Us :)