OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » blocking sqlplus

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

blocking sqlplus [message #238287]

Thu, 17 May 2007 05:56

mymailbox.21
Messages: 14
Registered: May 2007
Location: hyderabad

Junior Member

hi,

is there any script to block sqlplus for a particular user?

i tried but i got only blockage of tools

plz kindly help me

thanks
pavan

Report message to a moderator

Re: blocking sqlplus [message #238300 is a reply to message #238287]

Thu, 17 May 2007 06:28

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No. Not really.
You can forbid some statements for users using PRODUCT_USER_PROFILE table.
Have a look at SQL*Plus� User's Guide and Reference, Chapter 9 SQL*Plus Security

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238531 is a reply to message #238287]

Fri, 18 May 2007 08:10

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

You can block them from the database...

Report message to a moderator

Re: blocking sqlplus [message #238540 is a reply to message #238531]

Fri, 18 May 2007 08:26

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How? This is the question.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238551 is a reply to message #238287]

Fri, 18 May 2007 08:46

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Oh..I thought it was about sqlplus. For the database, just don't give them a login Smile

Report message to a moderator

Re: blocking sqlplus [message #238629 is a reply to message #238551]

Fri, 18 May 2007 15:37

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Hi,
Revoke the create session privilege from user.Thats an esay way;)

Report message to a moderator

Re: blocking sqlplus [message #238633 is a reply to message #238629]

Fri, 18 May 2007 15:48

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OP wants to block SQL*Plus not ANY connection.

Regards
Michel

[Updated on: Fri, 18 May 2007 15:48]

Report message to a moderator

Re: blocking sqlplus [message #238636 is a reply to message #238287]

Fri, 18 May 2007 15:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

create a logon that does a query similar to below:


SQL> select program from v$session where username = 'DBADMIN';

PROGRAM
------------------------------------------------
sqlplus@stagedb01.hitbox.com (TNS V1-V3)

when "sqlplus" is detected, just ALTER SYSTEM KILL SESSION

Report message to a moderator

Re: blocking sqlplus [message #238639 is a reply to message #238287]

Fri, 18 May 2007 15:54

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

mymailbox.21 wrote on Thu, 17 May 2007 04:56

hi,

is there any script to block sqlplus for a particular user?

i tried but i got only blockage of tools

plz kindly help me

thanks
pavan

Particular user means a single user access i guess, not sqlplus???

Report message to a moderator

Re: blocking sqlplus [message #238642 is a reply to message #238639]

Fri, 18 May 2007 15:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

DreamzZ wrote on Fri, 18 May 2007 22:54

Particular user means a single user access i guess, not sqlplus???

Quote:

is there any script to block sqlplus

sqlplus does not mean sqlplus?
Is this not the subject of the topic?

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238643 is a reply to message #238636]

Fri, 18 May 2007 16:01

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ana,

What about "mv sqlplus myprogram"?

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238644 is a reply to message #238287]

Fri, 18 May 2007 16:01

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

[quote title=mymailbox.21 wrote on Thu, 17 May 2007 04:56]hi,

is there any script to block sqlplus for a particular user?

But dis is the question not that;)

[Updated on: Fri, 18 May 2007 16:02]

Report message to a moderator

Re: blocking sqlplus [message #238646 is a reply to message #238644]

Fri, 18 May 2007 16:11

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

DreamzZ,

Explain your last post I don't understand it.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238647 is a reply to message #238287]

Fri, 18 May 2007 16:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Michel,
I did not claim it would be 100% effective.
IMO, the OP is chasing fools gold.

cp sqlplus fubar
./fubar dbadmin

SQL*Plus: Release 10.2.0.2.0 - Production on Fri May 18 14:06:46 2007

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

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select program from v$session where username = 'DBADMIN';

PROGRAM
------------------------------------------------
fubar@stagedb01.hitbox.com (TNS V1-V3)

With available of freeware like squirrel such attempts by OP are nothing more than security by obscurity.

Report message to a moderator

Re: blocking sqlplus [message #238648 is a reply to message #238647]

Fri, 18 May 2007 16:16

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Agree, so my first answer.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238867 is a reply to message #238287]

Sun, 20 May 2007 13:39

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

Yeah obviously there is solution. U can block SQLPlus, from taking import of database, from connecting TOAD, Even user cannot take data from Microsoft EXCEL Without ur permission.

To get it u have to write a trigger in SYS schema and include the exe files and exclude DBA users to get connect from SQL, TOAD and tools and restrict users to get login just from application Forms.

Zeeshan Ali Awan
Zesh@inbox.com

Report message to a moderator

Re: blocking sqlplus [message #238871 is a reply to message #238867]

Sun, 20 May 2007 14:00

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yeah obviously there is a way to workaround your trigger: rename the executable.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238872 is a reply to message #238287]

Sun, 20 May 2007 14:02

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

If not created then tell me i would create for u here ok.

Report message to a moderator

Re: blocking sqlplus [message #238873 is a reply to message #238872]

Sun, 20 May 2007 14:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you mean? It is not clear.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238874 is a reply to message #238287]

Sun, 20 May 2007 14:07

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

No Filename renaming would never work. Bcoz when u rename the sqlplus.exe and after renaming when execute, Engine will tell to database listener that SQL want to connected, the hardcoded u cannot change. U can check it. ok

Report message to a moderator

Re: blocking sqlplus [message #238876 is a reply to message #238287]

Sun, 20 May 2007 14:13

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

I said that if u not succeeded I will paste my code here that i m running to block the users and tell me renaming could not solve it because first i could not solved and let say if solved then from how much clients computers u would rename the file.

Report message to a moderator

Re: blocking sqlplus [message #238877 is a reply to message #238876]

Sun, 20 May 2007 14:30

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post your code and I will show you I can workaround it and connect with SQL*Plus.

Please write in english your language is hard to read for a non native english speaker.

Regards
Michel

Report message to a moderator

Re: blocking sqlplus [message #238919 is a reply to message #238287]

Mon, 21 May 2007 00:39

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

begin
FOR REC IN (SELECT USERNAME,PROGRAM,MODULE,TERMINAL,MODULE_HASH FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID'))
LOOP
if rec.username not in ('SYS','SYSTEM','ABBC','XYZ')
and
upper(rec.terminal) not like '%Computername%'
and
(rec.module is not null
OR
upper(rec.program) like '%EXP%'
OR
upper(rec.program) like '%IMP%'
OR
upper(rec.program) like '%VRMGR%'
OR
upper(rec.program) like '%EZSQL.EXE'
OR
rec.program is NULL
OR
upper(rec.program) like '%TOAD.EXE')
then
RAISE_APPLICATION_ERROR(-20001,'Sorry You are not Authorise to Logon from this tool');
end if;
end loop;
END;

Create it in SYS Schema and let me tell that from SQL/PLUS/EXP/IMP and MS EXcel u can connect r not?

Report message to a moderator

Re: blocking sqlplus [message #239081 is a reply to message #238287]

Mon, 21 May 2007 07:58

zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan

Senior Member

So Michel Cadot where are u? U must tell here what happened? Could u connected r not? Have u get connected from EXCEL r not?

Report message to a moderator

Re: blocking sqlplus [message #239133 is a reply to message #239081]

Mon, 21 May 2007 09:38

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

zeshanali76 wrote on Mon, 21 May 2007 08:58

So Michel Cadot where are u? U must tell here what happened? Could u connected r not? Have u get connected from EXCEL r not?

You left out too many letters from your words for people to understand. Please don't speak in AOhelL gibberish speak.

Report message to a moderator

Previous Topic:	Pinning Objects
Next Topic:	How to install Oracle 10.2.0.2 on SPARC system??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 12:50:47 CST 2024