Skip to Main Content
  • Questions
  • How do i prevent end users from connecting to the database other than my application?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Hetal.

Asked: August 16, 2000 - 3:46 pm UTC

Answered by: Tom Kyte - Last updated: July 11, 2019 - 2:46 pm UTC

Category: Database - Version: 8.1.5

Viewed 50K+ times! This question is

You Asked

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
</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>
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...

and you rated our response

  (90 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

March 21, 2002 - 1:47 pm UTC

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?

Tom Kyte

Followup  

March 21, 2002 - 5:08 pm UTC

Try this


copy sqlplus.exe somethingelse.exe

and see what happens.

March 21, 2002 - 7:33 pm UTC

Reviewer: mark from ca

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

what about non-default roles with passwords?

March 22, 2002 - 1:55 am UTC

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?

Tom Kyte

Followup  

March 22, 2002 - 9:18 am UTC

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).

sorry for persistence

March 25, 2002 - 6:20 am UTC

Reviewer: Alexander Rakhalsky from Moscow, Russia

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.

Tom Kyte

Followup  

March 25, 2002 - 8:15 am UTC

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 undestand

March 25, 2002 - 9:21 am UTC

Reviewer: Alexander Rakhalsky from Moscow, Russia

I don't understand, how can ON-LOGON trigger determine, is connection made trough application or SQL*Plus?

Tom Kyte

Followup  

March 25, 2002 - 12:28 pm UTC

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.



thank you

March 26, 2002 - 1:34 am UTC

Reviewer: Alexander Rakhalsky from Moscow, Russia

Thank you, Tom, for your quick feedback.

How can you prevent a User connecting to the Database Using ODBC?

March 26, 2002 - 3:20 am UTC

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

Tom Kyte

Followup  

March 26, 2002 - 7:51 am UTC

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.

One more useful method..

March 27, 2002 - 8:40 am UTC

Reviewer: Kiran Kumar Srirama from India

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

Tom Kyte

Followup  

March 27, 2002 - 10:07 am UTC

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.

Your method is not so flexible

March 28, 2002 - 7:55 am UTC

Reviewer: kiran kumar srirama from India

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



kiran kumar srirama [sorry you're defeated] look this!

March 17, 2003 - 3:59 pm UTC

Reviewer: Marcio from Brazil

ops$mportes@MRP816> grant create session to comm identified by comm;

Grant succeeded.

ops$mportes@MRP816>
ops$mportes@MRP816>
ops$mportes@MRP816> @conn comm/comm
comm@MRP816>
comm@MRP816>

-- In 5 seconds I could execute this at least 5 times ;)

comm@MRP816>
comm@MRP816> exec dbms_application_info.set_module( 'MyApp*Plux', '' );

PL/SQL procedure successfully completed.

[In another session where I have permission to see v$session]

ops$mportes@MRP816> 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]

comm@MRP816> exec dbms_application_info.set_module( 'SQL*Plux', '' );

PL/SQL procedure successfully completed.

[go see v$session]

ops$mportes@MRP816> /

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.

ops$mportes@MRP816>

See -- Your app was broken and any mortal user could do that ;)


does userenv('isdba') work?

December 28, 2003 - 8:47 am UTC

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?

Tom Kyte

Followup  

December 28, 2003 - 11:27 am UTC

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.

looks like a similar thread to ..

December 28, 2003 - 12:01 pm UTC

Reviewer: Sudhir

How can I lock a user account ?

January 27, 2004 - 9:58 am UTC

Reviewer: A reader


Tom Kyte

Followup  

January 27, 2004 - 10:35 am UTC

ops$tkyte@ORA920PC> alter user scott account lock;
 
User altered.
 
ops$tkyte@ORA920PC> connect scott/tiger
ERROR:
ORA-28000: the account is locked
 

Thanks !!!

January 27, 2004 - 2:17 pm UTC

Reviewer: A reader


How to allow only forms server and support staff access?

September 23, 2004 - 6:50 am UTC

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'?

Tom Kyte

Followup  

September 24, 2004 - 8:14 am UTC

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 - 8:46 am UTC

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).

Tom Kyte

Followup  

September 24, 2004 - 11:24 am UTC

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"



i just want to log some details

October 18, 2004 - 8:08 am UTC

Reviewer: Naveen.C from India

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?

Tom Kyte

Followup  

October 18, 2004 - 9:06 am UTC

yes, it can. just create an after logon on database and you can log whatever you like.

i just want to log some details

October 18, 2004 - 8:16 am UTC

Reviewer: Naveen.C from India

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?

Is it possible to prevent user from connecting database with different user in SQL Plus ?

October 18, 2004 - 12:08 pm UTC

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/parag@test . 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


Tom Kyte

Followup  

October 18, 2004 - 1:50 pm UTC

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 - 4:27 pm UTC

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


Tom Kyte

Followup  

October 26, 2004 - 5:36 pm UTC

Never tried it -- interesting concept.  but no

ops$tkyte@ORA9IR2> !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. 

What about this?

October 27, 2004 - 2:47 am UTC

Reviewer: j.

... 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 ;
/

Tom Kyte

Followup  

October 27, 2004 - 7:42 am UTC

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 j

October 27, 2004 - 5:57 am UTC

Reviewer: Dave

to keep scott out

alter user scott account lock;

No way he can get in then - why write code when you dont have to

to Dave

October 28, 2004 - 3:02 am UTC

Reviewer: A reader

i just thought of some kind of a "conditional" account lock ;o)

INTERESTING FINDINGS FROM V$SESSION TO PROTECT UNWANTED ACCESS TO DATABASE

October 30, 2004 - 4:50 pm UTC

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


Tom Kyte

Followup  

October 31, 2004 - 2:45 am UTC

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

the right way?

February 17, 2005 - 1:55 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

"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.

Tom Kyte

Followup  

February 17, 2005 - 2:30 pm UTC

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 ;)

deny unwanted access

February 17, 2005 - 3:43 pm UTC

Reviewer: Jairo Ojeda from Costa Rica

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?

Tom Kyte

Followup  

February 17, 2005 - 7:01 pm UTC

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?

What about this approach?

February 17, 2005 - 3:58 pm UTC

Reviewer: Dan from Raleigh, NC

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...

Tom Kyte

Followup  

February 17, 2005 - 7:05 pm UTC

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.

RE:What about this approach?

February 18, 2005 - 10:23 am UTC

Reviewer: Dan from Raleigh, NC

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.

A Not too bad method

April 07, 2005 - 10:16 pm UTC

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.



Tom Kyte

Followup  

April 08, 2005 - 6:51 am UTC

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 - 1:52 am UTC

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.


Tom Kyte

Followup  

April 10, 2005 - 9:51 am UTC

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 - 8:05 pm UTC

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..

Tom Kyte

Followup  

April 10, 2005 - 8:26 pm UTC

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.

Not sure.

April 10, 2005 - 8:43 pm UTC

Reviewer: Colin from Australia

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..

Tom Kyte

Followup  

