Solved

how to protect deleting data from a table

Posted on 2014-01-23
21
407 Views
Last Modified: 2014-02-06
we have a table viewers and columns viewname,viewrid. viewrid is genartaed based on sequence so we dont want to delete any row in the table viewers
becos If someone deletes a viewers  and adds it back, everything fails since that viewername now has a new viewerid. how to protect a table data not to deleted by every one including schema owner.
0
Comment
Question by:ajaybelde
  • 9
  • 6
  • 5
  • +1
21 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 138 total points
ID: 39803516
>>how to protect a table data not to deleted by every one including schema owner

You cannot protect it from the owner.  For all non-dba users just revoke delete from them for that table.

To protect it from the owner:  Turn on auditing and threaten to FIRE anyone that does it.
0
 

Author Comment

by:ajaybelde
ID: 39803537
Our db will be relying on a few key fields to not be modified.
Specifically  and viewers.viewerid can’t be modified without causing major problems.
I was just going to put in flag saying active or suspended for both and not allow anyone
to delete them using by preventing it in an SP. is it possible
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 138 total points
ID: 39803561
>>is it possible

I don't know if the flag will work or not.  It's your requirement.  Can you add a flag and control stuff through a procedure, sure.  That is how I do things.  Applications do not have direct DML access to the base tables.  They access everything through procedures.

ALL applications should connect to the database with their own account so access can be easily controlled.

I'm just letting you know that the table owner and DBA accounts will be able to delete data from the table.
0
 

Author Comment

by:ajaybelde
ID: 39803599
can we just write before delete trigger to prevent. if yes how should the code look like
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 138 total points
ID: 39803608
I don't believe a trigger can prevent the action that triggered it from happening.

Think about it:  A trigger fires when an event takes place.  How can it then undo the event that fired it?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39803609
You can put a trigger on the table owned by another schema that raises an exception if you try to delete.

Then, use a system event trigger to block the owners from doing a drop so the trigger and table themselves will be protected from their respective owners.

But, like slightwv said above,  your DBAs will still be able to get around this.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39803614
a trigger to block deletes might look something like this

CREATE OR REPLACE TRIGGER trigger_owner.trg_block_deletes
    BEFORE DELETE
    ON other_table_owner.mytable
BEGIN
    raise_application_error(-20001, 'Deletes may not be performed on this table');
END;


note the schemas of the trigger and the table are different
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39803631
Actually, it looks like the trigger will protect deletes even from the owner without the other schema posted above.

Here's is my test case to show it:
drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
commit;

create or replace trigger tab1_trig
before delete on tab1
for each row
begin
	raise_application_error(-20001, 'Delete from tab1 not allowed.');
end;
/

show errors

delete from tab1;

Open in new window


This will prevent 'accidents' but not intended deletes.  The owner can just disable the trigger and delete the data.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39803643
>>>The owner can just disable the trigger and delete the data.

That's why I said to make the owner someone else.

And why you also use a system event trigger to prevent both owners from doing drops
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39803702
Also note, the owner of a table can also truncate that table which will remove all rows without actually issuing a "delete".

If you want to block that as well, then you'd have to alter the system event trigger that blocks drop statements to also include truncate
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39805514
just to add on to all that whatever said, we can stop the records being deleted from a table with the below options

1. revoke the delete privilege from other users but DBA/owner can still do anything with this table like delete records, drop table etc

or

2. setup a before delete trigger which gives an error but again DBA/owner can disable/delete the trigger itself and then delete records right

or

3. setup a read only account or an account which does not have delete privilege on the tables and this account should be used in the application/any other needs.  Get the DBA to lock the table owner so that the owner of the schema could not login in to database to delete or do any changes to this table. I have seen this kind of setup along with the trigger setup to handle similar situations in my past experience while working with OLTP systems few years before. BUT we need to note that still DBA or any other equivalent account with database administrator privs could play with that table object - so you will have agree a process to kick them out of job if you find anyone doing such things with this table.

Also i would suggest that you explore dbms_rls package/utilities to setup if required accordingly which is a fine grained access control / row level security for the records in the table.

Thanks,
0
 

Author Comment

by:ajaybelde
ID: 39817521
@slightwv,nav_kum_v.

what if i want to choose trigger option. how can i prevent Drop/Truncate table with trigger.
if not trigger any other option to prevent it from object owned schema
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39817560
I'm not slightwv or nav_kum_v - but I am the one that suggested using triggers  :)

to block drop or truncate events you need to use a ddl trigger

something like this...

