OraFAQ Forum: SQL & PL/SQL » How to disable any command for user who has full rights

Home » SQL & PL/SQL » SQL & PL/SQL » How to disable any command for user who has full rights

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

How to disable any command for user who has full rights [message #616447]

Tue, 17 June 2014 02:07

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

I have created one user and he has all rights.
I want to disable DROP command for this user.
I want that whenever i execute DROP command the command should not run.
Please tell how to do that.

Thanks

Report message to a moderator

Re: How to disable any command for user who has full rights [message #616448 is a reply to message #616447]

Tue, 17 June 2014 02:13

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Owner can always drop its own objects, you can't REVOKE anything in order to prevent it. Therefore, you'll have to create a database trigger (BEFORE DROP ON DATABASE) which will take care about it. Here's an example.

Report message to a moderator

Re: How to disable any command for user who has full rights [message #616449 is a reply to message #616448]

Tue, 17 June 2014 02:14

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

If he really has all the rights, he can just give it back to himself and disable/bypass anything you try to use to stop him.

Perhaps it might be a better idea to only grant what is needed?

Report message to a moderator

Re: How to disable any command for user who has full rights [message #616458 is a reply to message #616447]

Tue, 17 June 2014 02:59

John Watson
Messages: 9000
Registered: January 2010
Location: Global Village

Senior Member

If you are using SQL*Plus, you can disable commands using the PRODUCT_USER_PROFILE table. Whether that will work for other clients is dependent on the client software.

Report message to a moderator

Re: How to disable any command for user who has full rights [message #616571 is a reply to message #616448]

Wed, 18 June 2014 07:30

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

thanks @littlefoot & @john there is no records in PRODUCT_USER_PROFILE table

[Updated on: Wed, 18 June 2014 07:30]

Report message to a moderator

Re: How to disable any command for user who has full rights [message #616576 is a reply to message #616571]

Wed, 18 June 2014 08:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

jgjeetu wrote on Wed, 18 June 2014 18:00

there is no records in PRODUCT_USER_PROFILE table

You need to insert the required values. Have a look at http://www.idevelopment.info/data/Oracle/DBA_tips/Security/SEC_1.shtml

Report message to a moderator

Previous Topic:	DBMS_JOB: Numerous jobs
Next Topic:	How to add new partition on a table if Default partition is existing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 19 16:36:34 CDT 2026