April 10, 2005 - 9:04 pm UTC

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)

The conversation

April 10, 2005 - 9:44 pm UTC

Reviewer: Colin from Australia

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



Tom Kyte

Followup  

April 11, 2005 - 8:32 am UTC

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 - 9:46 pm UTC

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.

The one flaw.

April 14, 2005 - 12:37 am UTC

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.


What about client machine/terminal?

November 01, 2005 - 12:38 pm UTC

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!



Tom Kyte

Followup  

November 02, 2005 - 4:49 am UTC

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.




Recent Worm

November 04, 2005 - 11:03 am UTC

Reviewer: A Reader from USA

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!

</code> http://news.zdnet.com/2100-1009_22-5926641.html <code>


Tom Kyte

Followup  

November 04, 2005 - 5:16 pm UTC

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?

ASO installation

November 11, 2005 - 8:51 am UTC

Reviewer: Bobby

Tom,

I have oracle 8i and 9i databases. How can I know that ASO has been installed on these?

Thanks.

December 18, 2005 - 7:02 am UTC

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!

Tom Kyte

Followup  

December 18, 2005 - 10:48 am UTC

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 - 3:52 am UTC

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)?



Tom Kyte

Followup  

December 19, 2005 - 7:35 am UTC

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.

Best Support :)

December 20, 2005 - 5:51 am UTC

Reviewer: Nishith Pandey from India

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 :)


Unbreakable huh:)

January 18, 2006 - 6:24 pm UTC

Reviewer: Oracle_Hacker from USA

Tom,

What is your comment on this?

</code> http://www.theregister.co.uk/2002/01/16/oracle_security_claim/ <code>


Non-DBA to change database password

May 15, 2006 - 10:12 pm UTC

Reviewer: Reader from S. Africa

We have applied password security based on the /oracle/product/9.2.0/rdbms/admin/utlpwdmg.sql. We did some revision on the script to reflect our own policy. Lately, Helpdesk is receiving calls on users being locked (after 3 failed attempts) and being forwarded to our DBAs on call. Is there a way that non-DBA like Helpdesk personnel change the password but without giving them too much privilege?

We're thinking of providing them "alter user" privilege and creating a oracle forms gui window JUST for the helpdesk to unlock or change a user's password, nothing more. How do we achieve this?

Tom Kyte

Followup  

May 16, 2006 - 6:56 am UTC

Encapsulate the logic in a stored procedure - performing whatever checks you deem necessary (eg: helpdesk should not be able to modify SYSTEM - or perhaps any account with DBA, or maybe they can only modify accounts listed in some table - whatever you deem correct and proper for your installation. Add in all of the auditing/whatever - perhaps audit by using an OS file or email so that if they do become a DBA and could wipe out the audit trail in the database - they cannot since the audit trail is not there)

Grant them execute on this procedure.

Call this procedure from any GUI you want.

About put your application in the database.

August 31, 2006 - 3:32 am UTC

Reviewer: Dulal from Dhaka

Hi Tom,

Earlyer you told "The best method, in my opinion, is to put your application in the database." at the begging of this topic.
I don't understand how I will do that.
Please give a short discussion.

Tom Kyte

Followup  

August 31, 2006 - 9:11 am UTC

easy:

stored procedures.




Securing Oracle Foundations, SANS

October 08, 2006 - 7:29 pm UTC

Reviewer: Dawar Naqvi from Los Angeles, CA USA


Hello Tom,

Beginning of 2006, I had attended SANS Conference in Orlando.
Overall, It was a very good learning experience.

I attended "Securing Oracle, SECURING 509" course.

We also got the reading material. (six Books material)

Ref:
"Securing Oracle Foundations" 509.1 book, page number 3-5 (Last paragraph).

It says “We might find that the data dictionary is accessible because the parameter 07_dictionary_accessibility is set to TRUE. This allows any user with the privilege SELECT ANY TABLE to also view dictionary tables. Up to Oracle 9i, this was a default installation issue. ... "

And on page 3-10 (Second paragraph)it says

“An example is that the parameter 07_dictionary_accessibility was automatically set to FALSE in versions of Oracle before 9i. When this was the case, any user that had the SELECT ANY TABLE system privilege could also see dictionary tables."


In my opinion there is a contraction between two statements,

If 07_dictionary_accessibility is set to TRUE.
This allows any user with the privilege SELECT ANY TABLE to also view dictionary tables.

OR

07_dictionary_accessibility was automatically set to FALSE in versions of Oracle before 9i.When this was the case, any user that had the SELECT ANY TABLE system privilege could also see dictionary tables."


I have feeling this is a typo error.
Or probably my understanding is different.

Please give your feed backs.


Regards,
Dawar Naqvi
California, USA


Tom Kyte

Followup  

October 09, 2006 - 8:18 am UTC

peek at the doc:

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams134.htm#sthref572

... 
O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in the SYS schema.
.....

they meant to say "was defaulted to a value of TRUE prior to 9i..."
http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1114.htm#56859 <code>

what is that - a security bug in a security conference set of proceedings :)

Executing Stored procs with Invoker rights

April 30, 2007 - 10:11 am UTC

Reviewer: Sunil

Hi Tom,

Is it possible to execute a stored procedure with Invoker rights than the procedure's owner rights? We have an issue when our application wants to change the connected user's password, since the stored procedure that executes 'ALTER USER... ' sits in the applications' schema. We dont want to give alter user privilege to the application owner, any other way the connected user can change his own password executing a stored procedure in a different schema?

thanks,
Sunil.
Tom Kyte

Followup  

April 30, 2007 - 3:23 pm UTC

create the stored procedure with invokers rights and it will be invoked with invokers rights

not sure what you are asking? if you are asking "can we take an existing definer rights routine and instantly just call it as if if were compiled with completely different options? making it an invokers rights routine? "

the answer to that is "no" (obvious security ramifications, forgetting the "compile me on the fly" issues)

May 01, 2007 - 9:41 am UTC

Reviewer: A reader

Tom,

Please see my scenario below:
SQL> create user foo identified by p;

User created.

SQL> create user foo_user identified by p;

User created.

SQL> grant create session to foo,foo_user;

Grant succeeded.

SQL> grant create procedure to foo;

Grant succeeded.

SQL> conn foo_user/p
Connected.
SQL> -- here user can change his own password without any extra permission
SQL> alter user foo_user identified by q;

User altered.

SQL> conn foo/p
Connected.
SQL> -- I'm wrapping this in a procedure inside foo user
SQL> create procedure temp as
2 begin
3 execute immediate 'alter user foo_user identified by q';
4 end;
5 /

Procedure created.

SQL> grant execute on temp to foo_user;

Grant succeeded.

SQL> conn foo/p
Connected.
SQL> execute foo.temp;
BEGIN foo.temp; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "FOO.TEMP", line 3
ORA-06512: at line 1

It shows insufficient privileges because the stored proc resides in foo and it is trying to change password for foo_user. We dont want to give ALTER USER privilege to foo. Here, our application sits in foo but user connects as foo_user. We are trying to give own password change facility to foo_user, the problem is the stored procedure for this sits in foo. Any way of doing this?

