Home » RDBMS Server » Server Administration » blocking sqlplus
blocking sqlplus [message #238287] Thu, 17 May 2007 05:56 Go to next message
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
Re: blocking sqlplus [message #238300 is a reply to message #238287] Thu, 17 May 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: blocking sqlplus [message #238531 is a reply to message #238287] Fri, 18 May 2007 08:10 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You can block them from the database...
Re: blocking sqlplus [message #238540 is a reply to message #238531] Fri, 18 May 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How? This is the question.

Regards
Michel
Re: blocking sqlplus [message #238551 is a reply to message #238287] Fri, 18 May 2007 08:46 Go to previous messageGo to next message
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
Re: blocking sqlplus [message #238629 is a reply to message #238551] Fri, 18 May 2007 15:37 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Hi,
Revoke the create session privilege from user.Thats an esay way;)
Re: blocking sqlplus [message #238633 is a reply to message #238629] Fri, 18 May 2007 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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
Re: blocking sqlplus [message #238639 is a reply to message #238287] Fri, 18 May 2007 15:54 Go to previous messageGo to next message
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???
Re: blocking sqlplus [message #238642 is a reply to message #238639] Fri, 18 May 2007 15:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: blocking sqlplus [message #238643 is a reply to message #238636] Fri, 18 May 2007 16:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ana,

What about "mv sqlplus myprogram"?

Regards
Michel
Re: blocking sqlplus [message #238644 is a reply to message #238287] Fri, 18 May 2007 16:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DreamzZ,

Explain your last post I don't understand it.

Regards
Michel
Re: blocking sqlplus [message #238647 is a reply to message #238287] Fri, 18 May 2007 16:12 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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.
Re: blocking sqlplus [message #238648 is a reply to message #238647] Fri, 18 May 2007 16:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Agree, so my first answer.

Regards
Michel
Re: blocking sqlplus [message #238867 is a reply to message #238287] Sun, 20 May 2007 13:39 Go to previous messageGo to next message
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
Re: blocking sqlplus [message #238871 is a reply to message #238867] Sun, 20 May 2007 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yeah obviously there is a way to workaround your trigger: rename the executable.

Regards
Michel
Re: blocking sqlplus [message #238872 is a reply to message #238287] Sun, 20 May 2007 14:02 Go to previous messageGo to next message
zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan
Senior Member

If not created then tell me i would create for u here ok.
Re: blocking sqlplus [message #238873 is a reply to message #238872] Sun, 20 May 2007 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean? It is not clear.

Regards
Michel
Re: blocking sqlplus [message #238874 is a reply to message #238287] Sun, 20 May 2007 14:07 Go to previous messageGo to next message
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
Re: blocking sqlplus [message #238876 is a reply to message #238287] Sun, 20 May 2007 14:13 Go to previous messageGo to next message
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.
Re: blocking sqlplus [message #238877 is a reply to message #238876] Sun, 20 May 2007 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: blocking sqlplus [message #238919 is a reply to message #238287] Mon, 21 May 2007 00:39 Go to previous messageGo to next message
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?
Re: blocking sqlplus [message #239081 is a reply to message #238287] Mon, 21 May 2007 07:58 Go to previous messageGo to next message
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?
Re: blocking sqlplus [message #239133 is a reply to message #239081] Mon, 21 May 2007 09:38 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: Pinning Objects
Next Topic: How to install Oracle 10.2.0.2 on SPARC system??
Goto Forum:
  


Current Time: Mon Dec 05 21:04:40 CST 2016

Total time taken to generate the page: 0.08821 seconds