OraFAQ Forum: Security » Dont want user to connect to connet through SQL plus?

Home » RDBMS Server » Security » Dont want user to connect to connet through SQL plus?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dont want user to connect to connet through SQL plus? [message #119072]

Tue, 10 May 2005 09:15

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

As said in the documentation

http://dnaugler.cs.semo.edu/oracledocs/a90842/ch10.htm

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Maaher
Messages: 7065
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

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Dont want user to connect through SQL plus? [message #119223 is a reply to message #119137]

Wed, 11 May 2005 06:07

Maaher
Messages: 7065
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

Report message to a moderator

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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 !!.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

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

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

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

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

Report message to a moderator

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

Maaher
Messages: 7065
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 13:12:58 CST 2025