thanks,
Sunil
Tom Kyte

Followup  

May 01, 2007 - 10:14 am UTC

well, you connected as foo/p at the very end - but the results would have been the same because you defined a DEFINER rights routine

I suggested for you to create an INVOKER rights routine. That would work to allow foo_user to run foo.temp to alter their own password

ops$tkyte%ORA10GR2> create user user1 identified by user1;

User created.

ops$tkyte%ORA10GR2> create user user2 identified by user2;

User created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant create session, create procedure to user1;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session to user2;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect user1/user1
Connected.
user1%ORA10GR2> create or replace procedure change_pw( p_new_pw in varchar2 )
  2  AUTHID CURRENT_USER
  3  as
  4  begin
  5          execute immediate
  6          'alter user ' || user ||
  7          ' identified by ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_new_pw);
  8  end;
  9  /

Procedure created.

user1%ORA10GR2> grant execute on change_pw to user2;

Grant succeeded.

user1%ORA10GR2>
user1%ORA10GR2> connect user2/user2
Connected.
user2%ORA10GR2> exec user1.change_pw( 'foobar' )

PL/SQL procedure successfully completed.

user2%ORA10GR2> connect user2/user2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
user2%ORA10GR2> connect user2/foobar
Connected.
user2%ORA10GR2>

May 01, 2007 - 9:46 am UTC

Reviewer: A reader

SQL> conn foo_user/p@semid
Connected.
SQL> execute foo.temp;
BEGIN foo.temp; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "FOO.TEMP", line 3
ORA-06512: at line 1

Tom Kyte

Followup  

May 01, 2007 - 10:15 am UTC

see above

May 01, 2007 - 10:48 am UTC

Reviewer: A reader

Just what i was looking for.

thanks Tom.

Password encryption on net

December 13, 2007 - 12:04 pm UTC

Reviewer: Tony from Canada

First of all thanks a lot for all your help, my question is:

1)how JDBC/Oracle OCI transfer the passwords over net

2)how can I encrypt the ID and Password going over the network from the Application server to the Database server
Tom Kyte

Followup  

December 13, 2007 - 12:28 pm UTC

1) passwords are encrypted
2) done - the password in case.

the entire conversation can be encrypted by using the advanced security option (ASO) - the entire conversation can be encrypted.

March 06, 2008 - 10:34 am UTC

Reviewer: Alexander the ok

Tom,

Can you please explain why this could happen when attemping to lock a user?

ALTER USER IRSBTCH
*
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/ocp19p/undo/undotbs01.dbf'
ORA-27092: size of file exceeds file size limit of the process Additional information: 262143 Additional information: 2328321

I have 5.5 gig of undo free....
Tom Kyte

Followup  

March 07, 2008 - 4:55 pm UTC

one possibility:

you have a ulimit set on your account

you are not using the network to connect

so your oracle backend process (dedicated server) inherits your ulimit

and that file is bigger than your accounts ulimit settings permit.

and your dedicated server tried to read that undo tablespace file - and failed.


[tkyte@localhost ~]$ ulimit -f 1024
[tkyte@localhost ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) 1024
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 8192
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[tkyte@localhost ~]$ plus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 7 16:56:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> @free
Wrote file /tmp/xtmpx.sql
Enter value for 1: 1
old  30: order by &1
new  30: order by 1
           from sys.dba_temp_files
                    *
ERROR at line 27:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/ora10gr2/oracle/product/10.2.0/oradata/ora10gr2/system01.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 128
Additional information: 62721

March 10, 2008 - 10:30 am UTC

Reviewer: Alexander the ok

Thank you very much. Cron was running this in a script. We su to oracle, which is the same account as the dedicated server process. I will have to test the ulimit when the script runs.

Also, I wasn't sure why would locking an account touch undo at all?
Tom Kyte

Followup  

March 10, 2008 - 11:47 am UTC

everything performs transactions.

Locking an account has to update the dictionary.

update the dictionary generates undo.

querying the dictionary READS undo (in general)

everything - read or write - needs undo.

block user

August 10, 2008 - 5:41 pm UTC

Reviewer: A reader

We have a PB appliacation that connects to an oracle database. Currently app is version 1.0. We are planning to upgrade client software to 2.0 next month.

Currently, we track the client software version number and userid in an oracle 9i table. Users have to synchronize software for the 2.0 the upgrade. However, we want to control this at the DB level where if a user with Version 1.0 connects it will block him completely from using application until he is forced to upgrade the machine to 2.0. Currently all users use one DB connection from the PB client to the database. This db account is also used by other web apps that we do not want to be affected.

1. Is there a way to block users from using Ver 1.0 after we go live from the DB side instead of adding more code in the PB client to check the software on machine versus software defined in table before blocking connection. This is locking the user by the client i assume.

Would you use "after logon trigger" only for that application name. However, after upgrading softare on PB client we need to update the DB. so the 1.0 machine needs still to connect to do that unless we do it using a different EXE or application name before the main one runs.

2. currently we use login userid (stored in table) to check the software version. However, should not we really check machine or host name since one user may use many machines?

3. is this correct? with current design we can't track user activity in oracle and see who did what, since we only have one DB account. Is there a way to do this. Like tracking the session id and userid and then query V$session to see the activity.

Tom Kyte

Followup  

August 12, 2008 - 8:11 am UTC

1) I cannot answer that - only you can tell if YOU can identify a connection as being "version 1" of your application.

I guess one thing you could do is to make it so your roles are NOT assigned to the connecting schema by default, but rather have to be enabled via a stored procedure. that way, version 1 would "lose" access to everthing - and version 2 would know to call this procedure with its version number.

The schema would have direct grants only to be able to "upgrade itself", it would lose all other privileges - version 2 would be able to enable these privileges by calling a stored procedure right after connecting. And in the future, when version 3 comes out - you would change the stored procedure and make it so that it doesn't enable them for version 2, only for version 3.

So, create non-default roles and use them - enable them only via a stored procedure.

2) really, only you can answer that - it is your application. If you use the approach above in #1, you would not have to check anything - the application would not work with version 1 because it would only have privileges to do the upgrade step - regardless of user, or machine.

3) if you use dbms_session.set_client_info and dbms_sesssion.set_identifier immediately after logging in, you'll have some visibility into who does what.

set_client_info data will appear in v$session, set_identifier information will appear in the audit trail.

connection

August 13, 2008 - 7:45 pm UTC

Reviewer: A reader

Tom:

I think your method with roles requires some DBA privilege which i do not have. i am also not clear on how to implement it.

would this work using oracle and is it a good solution?

1. Create a new table (machine name, current version, new version)

2 Create an after login trigger on database that gets host and application name from which connection is being established. If record does not exist, block user from connecting to DB.

If record exists, check if new version matches old version are same for that host.

3. From version 2.0 of application, create another small EXE that has different name than current application before user connects to DB. This simple application will insert/update the record in that table for that host with new version. So the trigger will not prevent this application from connecting to update the version since application name is different from same host.

