hot to limit sqlldr ?

[Stefan]

Is there a way to limit or prevent sqlloding into a database for a user
who has insert/update/delete privs?

Do I have to write a logon trigger and kill the session of the
applicaion name is sqlldr ?

Any better way of doing this?

Thanks

[Noel]

Użytkownik Stefan napisał:
Is there a way to limit or prevent sqlloding into a database for a user who has insert/update/delete privs? Do I have to write a logon trigger and kill the session of the applicaion name is sqlldr ? Any better way of doing this?

I really wonder of sense of that operation?
You let user insert rows, and now you want to prevent inserting, if user
uses a tool, which just helps to do it faster?
There is no big problem, to convert a file into table inserts and do
same job with sqlplus.
If you don't want user to use sqlldr just don't let him run executables.
That's my opinion.

--
Noel

[Stefan]

ideally you are correct - however, I am hosting an application which
requires that the application level user have the same username and
password as the database level user, AND the database level user has to
have insert/update/delete in order to be able to use the application's
front end....
I know it's a horrible design, but that's the way it is....

so the only thing I've been able to think of so far is to create a
LOGON trigger which kills sessions unless they are made via the
appropriate client application...

was wondering if there are any other ideas out there...

[Noel]

Użytkownik Stefan napisał:
ideally you are correct - however, I am hosting an application which requires that the application level user have the same username and password as the database level user, AND the database level user has to have insert/update/delete in order to be able to use the application's front end.... I know it's a horrible design, but that's the way it is.... so the only thing I've been able to think of so far is to create a LOGON trigger which kills sessions unless they are made via the appropriate client application... was wondering if there are any other ideas out there...

I know one. Change application ON_LOGON trigger.
Lest say application uses user SCOTT with TIGER password.
But real password is IGER and ON_LOGON trigger adds T.
You say user to logon SCOTT/TIGER, but if he uses sqlldr he just won't
connect.
(for example i just put 'T', you could think of more complex procedure).

--
Noel

[Noel]

Użytkownik Stefan napisał:
ideally you are correct - however, I am hosting an application which requires that the application level user have the same username and password as the database level user, AND the database level user has to have insert/update/delete in order to be able to use the application's front end.... I know it's a horrible design, but that's the way it is.... so the only thing I've been able to think of so far is to create a LOGON trigger which kills sessions unless they are made via the appropriate client application... was wondering if there are any other ideas out there...
I know one. Change application ON_LOGON trigger.
Lest say application uses user SCOTT with TIGER password.
But real password is IGER and ON_LOGON trigger removes T.
You say user to logon SCOTT/TIGER, but if he uses sqlldr he just won't
connect.
(for example i just removed 'T', you could think of more complex procedure).


--
Noel

[Ed prochak]

Noel's answer about running executables is the correct one. In the OS
remove his privilege to execute SQL Loader.

If the OS login is only allowed to run your application, then put it in
a restricted shell and configure the profile to runn the application
and nothing else. (I'm thinking from a UNIX/LINUX view here).

Simple rule:
User accounts run only user applications.
Developers and DBA accounts run tools (like SQLPLUS and SQL Loader).

Set up the OS accounts correctly and you'll be much better off.

Ed.

[Maxim Demenko]

Stefan schrieb: ideally you are correct - however, I am hosting an application which requires that the application level user have the same username and password as the database level user, AND the database level user has to have insert/update/delete in order to be able to use the application's front end.... I know it's a horrible design, but that's the way it is.... so the only thing I've been able to think of so far is to create a LOGON trigger which kills sessions unless they are made via the appropriate client application... was wondering if there are any other ideas out there...

I think ,your real intention is to restrict the users to do only those
things, that are allowed in your application forms, but you haven't
restrict users to use only application credentials ( as they are the
same as the database credentials ). If i am right in my suggestion, you
can't be sure if you restrict the use of sql loader - every intermediate
user can in 5 minutes write a script ( or a small java program, if you
restrict the use of sqlplus ) and put all it unwanted sql in your
database, that you can hard imagine. I would consider to use in that
case an application context ( good start point could be
http://www.oracle.com/technology/tech/windows/wp/Oracle_DB_10g_Security_WP.pdf
). For example , if your application run on the dedicated server and
your end user have no access to this machine, you can indeed write an
logon trigger that checks the context and then, if session comes from
one certain ip address - of your appserver - (ip address is not
necessary, you can set any attribute in your context for you application
server ) , needed role should be enabled to allow insert/update etc, all
other ip addresses should provide no permissions for dml, or somewhat
like this.
Unfortunately you can not kill session through an logon trigger , in
worst case - raise exception , that helps , if your end user aren't
dba's ...

Nevertheless, the Noel's idea ( if password = 'TIGER' then password :=
substr(password,2); end if; ) i found really perfect ... ( i'll try to
implement it next week)

Best regards

Maxim

[Guest]

"Stefan" <stefan@dragolov.com> wrote in message news:<1113402433.811868.295760@f14g2000cwb.googlegroups.com>... ideally you are correct - however, I am hosting an application which requires that the application level user have the same username and password as the database level user, AND the database level user has to have insert/update/delete in order to be able to use the application's front end.... I know it's a horrible design, but that's the way it is.... so the only thing I've been able to think of so far is to create a LOGON trigger which kills sessions unless they are made via the appropriate client application... was wondering if there are any other ideas out there...

Simply do not install sqlldr on the clients pc.
Easy

--
Sybrand Bakker
Senior Oracle DBA