Operating System - HP-UX
1748265 Members
4071 Online
108760 Solutions
New Discussion юеВ

Re: Controlling access to sqlplus

 
Kaustubh Kane
Advisor

Controlling access to sqlplus

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.
8 REPLIES 8
Murat SULUHAN
Honored Contributor

Re: Controlling access to sqlplus

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
Murat Suluhan
Court Campbell
Honored Contributor

Re: Controlling access to sqlplus

Outside of what Murat suggested you can use sudo or RBAC.
"The difference between me and you? I will read the man page." and "Respect the hat." and "You could just do a search on ITRC, you don't need to start a thread on a topic that's been answered 100 times already." Oh, and "What. no points???"
Ivan Krastev
Honored Contributor

Re: Controlling access to sqlplus

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
Ariel Cary
Frequent Advisor

Re: Controlling access to sqlplus

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
Arturo Galbiati
Esteemed Contributor

Re: Controlling access to sqlplus

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
Eric Antunes
Honored Contributor

Re: Controlling access to sqlplus

Hi,

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

alter user identified by ;

Best Regards,

Eric Antunes

Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor

Re: Controlling access to sqlplus

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ariel Cary
Frequent Advisor

Re: Controlling access to sqlplus

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