4. After this, when the new application connects to database the trigger will again check host name and application name and will find a record in table and will allow to connect and execute.



Also, do you agree that version control/check should be at server or you would simply add a a new check to the client code after user enters userid/password

If soft_version_in_db <> '2.0' Then
Messagebox('Invalid Version','Client version in not latest. Please update client version.')
End If
Tom Kyte

Followup  

August 18, 2008 - 9:52 am UTC

You should then

a) work with your DBA
b) understand it......

that would be my approach.

I cannot tell if your approach will or will not work - it is YOUR application, after you study this, can you tell us if it will work.


My approach simply was

a) make the role activated only by a stored procedure (see sql reference guide on the ROLE command - create, alter, etc - it is documented)

b) this role will NOT BE ACTIVE anymore by default (so version 1 application now FAILS, it has no privileges)

c) version 2 of the application enables it by calling stored procedure and saying "I am version 2"


in the future, when version 3 rolls out - you change the stored procedure so that it does not enable the role for version 2 and the same thing happens...

application versioning

August 15, 2008 - 3:10 pm UTC

Reviewer: Jason Martin from Snohomish, WA

It is reasonable to put a version check in a client application -- have it check if its version matches the DB version and show an error if that is the case. This isn't a security issue so much as a deployment issue.

Either way the database should enforce that user X can only perform authorized actions, so it won't really matter what client the users logs in with.
Tom Kyte

Followup  

August 20, 2008 - 8:50 am UTC

... It is reasonable to put a version check in a client application ...

sure it is.

version

August 18, 2008 - 12:50 pm UTC

Reviewer: A reader

Tom:

I will check on the role way of doing it with DBA. My preference was to not involve him as he is remote and does not fully understand oracle db.

But some general questions on client/server:

1. Do you think that this is a security issue and the oracle server should be doing this validation or the client can still do it? it could be just deployment issue!

2. I cant see how an after logon trigger will get the hostname and application and software version from? Is this submitted by client like username/password or something that oracle can figure out from connection.

The client is currently logging in using one oracle account defined in TNSNMES.ora that many other applications use too. How would oracle know what version the client is running at.

3. IT seems to me the powerbuilder client is doing the authentication.

user enters userid/password and a sql statement is sent to oracle to check if record exists. but then the client decides to allows the login based on the results of the sql statement. is not this client authentication and should not this be oracle server authentication instead? How would you change it.


Tom Kyte

Followup  

August 20, 2008 - 10:09 am UTC

... I will check on the role way of doing it with DBA. My preference was to not
involve him as he is remote and does not fully understand oracle db.
...

then they are most certainly NOT a DBA - find a real DBA and involve them.


1) it is purely a deployment issue, I see nothing 'security' relevant here at all.

2) I didn't say it would, I didn't suggest a logon trigger.

we do not define accounts in tnsnames.ora - that is absolutely an inaccurate statement.

3) no, the power builder application supplies credentials to the database and we do the authentication.

The powerbuilder application must be using a common username and password - that stinks, doing that in client server is a horrible idea - THAT is a security issue (it would take me about 5 milliseconds to get that user and password that must be hard coded into the application and then I'm in using sqlplus and doing whatever I want)

use Oracle Advanced Security or Before LOGON TRIGGERS

August 20, 2008 - 3:52 am UTC

Reviewer: Dhairyasheel Tawade. from India- Mumbai.

HI,
We faced similar problems of stopping user access thru sqlplus etc.
What we did was set up oracle advanced security on the database server and set the level to 'required'
we went and did the same on the application server(oracle client installation) wherein we fed the same encryption key as on the DB server so that only the application server could connect to the database server.
Now the users could use the applications as app server could connect to the DB server but they could not connect through any other MACHINE because the encryption was not set on it.

Note, we had a three tier architecture i.e. db server - appserver - client and all our appliation were browser based.

The other way is to create before logon triggers. What we did was to store some authentic programs like sqlplus , jdbc client which we wanted to connect to DB in a table and before every logon on the DB it would fire a select in query to determine the person logging on was doing so by the pre-approved methods.

However I Wish to stress that in an Enterprise, the first thing to do (before doing any technical stuff) is to form a enterprise wide policy restricting user access, getting it approved by the higher authorities, propogating the policy to the users and then reporting the defaulters. This will help the DBA or admin group avoid being blamed for any violations.
Tom Kyte

Followup  

August 20, 2008 - 10:36 am UTC

you could have just set up a sqlnet.ora with a list of valid IP addresses that are allowed to connect as well.


connection

August 20, 2008 - 2:05 pm UTC

Reviewer: A reader

Tom:

User use their own userid/password to use the application.
Do you mean that you can read the userid/password from tnsnames.ora and connect to the DB from ay client tool.

that is true. but how do you prevent that unless you create one DB account per user.

Is there a way to hide the tnsnames.ora or encrypt it?

2. Should not the server be doing the authentication. The way i think of it is:

User keys in userid/password. PB sends that with software version on machine to an oracle stored procedure.

Then oracle will check the software and userid/password and either raise an error "invalid login" or let the connection through.

This way oracle server controls the login. If PB sends the SQL and then checks what comes back, PB is doing the authentication based on whether that account exists or not.

Are you implying that both ways can be implemented and this is not security issue.
Tom Kyte

Followup  

August 21, 2008 - 8:25 am UTC

the application logs into the database - using a common username and password - a MASTER "ALL POWERFUL" one - one that can do anything.

how else would it query this magic table with everyones credentials to verify a user/pass combination.

It would take no time at all for me to get that user/pass as a client, if you install that program on my machine, I will have that master user/password and will use sqlplus to - to do whatever I felt like.


the tnsnames.ora DOES NOT HAVE ANY USER INFORMATION AT ALL - NONE - look at one, it simply describes where a database is, it has nothing to do with users.

hiding the tnsnames.ora would do *NOTHING*

what is wrong with actually identifying users in the database???? Yes, I would, I have, I will create an account per user in many cases - especially a client server one - ESPECIALLY a client server one.


2) yes it should, but IT ISN'T really. The database authenticates that common user/password - the master userid, but then your application 'authenticates' the user.


PB is not sending - the program you wrote sends it. What you describe is NOT ORACLE CONTROLLING THE LOGIN - that would require

a) use that master user/password to login (i'll get that one easily, it is trivial)

b) run YOUR CODE

c) your code either succeeds or fails


Oracle will verify (a), you did the rest and the database has nothing to do with it.


You have the most insecure implementation right now.

server

August 23, 2008 - 9:45 am UTC

Reviewer: A reader

Tom:

Can you summarize how you move from this "insecure" to "secure" client/server environment.

1. Is it only done by creating one database account per user and using a different account for each client to connect to DB? is there any other ways to accomplish this if we continued with storing userid/password in app table and having one common oracle account?

2. you said that you can find out the username/passwword in a few minutes on the client and login using sql*plus. How do you find that out? using a network sniffer?

