Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    13

    Unanswered: How to restrict the user(Schema) from deleting the data from a table

    Hi All,

    I have scenario here.
    I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

    Below is the example.

    I have created a table employee in abc schema which has two values.

    EMPLOYEE
    ABC
    XYZ

    In the above scenario the abc user can only fire select query on the EMPLOYEE table.

    SELECT * FROM EMPLOYEE;

    He should not be able to use any other DML commands on that table.
    If he uses then Insufficient privileges error should be thrown.

    Can anyone please help me out on this.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.

    If USER can CREATE TABLE, then USER can DELETE from table.
    realize that there is no
    GRANT INSERT ON OWN_TABLE privilege.
    implicit is that table owner can do DML against own tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you can create an INSTEAD OF DELETE trigger, but that just puts a speed bump in their way. A determined user will delete rows no matter what you do.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    put an on delete before trigger on the table and issue a raise_application_error in the trigger. No row deletes. They could still truncate the table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •