- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Controlling access to sqlplus
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 01:38 AM
тАО06-19-2007 01:38 AM
Controlling access to sqlplus
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 01:52 AM
тАО06-19-2007 01:52 AM
Re: Controlling access to sqlplus
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 01:55 AM
тАО06-19-2007 01:55 AM
Re: Controlling access to sqlplus
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 01:56 AM
тАО06-19-2007 01:56 AM
Re: Controlling access to sqlplus
regards,
ivan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 01:28 PM
тАО06-19-2007 01:28 PM
Re: Controlling access to sqlplus
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 08:28 PM
тАО06-19-2007 08:28 PM
Re: Controlling access to sqlplus
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 08:50 PM
тАО06-19-2007 08:50 PM
Re: Controlling access to sqlplus
If your applications don't have user password changing restrictions, change the user password:
alter user
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-19-2007 09:24 PM
тАО06-19-2007 09:24 PM
Re: Controlling access to sqlplus
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-20-2007 05:17 AM
тАО06-20-2007 05:17 AM
Re: Controlling access to sqlplus
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