CREATE OR REPLACE TRIGGER system.trg_check_alter_or_drop
    BEFORE DROP OR TRUNCATE
    ON DATABASE
DECLARE
    v_sql_text  ora_name_list_t;
    v_sql_lines INTEGER;
BEGIN
    IF (ora_dict_obj_type = 'TRIGGER'
    AND ora_dict_obj_name IN ('TRG_CHECK_ALTER_OR_DROP', 'YOUR_TRIGGER'))
    OR (ora_dict_obj_type = 'TABLE' AND ora_dict_obj_name = 'YOUR_TABLE')
   AND USER NOT IN ('SYS', 'SYSTEM', 'OTHER_AUTHORIZED_USER')
    THEN
        RAISE_APPLICATION_ERROR(-20001, USER || ' may not alter or drop ' || ora_dict_obj_name);
    END IF;
END;
/

Open in new window



You might not want to put it under the SYSTEM schema, that's fine; just make sure the trigger is put in a secured schema.  Note this trigger also protects itself.
0
 

Author Comment

by:ajaybelde
ID: 39817891
Thanks sdstuber and sorry for confusion!!.
 I have question on these 2 lines.
so do i need to create spearate trigger for each table again to prevent drop.?
if not why would i need trigger and table name
AND ora_dict_obj_name IN ('TRG_CHECK_ALTER_OR_DROP', 'YOUR_TRIGGER'))
 10      OR (obj_dict_obj_type = 'TABLE' AND ora_dict_obj_name = 'YOUR_TABLE')
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39817944
you can use the same trigger for multiple tables to prevent drop or truncate
just change the "=" to an "IN" clause

 OR (obj_dict_obj_type = 'TABLE' AND ora_dict_obj_name in ('TAB1','TAB2','TAB3','TAB4'))
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39817946
you can use the same trigger for multiple tables to prevent drop or truncate
just change the "=" to an "IN" clause

 OR (obj_dict_obj_type = 'TABLE' AND ora_dict_obj_name in ('TAB1','TAB2','TAB3','TAB4'))
0
 

Author Comment

by:ajaybelde
ID: 39818007
Just want to make sure. does this prevents droping all the triggers and tables on dummy2 schema except for system and sys.

CREATE OR REPLACE TRIGGER system.trg_check_alter_or_drop
    BEFORE DROP OR TRUNCATE
    ON DATABASE
DECLARE
    v_sql_text  ora_name_list_t;
    v_sql_lines INTEGER;
BEGIN
   IF (ora_dict_obj_type IN ('TRIGGER','TABLE'))
   AND (ora_dict_obj_owner = 'DUMMY2')
   AND USER NOT IN ('SYS', 'SYSTEM')
    THEN
        RAISE_APPLICATION_ERROR(-20001, USER || ' may not alter or drop ' || ora_dict_obj_name);
    END IF;
END;
/
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39818508
Yes, what you have should prevent the drops and truncates on any tables or triggers owned by that schema.

However,  in my suggestions above  the delete trigger would be owned by a different schema than the table owner.

Another thing to consider if you go with specific names is your system trigger should probably block RENAME events too.  Otherwise the owner could simply rename the object and then drop it.

simply change the BEFORE line in the trigger...

 BEFORE DROP OR TRUNCATE OR RENAME

of course, if you're using your version where you block everything regardless of name then this won't be a problem.

If the same user does own the delete trigger and the table then you'll probably want to block ALTER as well so they can't do a DISABLE on the trigger

 BEFORE DROP OR TRUNCATE OR RENAME or ALTER
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39818613
Out of curiosity:  Assuming you successfully block deletes from the table, can the viewerid be updated and cause the same issue?

Since it looks like you are going through great hurdles to even keep the table owner from deleting data, I'm thinking as the owner, I can leap through some holes to allow me to update that column.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 362 total points
ID: 39818877
adding to what slightwv said above.  I assume users can do inserts to the table as well.  So, in addition to updates, it would also be possible to create lots of corrupt data with a flood of improper inserts.

Even worse, depending on the key of the table bad inserts might be able to block the creation of valid data.

I'm assuming you'll want to allow updates and inserts though.  If not, then I suggest moving the table to a new tablespace and setting that tablespace to readonly.

Hopefully you see the pattern to the rules.

You can use dml triggers to block data changes
You can use ddl (system) triggers to block structural changes.

How far you want to carry those to block the owner from doing things is up to you.
0
 

Author Closing Comment

by:ajaybelde
ID: 39839905
Thank you for your excellent inputs.  app team still thinking on going with ddl tirgger or not
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now