topic Re: Controlling access to sqlplus in Operating System - HP-UX

Controlling access to sqlplus

Kaustubh Kane — Tue, 19 Jun 2007 08:38:19 GMT

Hi,
We have a Production database. There have been instances when someone logged into the database via SqlPlus and performed some service impacting transactions.

We would like to know if there's any way to prevent this.
How do we restrict access to the database?
What is the strategy that can be applied?

Please let me know.

Thanks and regards,
Kaustubh Kane.

Re: Controlling access to sqlplus

Murat SULUHAN — Tue, 19 Jun 2007 08:52:20 GMT

Hi

You must look at Oracle Security Guide book.

I think "5 Authorization: Privileges, Roles, Profiles, and Resource Limitations" section for 10GR2 is useful for you
http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#sthref427

If your database different from 10GR2 please look at related page from OTN.

Regards
Murat
HP

Re: Controlling access to sqlplus

Court Campbell — Tue, 19 Jun 2007 08:55:30 GMT

Outside of what Murat suggested you can use sudo or RBAC.

Re: Controlling access to sqlplus

Ivan Krastev — Tue, 19 Jun 2007 08:56:31 GMT

Normally sqlplus is installed with user:groups same as the Oracle instance. Just remove all users from that group (dba) and control access to sqlplus via sudo.

regards,
ivan

Re: Controlling access to sqlplus

Ariel Cary — Tue, 19 Jun 2007 20:28:52 GMT

Hi,

If the connection comes from a remote client, it's difficult to have such a control as to what tools the client can or cannot use to login into the database. You may try checking the v$session.PROGRAM column (plus others) to see if a user is logged in using sqlplus, but that can be easily fooled.

The problem then comes down to restricting your users' privileges to the minimum required. Now, if they already have privileges to update the database in such a way that can potentially harm your services, then you could start auditing transactions. Furthermore, if those users have DBA privileges (meaning no need for explicit grants to update the db), then you may want to take a look at latest Oracle's database vault technology available in Oracle 10g and for some ports in 9i terminal release.

HTH,

-Ariel

Re: Controlling access to sqlplus

Arturo Galbiati — Wed, 20 Jun 2007 03:28:38 GMT

Hi,
I had this problem in a past and I fixed this writing some pl/sql code.
I'm sorry but I'm not able to share it to you.
BTW the flow was:
1. define in a table the users able to use sqlplus (or more in general other program)

2. use a trigger at logon to identify which program is using the user queryng v$session (module or program)

3. if the user is not in the authorized table, drop it sending a messge about deny access (optionally you can tarck alll the logon, logoff, program used, user and tatus in a table for auditing review)

This work on 9i.

HTH,
Art

Re: Controlling access to sqlplus

Eric Antunes — Wed, 20 Jun 2007 03:50:56 GMT

Hi,

If your applications don't have user password changing restrictions, change the user password:

alter user identified by ;

Best Regards,

Eric Antunes

Re: Controlling access to sqlplus

Yogeeraj_1 — Wed, 20 Jun 2007 04:24:25 GMT

hi,

In oracle 11g, you will have the Oracle Vault feature. In the meanwhile, the problem you have describe before is because of unauthorised access. Someone has guessed the password of an oracle user and got access to the database.

Access to the database can be made not only through SQLPLUS but lots of other freely available tools. e.g. SQL Developer, Toad trial version, etc.

You should enforce strong passwords.

You should also run diagnosis programs to determine the real cause of the performance drop.

if you need any further guidances, please do let us know.

kind regards
yogeeraj

Re: Controlling access to sqlplus

Ariel Cary — Wed, 20 Jun 2007 12:17:49 GMT

Arturo,

Beware that could be easily disguised as I mentioned. Your user might just execute this sequence to fool it.

--~~~~~~~~~~
$ cd $OH/bin
$ cp sqlplus myapp
$ myapp acary

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 20 13:08:59 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

SQL> r
1 select program, module
2 from v$session
3* where username='ACARY'

PROGRAM MODULE
--------------- ---------
myapp@myhost SQL*Plus

SQL>
SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('MYMODULE', NULL);

PL/SQL procedure successfully completed.

SQL> r
1 select program, module
2 from v$session
3* where username='ACARY'

PROGRAM MODULE
--------------- ---------
myapp@raju3 MYMODULE

SQL>

Now your program may not be able to identify the user is logged in using sqlplus (or potentially any other tool).
--~~~~~~~~~~

Not that I'm revealing a secret, but just making the point that this is not a secure way to control this kind of accesses, and hope is also instructional for others.

BR,

-Ariel