3. if you have PB app on remote site is it connecting to DB via Ora*Net protocol running over tcp/ip through the internet.

Tom Kyte

Followup  

August 26, 2008 - 7:59 pm UTC

you start by not having a password hard coded into the client application

which implies each user does have their own account, yes, in client server.

2) I'm not going to say, but suffice to say that anyone that has ever played with sqlnet would get it very fast - it is rather easy and doesn't involve a sniffer.

3) yes, probably, unless they wrote their own networking "application server" that you are running, but if you were - you would probably know about it.

August 26, 2008 - 10:44 am UTC

Reviewer: pmdba

Having a single db account with app accounts stored in a table is a recipe for disaster. Are your app account passwords encrypted in the app_user table? When the client app submits the app username/password is it being encrypted over the network?

If your master db password is stored somewhere in the client application, I will find it using 'strings' or some other binary file viewer (wordpad even works, on some apps - that's how easy this is to defeat). I can then gain access to the db using sqlplus and completely bypass the app security. Now I can read the app_users table and any stored passwords there, assuming I can't just get directly at the application data tables.

You're essentially handing out the keys to the cookie jar and hoping that the end user it too stupid to realize it. Seems like a completely unnecessary risk. One db account for each user for client-server is the only way to go. Leave NOTHING in the client that would give an attacker a toehold.

Using Valid Node Checking or Oracle Connection Manager is a great way to limit which machines have access to the db as well. OCM can even limit by service name, which can also come in handy if you're supporting multiple applications out of the same database instance.

Also a good idea to use Advanced Security to encrypt your communications, especially if you're on an open or public network. Otherwise you're just transmitting all your information, including the user's application (not database) password in the clear, along with all the rest of your sql commands and data.

Lastly, audit everything! If someone does get in, you need to know who, what, when, where, and how they did it.

authentication

August 26, 2008 - 10:55 pm UTC

Reviewer: A reader

The above is really excellent. So with binary file viewer or sqlnet i can figure out the DB userid/password and i can then log in to the DB.

Let us say we create one DB account per user and give it to each user.

Now, the user will key this in, can't someone with sqlnet see this too? is this where advanced security encrypts the traffic and secure the communication.

Can't the same user use sql*plus and access the DB tables directly?

is there a good book on how to secure client/server apps with oracle or that is covered under database security.



Tom Kyte

Followup  

August 27, 2008 - 8:27 am UTC

getting the information out of the powerbuilder binary is pretty easy - yes.

it is covered under database security - when you do security yourself, you are on the hook to do everything *everything* yourself. You are not currently even using Oracle to secure a thing, you do identification and authorization. A book on how to secure your application with Oracle wouldn't help since you are not using any of our stuff.

authentication

August 27, 2008 - 11:57 am UTC

Reviewer: A reader

Tom:

I think you are saying that we need to implement "Database security" and "application security" and communication between the two. The first one is related to oracle. The second has nothing to do with oracle and only related to the development language used. communcation is related to protocol used (oracle client) which is ORA*NET and is oracle now.

is this correct?
Tom Kyte

Followup  

August 29, 2008 - 12:44 pm UTC

more simply: implement a secure application.

forget about client, database, sql*net (ora*net is not a 'thing') - just think about HOW the application works here. Is it a) secure or b) not secure.

Trust, but not too much

August 27, 2008 - 8:53 pm UTC

Reviewer: Gary from Sydney, Aus

"is there a good book on how to secure client/server apps with oracle or that is covered under database security."
A good book on Oracle security is the "Oracle Hacker's Handbook' by David Litchfield.
The intrinsic security problem with client/server is that you don't control the client. People can (often) run their own software, copy application code off the client, plug in their own laptop.... So the only security here is database security (ie you should limit the users to transactional APIs for which they personally are authorised).
N-tier allows those transactional APIs to be coded in the middle tier instead of the database. The assumption being you have control over the mid-tier so it is more trusted. Better yet, have the mid-tier APIs acts as wrappers for database transaction APIs, so you are trusting your mid-tier, but not entirely.

Tom Kyte

Followup  

August 29, 2008 - 1:29 pm UTC

... N-tier allows those transactional APIs to be coded in the middle tier instead
of the database. ...

and that decreases security :(

they belong in the database, close to the data - where they can be used and reused over and over again.

Put them in the middle tier and pretty soon you have 15 copies of the same "security code" here there and everywhere - and you make it so the data is ONLY available via the application - not good since applications come and go so so quickly.

Never trust the man in the middle, my take on it.


If the client/server application had been built

a) using DATABASE ACCOUNTS
b) and stored procedures (no access to tables)

and you are pretty darn secure. You do NOT need (or if you ask me - always desire) 3 physical tiers. You have your data tier (tables), you have your transactional API (stored procedures) written in perhaps the single most "open" fashion ever - anything that can connect to the database (pretty much everything) can run them, you have your presentation - the client.


And frankly, I don't care if that user logged into the database and called my stored procedures directly - or called them via a web service, or called them via a middle tier (that used N-TIER PROXY authentication so the database actually understands WHO is running them) and I need not reinvent the entire "identification and authorization" bit over and over and over again.

Re: client/server vs. n-tier & authentication

August 29, 2008 - 4:29 pm UTC

Reviewer: Stew Ashton from Paris, France


1. I thought client/server meant "code on the client machine accesses data on a server". If there's a middle tier, it's not client/server anymore, whether you go through a "rich client" interface or a browser. Now, if the client accesses the database directly, he has his own connection and his own session and should log on as himself.

2. n-tier architectures were born to scale, through massive reuse of stateless resources. If each user identifies him/herself individually to the database, doesn't that mean one session per user, with all the additional memory use, parsing, etc. that comes with a new session, leading to a less scalable system?

I'm not trying to argue against individual authentication, just trying to get a handle on possible side effects.
Tom Kyte

Followup  

August 30, 2008 - 9:59 am UTC

1) a middle tier is a client, the database is a server. three tier client server. Having n-tiers does not make it "not client server".

Client server is used to refer to the classic "deploy application to desktop, connect it to database"

Three tier moved the client to the middle.


I don't know what your point is in #2. I agree your first sentence.

however, if you do NOT push the identity of the client through to the database - YOU HAVE TO BUILD ALL SECURITY YOURSELF and no one can every access the database directly ever again (that is not good)

and no, if each user identifies themselves, they do not need an extra CONNECTION (that is expensive), they need to identify themselves - using n-tier proxy authentication for example (one easy approach and you get all of the features of the database), using dbms_session at the very least (at least the database can sort of figure out who the heck you are...)

Middle tier scalability

August 29, 2008 - 8:36 pm UTC

Reviewer: Govind from UK

I think I agree with Tom.

C/S is more secured and when the n-tier arcitecture talks about scalability, I am still trying to see how having more middle tier boxes is more scalable than having more DB boxes or even more powerful DB boxes. Even the argument that more power on the DB box is something I do not quite agree (nothing againt Java / n-tier architecture as such).

