Home » RDBMS Server » Security » Dont want user to connect to connet through SQL plus?
Dont want user to connect to connet through SQL plus? [message #119072] Tue, 10 May 2005 09:15 Go to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi

My question is as below....


I dont want user to connect through SQL*plus on database .... As a DBA What query i should write to to do this?

Note:- I know there is a simple query that DBA can type to do this but right now i m not getting it..

so please if any one know query to solve my problem than please let me know.

thank you

From :- sunil
Re: Dont want user to connect to connet through SQL plus? [message #119075 is a reply to message #119072] Tue, 10 May 2005 09:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said in the documentation

http://dnaugler.cs.semo.edu/oracledocs/a90842/ch10.htm
Re: Dont want user to connect to connet through SQL plus? [message #119078 is a reply to message #119075] Tue, 10 May 2005 09:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
my apologies.
I was wrong.
The above will URL will NOT restrict using sql*plus.
You may need to write a custom package, that looks up dictionary ( like v$session) to see if the connection is made by 'thisapplication/tool' and restrict it.
Re: Dont want user to connect to connet through SQL plus? [message #119082 is a reply to message #119078] Tue, 10 May 2005 09:31 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

Maheshji earlier i had doen this by query but i forget query now

any way thanks
sunil
Re: Dont want user to connect to connet through SQL plus? [message #119105 is a reply to message #119082] Tue, 10 May 2005 10:53 Go to previous messageGo to next message
DaljitSingh
Messages: 4
Registered: May 2005
Junior Member
Hi,

You can write the following LOGON trigger to prevent SQLPLUS sessions on your DB.


CREATE OR REPLACE TRIGGER block_sqlplus
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0; -- Allow SYS Connections

IF UPPER(v_prog) LIKE '%SQLPLUS%' THEN
RAISE_APPLICATION_ERROR(-20001, 'SQL*PLUS users not allowed on Database');
END IF;
END;
/
SHOW ERRORS

Daljit Singh
Re: Dont want user to connect to connet through SQL plus? [message #119119 is a reply to message #119105] Tue, 10 May 2005 13:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I was waiting for this solution but it won't work, DaljitSingh.

Read this and this and you'll get the point.

MHE
icon14.gif  Re: Dont want user to connect to connet through SQL plus? [message #119137 is a reply to message #119119] Tue, 10 May 2005 15:03 Go to previous messageGo to next message
DaljitSingh
Messages: 4
Registered: May 2005
Junior Member

Yeah, if the users rename the exe then definitely they can login through that and unfortunately there is no way out.

According to me instead of focusing on any program level security we should focus on the user level security means no matter from which program the user get enters into the DB but he should have appropriate privileges to perform only and only his operations. This we can impelement using either DB roles or Application roles. This is all I have to say on this.

Daljit Singh.
Re: Dont want user to connect to connet through SQL plus? [message #119181 is a reply to message #119137] Wed, 11 May 2005 01:12 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI all

As per my info, Oracle does provide security using roles & priveleges only. Rest all is dependent on Application logic and host OS.

Am I right in saying this??

Regds
Girish
Re: Dont want user to connect to connet through SQL plus? [message #119219 is a reply to message #119181] Wed, 11 May 2005 05:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
FGAC or row level security is the lowest level oracle can get into.
Roles / priveleges are the highest level.
With RLS, different Application Users can query the same database table, though they can see only a specific subset of data.
Re: Dont want user to connect through SQL plus? [message #119223 is a reply to message #119137] Wed, 11 May 2005 06:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
DaljitSingh wrote on Tue, 10 May 2005 22:03

we should focus on the user level security means no matter from which program the user get enters into the DB but he should have appropriate privileges to perform only and only his operations.
Amen to that. Oracle provides several ways to ensure security, as Mahesh has pointed out. It's best to use those methods...

MHE
icon5.gif  Re: Dont want user to connect to connet through SQL plus? [message #124199 is a reply to message #119072] Thu, 16 June 2005 17:01 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Sunil,

I have a similar kind of question. I am not sure if you did find answer to your question or not. Guys Here is my question. I have a user which is embedded in batch jobs. I dont want this user to login using sqlplus or any other tool. He should be able to perform the batch job though. Any help would be greatly appreciated. Thanks in advance.

Vikram Jogi
Re: Dont want user to connect to connet through SQL plus? [message #124200 is a reply to message #124199] Thu, 16 June 2005 17:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I dont want this user to login using sqlplus or any other tool. He should be able to perform the batch job though.

without logging into database, what is 'USER' about to do?
He can do anything he wants with without ever logging into database.
May be you are actually lookinto writing a procedure (job), and scheduling the procedure using DBMS_JOB (within oracle).
THus, the user is not actually logging into database with any external means.

[Updated on: Thu, 16 June 2005 17:11]

Report message to a moderator

icon5.gif  Re: Dont want user to connect to connet through SQL plus? [message #124201 is a reply to message #124200] Thu, 16 June 2005 17:20 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Hi Mahesh,

Thanks for your instant reply. Let me tell you my situation. I have a user created specifically to run a batch job. The job is scheduled via cron. In the script the user is called with his username/password to run the job. Every one know that password and my company is not so particular about security. But what my idea is to restrict that user to use a sqlplus session or infact any other session using other tools like toad,dbartisan....

Thanks,
Vikram
Re: Dont want user to connect to connet through SQL plus? [message #124207 is a reply to message #124201] Thu, 16 June 2005 18:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
YOur requirement is trivial.

>>I have a user created specifically to run a batch job. The job is scheduled via cron.
>>But what my idea is to restrict that user to use a sqlplus session or infact any other session using other tools like toad,dbartisan

Still, any `intruder`can use the same program / tool that your 'batch job' uses!!.
Within an sql*plus session you can disable / restrict certain operations.
But you cannot restrict the user from logging in !!.


Re: Dont want user to connect to connet through SQL plus? [message #124217 is a reply to message #124207] Thu, 16 June 2005 20:46 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

My Question was ...I want user to connect to the Database but by application not by SQL *Plus Client provided by oracle.

Thanks
sunil
Re: Dont want user to connect to connet through SQL plus? [message #124219 is a reply to message #124217] Thu, 16 June 2005 21:10 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Sunil,

Even my question was same as your. Can you please help me out in finding the solution.

Thanks,
Vikram
Re: Dont want user to connect to connet through SQL plus? [message #124220 is a reply to message #124207] Thu, 16 June 2005 21:13 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Mahesh,

Thanks for your reply. I know that we can put on the sql*session. But i read some where that it can be done on a local system. How does this one work.Can you please suggest me a manual or some kind of documentation.

Thanks,
Vikram.
Re: Dont want user to connect to connet through SQL plus? [message #124313 is a reply to message #124220] Fri, 17 June 2005 08:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I know that we can put on the sql*session. But i read some where that it can be done on a local system. How does this one work.Can you please suggest me a manual or some kind of documentation.

I dont understand what your need is.
YOu cannnot restrict a user to use or NOT to use ,one particular application
Re: Dont want user to connect to connet through SQL plus? [message #124320 is a reply to message #124313] Fri, 17 June 2005 09:08 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Mahesh,

I dont want user to connect through SQL*plus on database ... Is there no way to do this? This is my question. Please help me out in finding a way.

Thanks,
Vikram
Re: Dont want user to connect to connet through SQL plus? [message #124322 is a reply to message #124320] Fri, 17 June 2005 09:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There is no way to do it perfectly.
YOu can write a trigger as show in one of above postings, to trap the programname. IF sqlplus.exe then deny.
But the user can always rename sqlplus.exe to mytool.exe or whatever and do his business.
Re: Dont want user to connect to connet through SQL plus? [message #124324 is a reply to message #124322] Fri, 17 June 2005 09:17 Go to previous messageGo to next message
vikramjogi
Messages: 71
Registered: June 2005
Location: Scranton
Member
Thanks Mahesh. I will try working out with some kind of cutomized triggers. Neways thanx for your suggestion.

Vikram
Re: Dont want user to connect to connet through SQL plus? [message #124336 is a reply to message #124324] Fri, 17 June 2005 10:35 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And what do you do when a user dowloads TOAD or SQL*Navigator or SQL*Minus or ... any other tool around?

Perhaps you should only enable your application?

MHE
Previous Topic: ORA-28001
Next Topic: Batch only Log on to oracle DB
Goto Forum:
  


Current Time: Mon Aug 03 06:22:39 CDT 2020