Re: Restriction on sqlplus access

Hi Paul

I too have read Geoff's idea as well some time ago, so i dug it out again to remind myself of the details.

He doesn't use a logon trigger but a trigger on insert and update on sys.aud$ and watches for -942 errors on PRODUCT_PRIVS and then sends a kill message via an alert to a "watcher" process that waits to receive kill signals and kills the process logging in. The premise for this to all work is that a product_user_profile view has been dropped and that sys.aud$ has been set up in a different tablespace to SYSTEM and also owned by SYSTEM as it is not possible to create triggers on SYS objects. Also audit needs to be enabled and also set-up to check for NOT EXISTS to capture the -942 error.

The idea is a good one to use the fact that SQL*Plus queries the product_user_profile tables/views on start-up but what he suggests could be easy to foil and has other issues. If someone inadvertently runs the pupbld script it wont work. The other problem is Oracle no longer supports moving sys.aud$ (unless they have changed their minds again since late last year) and also the fact that audit needs to be enabled for this to work. If a lot of auidt is generated on the database being monitored this trigger would also fire for every insert and update (it might not do much in most cases but it would fire)

I did a little check with trace set to find out what SQL*Plus does on start-up. It does check product_privs twice.

I can see where Geoff is going with this one, you couldn't just have the trigger on the product_user_profile tables otherwise no legitimate user using SQL*plus could get in. I would implement the idea by keeping the product tables intact and having a trigger on product_privs and then process within the trigger to check if it is a legitimate user logging on and kill all others. Of course this wouldn't keep out MS Access. But what about Excel or word or MS query or ......

Nice idea though for SQL*Plus

cheers

Pete

>as a matter of fact, I was reading some code in Geoff Ingram's "High
>Performance Oracle" book on the train ride home this evening.
>
>a logon trigger and a targeted drop table combined with auditing
>provided quite an elegant solution to blocking (and logging) attempted
>sessions by sqlplus and MS access. I believe that he recommended
>sending a message via dbms_alert to a session that is just there to
>kill sessions. You have to admit, its pretty funny to have a logon
>trigger send a message that says "kill session", sid, serial# to a
>session killer listening for such alerts.

-- 
Pete Finnigan
Managing Director
PeteFinnigan.com Limited

Email : pete_at_petefinnigan.com

Web site: http://www.petefinnigan.com

Pete is the founder of PeteFinnigan.com Limited a UK based company specialising 
in Oracle security audits and services. Email info_at_petefinnigan.com for details 
and availability.

Pete Finnigan is the  author of the recently published book about Oracle 
security from the SANS  Institute "Oracle security Step-by-step (A survival 
guide for Oracle security)" - see http://store.sans.org for details.

Some recently published articles include:

http://online.securityfocus.com/infocus/1644 - "SQL injection and Oracle - part 
one"

http://online.securityfocus.com/infocus/1646 - "SQL injection and Oracle - part 
two"