Anyway, I think ON-LOGON trigger may be one option for making things more secure and may work in all situations.

Regards, Govind
orafind.blogspot.com - Oracle Search with a difference
Tom Kyte

Followup  

August 30, 2008 - 10:00 am UTC

on-login is virtually useless in middle tier applications using a connection pool.

they logon ONCE for all users.

not once per use.

@Govind re: Middle tier scalability

August 30, 2008 - 4:24 am UTC

Reviewer: Stew Ashton from Paris, France


What makes n-tier architectures scalable first and foremost is reuse of resources. If connecting to the DB is a lot of work, then connect once and use that connection over and over again. If parsing is a lot of work, then parse once and reuse that cursor to execute many times.

You don't need lots of boxes to do that; you could do it on one box. You do need the resources to be stateless if you want to get maximum reuse out of them.

Let's take AskTom: I'll bet that the connections and sessions are few in number and reused a lot. I'll also bet that there is practically no resource that "belongs" to me individually. When I click somewhere on the page, AskTom gets what it needs from the HTTP request I send, processes it, then forgets about me and goes on to the next request.

Now suppose I had to log on to AskTom: would I get my own connection? I guess not, with proxy authentication. Would I get my own session? Would I need my own cursors, meaning more parsing? Would my session stick around between calls?

One way or another, there's more work for the server to "remember" me and manage my resources.

Before shifting to proxy authentication, I would want to understand better exactly what additional burden that would put on the database.
Tom Kyte

Followup  

August 30, 2008 - 10:16 am UTC

... Now suppose I had to log on to AskTom: would I get my own connection? I guess not, with proxy authentication. Would I get my own session? Would I need my own cursors, meaning more parsing? Would my session stick around between calls? ....

would you get your own connection: no, I'd be using n-tier proxy authentication with connection pooling. There'd be one connection.

Would I get my own session: yes, absolutely, as a sub session of the existing connection. Just like you do with AUTOTRACE ON (it happens all of the time, we do it for lots of stuff)

ops$tkyte%ORA10GR2> select count(*) from v$session where username = user;

  COUNT(*)
----------
         1

1 row selected.

ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select count(*) from v$session where username = user;

  COUNT(*)
----------
         2

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2376410614



I had one connection from sqlplus, two sessions.

would I need my own cursors: yes, because you are you and I am I and we are using the database to authorize (because I hate writing code). And if you used stored procedures, we'd be able to reduce as much as possible the parsing taking place (and session_cached_cursors would be set appropriately and it would be mostly all soft parsing)

would my session stick around between calls: highly unlikely, not impossible (you can do stateful applications in n-tier environments, I would not recommend it)




... One way or another, there's more work for the server to "remember" me and manage my resources. ...

and to that I say "GOOD, EXCELLENT, because that is less work we have to do ourselves"

re: Followups on C/S and proxy authentication

August 30, 2008 - 11:03 am UTC

Reviewer: Stew Ashton from Paris, France


1) >> Client server is used to refer to the classic "deploy application to desktop, connect it to database"

Yes, that is what I mean by client server. When there's a middleman, the architecture is different enough that I would like to have another name for it.

2) You've got me convinced that "pushing the identity of the client through to the database" is a very good thing, I just wanted to know more about the additional resources that might be required. Your second followup (10 AM) goes a ways toward answering that, so thanks very much. I think I need to go do some tests to get my head around the details...

ON-LOGON

August 31, 2008 - 7:59 am UTC

Reviewer: Govind from UK

I thought with ON-LOGON trigger we can check which application / box is the user connecting from, hence only allow the connection from the middle tier and rest can be controlled by the UI.

However if the user needs access other than UI than it may need to be different.

Regards, Govind
searchingoracle.com
Tom Kyte

Followup  

August 31, 2008 - 9:48 am UTC

you wrote:

... Anyway, I think ON-LOGON trigger may be one option for making things more secure and may work in
all situations.
....


without really saying what you were going to do in this trigger. In any case, I would not use a trigger to enforce an IP restriction, the listener (sqlnet) has that capability builtin - you can supply a white list or black list of IP addresses without having to write any code at all.

And controlling the application access is a feature of fine grained access control and again uses the sqlnet connect information to determine the application.

connecting

November 24, 2008 - 2:03 pm UTC

Reviewer: A reader

Tom:

I would like to take your opinion on two things related to the above:

1. I have several powerbuilder client machines that have software version 5.0. We want to upgrade to 6.0. I have a requirement to block any machine that has 5.0 from connecting and do that from the oracle server. I am thinking of changing the password for database used by vesion 5.0 so they will fail. the 6.0 wil have the new db password. Is this a good solution or not?

2. I also wrote an oracle function to authenticate the client. Basically afer user logs in to client and enters userid/password, it calls an oracle function that checks al the credential and software and reports back with "OK" or "INVALID USER or SOFTWARE VERSION" to client. is this good to control authenciation from server instesd of client?

password

November 24, 2008 - 5:49 pm UTC

Reviewer: A reader

Tom:

Please disregard #1 above. I got that working.

On #2, above I assume you would recommend using a server function.

If yes, would you use "RAISE_APPLICATION_ERROR" if client provided incorrect userid/password or version or you just return a STRING that says the suerid/password was not valid.

I think you like the RAISE but read somwething that RAISE is for abnormal errors and these ar considered to be normal errors so they can be reported by a STRING to the client.
Tom Kyte

Followup  

November 24, 2008 - 7:54 pm UTC

i would use raise_application_error in order to raise a custom application error message back to the application.

raise

November 24, 2008 - 8:22 pm UTC

Reviewer: A reader

Tom:
Is this to force the client to pay attention instead of a string saying "error - invalid data".

also, do you think the server authenticate function is a good idea?

I had a problem in doing DML INSERT into audit trail using a function like this - it said you can;t do DML inside a subquery.

SELECT autheticate_user('mike','secret') from dual;


Tom Kyte

Followup  

November 24, 2008 - 10:50 pm UTC

return codes and 'strings with a message' are so 1980.

You hit an error, you have an error, the error is "attempt to logon from something you cannot". Raise an error.


I generally use the database to authenticate, I don't write my own.


You do not need to select a function to run it, just

begin :output := f(x); end;



authenticate

November 25, 2008 - 12:50 am UTC

Reviewer: A reader

Tom:
this is pl/sql call.
begin :output := f(x); end;

Power builder would be different. I thought the SQL statement call with return value is easier. I am sure PB can call oracle stored procedure but not sure how.

If i want to do some DML inside the function, do i need to do autonomous transaction or put it in a separate after-logon trigger or can't be done inside a function.
Tom Kyte

Followup  

November 25, 2008 - 10:46 am UTC

... I am sure PB can call oracle stored procedure but not sure
how.
...

that is what documentation is all about..... found this in a quick search
http://power-builder.blogspot.com/2008/04/calling-oracle-stored-procsfunctions.html




DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.
DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.
DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.
DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.
DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.
DO NOT MAKE THIS AN AUTONOMOUS TRANSACTION.


