Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for restrictions on Database
icon9.gif  Trigger for restrictions on Database [message #263705] Thu, 30 August 2007 16:33 Go to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello,

Version : Oracle 9.2.0.1.0
Iam new to this forum....i neeed some suggestion regarding Triggers.
I have a Schema X which has the acces for all the users who have rights for Dev and production..because of this some users are updating and deleting the tables in Production.I need to write a trigger which restricts these users to manipulate the tables...
for example:
XX123
xx234
xx345
xx768

This trigger should restrict the user and give out a error message..

Any help ......

Thanks
Re: Trigger for restrictions on Database [message #263706 is a reply to message #263705] Thu, 30 August 2007 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
While logged onto Production database:
DROP USER <PROBLEM_USER> CASCADE;


There is NO need for any trigger!

Just terminate employment of anyone who violates policy on Production DB!
Re: Trigger for restrictions on Database [message #263712 is a reply to message #263705] Thu, 30 August 2007 18:16 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
okay, I have to ask the obvious...why grant access if they are not suppose to use it?
Re: Trigger for restrictions on Database [message #264070 is a reply to message #263712] Fri, 31 August 2007 11:15 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello

Thanks for the reply...
My requirement is the users have all the access on the prod..so that they can run some queries and retrieve data ....but i have to create a trigger which stops them from doing any changes on the tables ..only restrict them to retrive data....so whenever they try to INSERT,UPDATE,OR DELETE ANY table the trigger should fire giving the users an error message????Hope its little bit clear now

Thanks
Re: Trigger for restrictions on Database [message #264071 is a reply to message #264070] Fri, 31 August 2007 11:23 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
I don't quite understand this sentence...
Quote:

I have a Schema X which has the acces for all the users who have rights for Dev and production


...but I am assuming your just want to revoke insert,update and delete on production tables from schema x.
Re: Trigger for restrictions on Database [message #264072 is a reply to message #264070] Fri, 31 August 2007 11:23 Go to previous message
diku
Messages: 23
Registered: April 2007
Junior Member
Quote:
okay, I have to ask the obvious...why grant access if they are not suppose to use it?


CMERRY answered your question already.
Don't give those users the specified privileges. If all the users are not supposed to do DML just grant them SELECT and nothing else.
But you need to be careful about this.be sure to know whom you are restricting intentinally or not

[Updated on: Fri, 31 August 2007 11:24]

Report message to a moderator

Previous Topic: stored proc to add data to a new row?
Next Topic: LEFT JOIN works wrong?
Goto Forum:
  


Current Time: Wed Dec 07 08:30:14 CST 2016

Total time taken to generate the page: 0.08076 seconds