Home » SQL & PL/SQL » SQL & PL/SQL » How to restrict the user(Schema) from deleting the data from a table (Oracle 10g)
How to restrict the user(Schema) from deleting the data from a table [message #569845] Fri, 02 November 2012 08:40 Go to next message
kumar0828
Messages: 15
Registered: August 2012
Location: Mysore
Junior Member
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.
Re: How to restrict the user(Schema) from deleting the data from a table [message #569846 is a reply to message #569845] Fri, 02 November 2012 08:45 Go to previous messageGo to next message
Littlefoot
Messages: 18846
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, this doesn't make much sense because table owner can do anything he/she wants.

Anyway: a trigger can be one option (although the same user - read: owner - can easily disable or drop it):
SQL> create table test (col number);

Table created.

SQL> create or replace trigger trg_b_del
  2    before delete on test
  3    for each row
  4  begin
  5    raise_application_error(-20001, 'You can not delete records from this table');
  6  end;
  7  /

Trigger created.

SQL> insert into test (col) values (1);

1 row created.

SQL> delete from test;
delete from test
            *
ERROR at line 1:
ORA-20001: You can not delete records from this table
ORA-06512: at "SCOTT.TRG_B_DEL", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_B_DEL'


SQL>
Re: How to restrict the user(Schema) from deleting the data from a table [message #569847 is a reply to message #569845] Fri, 02 November 2012 08:45 Go to previous messageGo to next message
cookiemonster
Messages: 10592
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't.
The table will have to go in a different schema.
Re: How to restrict the user(Schema) from deleting the data from a table [message #569864 is a reply to message #569846] Fri, 02 November 2012 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Anyway: a trigger can be one option (although the same user - read: owner - can easily disable or drop it):


It is not mandatory that the trigger is in the table owner schema. It can be in another schema (a security officer one for instance).
In addition, it does not need to be a row trigger, a statement trigger is sufficient and would be more efficient.

SQL> create trigger ttt before delete on scott.emp 
  2  begin
  3    raise_application_error(-20001, 'You can not delete records from this table');
  4  end;
  5  /

Trigger created.

SQL> connect scott/tiger
Connected.
SCOTT> delete emp;
delete emp
       *
ERROR at line 1:
ORA-20001: You can not delete records from this table
ORA-06512: at "MICHEL.TTT", line 2
ORA-04088: error during execution of trigger 'MICHEL.TTT'


Regards
Michel

Re: How to restrict the user(Schema) from deleting the data from a table [message #569875 is a reply to message #569845] Fri, 02 November 2012 12:35 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
Then there is the scenario where the user does a CTAS from the original table, drops the original, then renames the new table.
Re: How to restrict the user(Schema) from deleting the data from a table [message #569880 is a reply to message #569875] Fri, 02 November 2012 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a DDL trigger to prevent this (drop or rename of the original table)?

Regards
Michel
Re: How to restrict the user(Schema) from deleting the data from a table [message #569888 is a reply to message #569880] Fri, 02 November 2012 15:03 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
But user didn't mention that they cannot drop Wink
Re: How to restrict the user(Schema) from deleting the data from a table [message #569889 is a reply to message #569888] Fri, 02 November 2012 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indeed. Laughing

Regards
Michel
Re: How to restrict the user(Schema) from deleting the data from a table [message #569908 is a reply to message #569889] Fri, 02 November 2012 23:53 Go to previous messageGo to next message
ashwani.oca
Messages: 1
Registered: November 2012
Location: Bangalore
Junior Member
Hi kumar0828,

My suggestion is to use the FGA(Fine Grained Auditing) I have never user in this
scenario. But I had used it when i had to restrict the DML operation on
any table by using RLS in this scenario you can restrict the access on your table.....

May be this suggestion will help in your scenario....

Re: How to restrict the user(Schema) from deleting the data from a table [message #569913 is a reply to message #569908] Sat, 03 November 2012 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean FGAC: Fine-Grained Access Control which related to RLS (Row Level Security) packages and also known as VDP (Virtual Private Database).
It could be done but is actually more complex than a simple trigger.

Regards
Michel

Re: How to restrict the user(Schema) from deleting the data from a table [message #569916 is a reply to message #569913] Sat, 03 November 2012 01:52 Go to previous message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An example how you can use VPD to hide the rows when you execute a DELETE:
SQL> create or replace function VPD_DELETE_EMP (p1 varchar2, p2 varchar2) return varchar2
  2  is
  3  begin
  4    return '1=2';
  5  end;
  6  /

Function created.

SQL> declare
  2    l_except exception;
  3    pragma exception_init(l_except, -28102);
  4  begin
  5   begin
  6     dbms_rls.drop_policy (
  7      object_schema    => 'SCOTT', 
  8      object_name      => 'EMP', 
  9      policy_name      => 'FORBID_DELETE_EMP'
 10     );
 11    exception when l_except then null;
 12   end;
 13   dbms_rls.add_policy (
 14    object_schema    => 'SCOTT', 
 15    object_name      => 'EMP', 
 16    policy_name      => 'FORBID_DELETE_EMP', 
 17    function_schema  => 'MICHEL',
 18    policy_function  => 'VPD_DELETE_EMP', 
 19    statement_types  => 'DELETE');
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> @sc
Connected.
SCOTT> select count(*) from emp;
  COUNT(*)
----------
        14

1 row selected.

SCOTT> delete emp;

0 rows deleted.

Regards
Michel

[Updated on: Sat, 03 November 2012 04:19]

Report message to a moderator

Previous Topic: Query without outer join
Next Topic: Need Query, but cant test it...
Goto Forum:
  


Current Time: Thu Apr 24 03:11:56 CDT 2014

Total time taken to generate the page: 0.16955 seconds