just call it as a function. If it requires inputs (as it does), you cannot put it in a trigger anyway.

Thankful

November 25, 2008 - 5:09 am UTC

Reviewer: François from Reims, FRANCE

I find you're website very usefull, and what's more, as usefull to beginners as it is for experts, which is somehow very impressive.

I usually don't post on forums but I think sometimes it's a minimum to be grateful...so be it.

password

November 25, 2008 - 2:51 pm UTC

Reviewer: A reader

Tom:

Ok i wont make it autonomous transaction. I agreed to implement using a function. Does oracle support DML inside a function?

can you insert a record into a table with something like this.

SELECT auth_user(userid,password) from dual;
Tom Kyte

Followup  

November 25, 2008 - 3:10 pm UTC

Yes, Oracle supports DML inside of a function, in fact - we'd really prefer it if ALL DML were inside of a function or procedure.



You can do that dual thing - yes.

But - you shouldn't, it would be a really horrible idea, you would HAVE to use an autonomous transaction to select a function that modifies a table.


You would simply EXECUTE THE FUNCTION. You do not select functions from dual just to run them, that is a waste.

password

November 25, 2008 - 3:19 pm UTC

Reviewer: A reader

Tom:

The client is using "SELECT" statement on the function and i want to log every call to the function. Here is the error i get. is there a workaround

SQL> create or replace FUNCTION TEST_AUTH_USER (
  2        p_user           IN   VARCHAR2   DEFAULT    NULL,
  3        p_pass           IN   VARCHAR2   DEFAULT    NULL,
  4        p_vers          IN   VARCHAR2   DEFAULT    NULL )
  5  
  6    RETURN VARCHAR2
  7  
  8     IS
  9  
 10     
 11
 12  BEGIN
 13  
 14   
 15    insert into audit_user_log (userid,password,client_version,login_date,login_time)
 16      values (p_user,p_pass,p_vers,sysdate,sysdate);
 17    commit;  
 18  
 19       RETURN '1';
 20  
 21      
 22  END;
 23  /

Function created.

SQL> select test_auth_user('test','test','5') from dual; 
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "TEST_AUTH_USER", line 15
ORA-06512: at line 1

Tom Kyte

Followup  

November 25, 2008 - 3:27 pm UTC

teach your client to do it correctly.

I said above what you would have to do in order to modify a table in a function called INCORRECTLY from a select statement.

And I said "do not do that, call it correctly"

But, I already told you how to do this.

password

November 25, 2008 - 6:49 pm UTC

Reviewer: A reader

om:

thanks, will do.

1. is there a way to suppress the last two lines returned to the client by raise_application_error


select test_auth_user('mike','secret') from dual
*
ERROR at line 1:
ORA-20001: Invalid userid and password.
ORA-06512: at "test_auth_user", line 91
ORA-06512: at line 1

2. to test the function using sql*plus do i need to write a plsql program and execute that or i can do it somehow directly in sql*plus prompt
Tom Kyte

Followup  

November 28, 2008 - 3:57 pm UTC

1) no, that would be a function of the application - applications must receive consistent and full error messages, then applications FILTER what they want to expose to the end user.

2)

SQL> exec dbms_output.put_line( test_auth_user( 'x', 'y' ) )



function

November 28, 2008 - 6:04 pm UTC

Reviewer: A reader

Tom:

So you are saying when oracle server application responds to another client (i.e. PB, VB, Forms, Java) application (not user) the error should be full and consistent.

If the oracle server application responds back to the User (like web page/browser) then I can strip the ORA-20000 or return a user friendly error message
htp.p('This is invalid userid/password');

without using RAISE_APPLICAION_ERROR because user does not know what ORA-XXXX is about and he does not know line number code or cares for it.

Is the above true?
Tom Kyte

Followup  

December 01, 2008 - 5:36 am UTC

yes, that is what I'm saying - the error message returned to ANY client should be consistent. The client - who in turn interacts with the end user - should format an error message consistent with that application interface.

Think about it - what if you returned a different error message from the database to each client - how hard would debugging or just figuring out "what the heck is happening" would be. Error message DISPLAY in a user interface is an application thing - the database, much like an operating system, must consistently inform the client of the full error message. It would be a really bad thing to see one error message in sqlplus, another in powerbuilder, a third in enterprise manager - the confusion would be immense.


db

December 01, 2008 - 11:34 am UTC

Reviewer: sam

Tom:

I see what you are saying.

The thing I do not understand is I have over 6 books in oracle and pl/sql (some written by oracle press) and "many" of them sometimes use this statement in an exception handler
to report errors to clients.

EXCEPTION
WHEN NO_DATA_FOUND
dbms_output.put_line('Invalid userid/password');
END;

without using RAISE_APPLCATION_ERROR or RAISE() or using ORA-XXXX error codes.
Tom Kyte

Followup  

December 01, 2008 - 12:08 pm UTC

write to the authors, now that you know, and ask them "why"

when reading stuff, you should be looking for "why" you are doing what you are doing...

I try to do that, when I say "do something like this", it is usually followed up with "because...."

You have a chance to evaluate whether what I propose is a 'good thing' or not. You get to see my assumptions.

My assumptions for

EXCEPTION
 WHEN NO_DATA_FOUND
 dbms_output.put_line('Invalid userid/password');
END;


would be:

a) i only use sqlplus
b) i always call set serveroutput on (somehow! and make sure no one turns it off! somehow!)
c) that I've hit an error isn't relevant, we don't really care.
d) i meant to break oracle's transaction handling - on purpose, I intentionally crippled the databases ability to preserve the integrity of data


So, ask them "why" - "what was the goal"

Use critical thinking - this is the most important thing. Don't be a 'sheeple', be a people. Question Authority.

But first and foremost - *use common sense*, think it through, use critical thinking, understand what you are doing, never apply a technique you "don't get" - "don't understand"


auth

December 01, 2008 - 12:14 pm UTC

Reviewer: A reader

Tom:

I do not think you can run a function like you have above. The execute is only for procedures from what i know. I had to write  small pl/sql to see the function result in sql*plus.

SQL>  execute app_sec.auth_user('mike','secret');
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'APP_SEC.AUTH_USER' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


declare
x  varchar2(100);
begin
x:= app_sec.test_auth_user('mike','secret');
dbms_output.put_line(x);
end;
/
1
PL/SQL procedure successfully completed.

Tom Kyte

Followup  

December 02, 2008 - 5:41 am UTC

smk - read again


SQL> exec <b> dbms_output.put_line( </b> test_auth_user( 'x', 'y' ) <b> ) </b>


is what I proposed, and not what you did.... you left off the dbms_output. In fact, your example PROVES that what I did would in fact work - if you work through it!!!


Another way would be:
SQL> variable output varchar2(100)
SQL> exec :output := test_auth_user( 'x', 'y' );
SQL> print output





Alternative to password with set role.

February 13, 2009 - 4:56 pm UTC

Reviewer: Tom from Leesburg, VA USA

I understand and have used the password protected role on many occasions but did not realize that you could utilize the ¿USING¿ clause to call a procedure instead:
CREATE ROLE role_name
[ NOT IDENTIFIED |
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;
Note: If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.
The role_name phrase is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.
The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.
The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.
The BY password phrase means that a user must supply a password to enable the role.
The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.
The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.
The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.

I have included a link that I believe gives a good example of this functionality:
http://www.amar-padhi.com/oradb_secure_app_roles.html

Can you let us know how you feel about this method compared to the password mythology? People seem to get turned off when I tell them we need to store the password for the roles in the database or application. I thought this might be a good alternative.

Thank You.


Tom Kyte

Followup  

February 16, 2009 - 12:09 pm UTC

... Can you let us know how you feel about this method compared to the password
mythology? People seem to get turned off when I tell them we need to store the
password for the roles in the database or application. I thought this might be
a good alternative.
...

that is part of the reason this was invented. Storing a password in the application is a horrible idea.

these are called secure application roles, the application can enable a role because the application is able to call a procedure and the only way to enable the role is to call the procedure - and the application would only do so after verifying the user running the application is supposed to have that role using whatever logic it wants.

failed to open file :ifrun60_dump_3348

June 30, 2009 - 3:35 am UTC

Reviewer: ziad from KSA

hi tom,
we have problem, if i want call froms from another forms to create or update and i want return to original forms,
after closed the forms the system display:
failed to open file :ifrun60_dump_3348 and next time new number. and cloase all system and the menu.

encryption over network connection

September 15, 2009 - 5:28 pm UTC

Reviewer: NP from USA

I believe that by using either TDE or DBMS_CRYPTO the data will not be encrypted when it travels over the network between the database and the client machine. Is that correct?
Tom Kyte

Followup  

September 16, 2009 - 8:48 am UTC

correct, both of those are for data at rest. sqlnet encryption would be used independent of those two techniques to encrypt data in motion - on the network.

Encryption over network

September 16, 2009 - 9:43 am UTC

Reviewer: NP from USA

Thanks Tom for the reply!
Does all the data traveling between the database server and the client get encrypted using sqlnet encryption? Is there any way to encrypt just a single column (like credit card number) from a single table, and leave everything else unencrypted?
Tom Kyte

Followup  

September 16, 2009 - 4:27 pm UTC

... Does all the data traveling between the database server and the client get
encrypted using sqlnet encryption? ...

yes

... Is there any way to encrypt just a single
column (like credit card number) from a single table, and leave everything else
unencrypted? ...

no, not unless you do it yourself - the application encrypts and transmits (and database just gets raw binary data), the application requests and decrypts. Not recommended as key management is painfully hard.

If you are worried about "all of that encryption", probably not a worry - benchmark it and you'll see.

Look at how often you use SSL every day, for really big pages...

March 19, 2012 - 10:13 am UTC

Reviewer: gopal from Netherlands

Hi Tom,

It looks like this link http://asktom.oracle.com/~tkyte/article2/index.html is not working.

Tom Kyte

Followup  

July 11, 2019 - 2:41 pm UTC

I'll suggest this, as that article would have been written against 8i 10 years ago and would be a little out of date :)

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/topics-for-database-administrators-and-developers.html#GUID-1BD4EDBC-F265-46E1-BD3D-D3D462876965

Module_hash algorithem

September 03, 2013 - 11:03 am UTC

Reviewer: Øyvind from Norway

Hi Tom,
your views and knowledge on oracle access security are really interesting to read.

In the v$session table there is a module_hash column, and there have not been much talk about it in this thread. I have not been able to find much information about this hash value.

1) Would it be possible to use the module_hash to make sure that it is only the application that can be used to log in the database?
2) What hash algorithm does module_hash use?
3) Could you change the hash algorithm used to calculate the module_hash?

Cheers,
Øyvind
Tom Kyte

Followup  

September 04, 2013 - 7:00 pm UTC

i don't know what you mean in #1, can you clarify what you are trying to do.

module hash is simply a hash of the module column. the module column is set to whatever value the application wants to set it to. the module hash isn't going to be extremely useful to you

Hi tom

September 05, 2013 - 2:48 am UTC

Reviewer: A reader

asdasdasdasdasdasdasdasdasdasdasdasd

September 09, 2013 - 7:49 am UTC

Reviewer: A reader


Revoke Dbms_application_info from public

March 10, 2015 - 8:08 am UTC

Reviewer: Eamon from UK

Given that the module can be updated by anyone due to execute on Dbms_application_info being granted to public, is it best to revoke Dbms_application_info from public and grant only to SYS users and appropriate app owners? Otherwise it seems we can never rely on module for any context type security?

how to find suspicious activity on oracle linux server

October 31, 2018 - 6:25 am UTC

Reviewer: Dipika from Ahmedabad,India

Hello genius,

I'm regular viewer of this site,Many thanks for your help.
In my Server, which is running on AWS-linux ,Oracle 11g2,Tomcat-7, APEX 5.0.1.
Sometimes it's having issues like user may not login into application but there is no error logs generated from backend and apex it self.
Today,Apex Directory in which ORDS was configured was also not found by apex,It seems like it was deleted,But there was no log in linux history,there is nothing in alert log and nothing in catalina.out.
Can you Please help me to figure it out this issue,Why it's happened in server,If it can be securiry compromise then how can i fix it?


I found below error in /var/log/secure file:

polkitd(authority=local): Operator of unix-session:/org/freedesktop/ConsoleKit/Session1 FAILED to authenticate to gain authorization for action org.freedesktop.packagekit.system-network-proxy-configure for system-bus-name::1.32 [gpk-update-icon] (owned by unix-user:oracle)
I want to trace this error,can you guys please me.

Thanking you,
Dipika Poraniya


Connor McDonald

Followup  

November 02, 2018 - 2:52 am UTC

Sorry, that sounds like something happening down in the OS/AWS end of things, so not sure we can help you there.

One thing you could do is turn on database auditing and check the internal audit for failed login attempts at the database layer.

You can also look at APEX_WORKSPACE_ACCESS_LOG to see who tried to connect to apex and whether they were successful

Prevent users from login into database from backend application

July 11, 2019 - 10:42 am UTC

Reviewer: Nilesh Kumar

Hey Tom,
I'll suggest this, as that article would have been written against 8i 10 years ago and would be a little out of date :)

http://docs.oracle.com/cd/E11882_01/network.112/e16543/vpd.htm 

this Link that you have provided is not working, and am getting 404 errors for the link

And secondly we have developed this code for preventing users to logon using any SQL tool

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program-- INTO v_prog FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%SQL DEVELOPER%' -- SQL Developer
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/

can you comment, will this solve our purpose or not. The database is 11g
Chris Saxon

Followup  

July 11, 2019 - 2:46 pm UTC

Thanks, I've updated the link.

The trigger suffers from the same problem Tom describes in his original answer: it's possible to spoof the program. So a hacker could make SQL Developer "look like" something else.

Plus there are far more SQL clients than the four you've listed!

So no, this doesn't solve your purpose.