How best to stop rows being deleted?

[Thargy]

All,

I have a table with some data in it, that is loaded whenever a new system is installed. It defines some standard items which will be useful for the life of the application, and should therefore never be altered or deleted.

However, users can legitimately configure their own standard items, which obviously belong in the same table, as they have an identical structure.

How best can I prevent users altering or deleting the standard rows, but still let them insert, update and delete on their own user-configurable rows?

I don't want to go the trigger route, ideally I'd like to do this with some sort of structure, e.g. make the table partitioned, and put the fixed rows in a partition in a read-only tablespace.

any ideas?

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

John,

I believe that there is an easy/acceptable route for you that doesn't involve partitioning/read-only tablespaces:

1) Create the standard table as a particular user,
2) GRANT SELECT ON <standard table> to <role>;
3) Disable log-ins to the owning user;
4) Grant role membership to the users that you want to access the table.

No one (except DBAs or "super-privileged") can update the standard table.

Let us know if this is an acceptable option.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Dave,

I didn't make myself clear engough, sorry.

I want one table, which ordinary users can insert, update and delete, but not update or delete the essential records. If I put the table in a different schema and then only grant select, how will users be able to add their configurable items to the table?

Regards

John

Grinding away at things Oracular

 

[SantaMufasa]

My initial reaction to your most recent clarification is that "partitioning" (involving a read-only tablespace) or using "triggers", are your most viable options (recognising, of course, that you have an aversion, in this case, to using triggers).

BTW, what is the nature of your reluctance to using a trigger, which seems to me to be a most viable/efficient option?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Dave,

My reluctance to use triggers is largely based on Tom Kyte's recommendation to avoid 'automagic' things happening within triggers. If they're not well documented, such things embed themselves into the database, and eventually none dare remove them, 'just in case'. Also, triggers involve writing code, which will need debugging, unit testing etc. and I don't like the sound of all that hard work.

If I can achieve declarative control over data, the db engine will do all the donkey work for me, and I can sit and twiddle my thumbs (or somesuch). I've recently forced some buggy code out of the woodwork, by dint of applying integrity constraints, so I'm a firm believer in this approach.

Partly to answer my own question, I suppose that if a trigger stops something happening, that's not so bad, as it would make the user investigate why the db is 'broken' and discover that he/she was attempting something disallowed.

Any words of wisdom, or should I just crack on and write those triggers?

John

Grinding away at things Oracular

 

[SantaMufasa]

John,

I understand, and generally agree with, Tom Kyte's assertions about triggers. But, as you so aptly pointed out, if a trigger behaves more like a constraint that prevents "misbehaviour", versus a trigger that performs some "automagical" enhancement/modification to data, then (IMHO) it favourably balances the risks that a trigger represents.

So I suggest that you "crack on and write those triggers."

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
One other approach involves a view and 2 tables..

1: Standard table ( no mods except by DBA)
2: UserTable ( same structure) - users can add/delete/etc.

Create a UNION ALL view combining those 2 tables..

Grant select on the view..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Please pardon my dimness, Turkbear, but how does John allow his users to INSERT/UPDATE/DELETE their own rows yet not touch standard rows, using the VIEW/UNION approach?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

By using instead of triggers (couldn't resist)

A simple trigger to stop delete and update of specific rows containg a unique identifier of some sort would take about 3 lines of code, not a particully difficult task.



Bill
Oracle DBA/Developer
New York State, USA

 

[Turkbear]

Hi,
With 2 tables:

Users cannot modify the Standard Table so they cannot change any of the Standard rows..

They also cannot update/modify using the View since they are only granted Select rights to it..

The UNION combines the Standard Table's data and the UserDefined Tables data..

Does not seem to require any trigger code..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

If you don't want to write a trigger because you don't think that you will be able to do it, then supply us with the information and we can write it for you. Tom Kyle is NOT and has NEVER said not to use triggers, he simply said not to hide complex data manpulation inside of a trigger that the user will have no way of finding.

Bill
Oracle DBA/Developer
New York State, USA

 

[Thargy]

Gents,

I have used another of my standard 'laziness' methods, and done the deal with a materialized view.

Demo code follows:-

Create sample table and populate with essential and user-configurable data

CREATE TABLE NO_DELETE
(no_delete_id INTEGER PRIMARY KEY,
 description  VARCHAR2(20)
);

ALTER TABLE NO_DELETE
ADD CONSTRAINT UC_NO_DELETE_DESCRIPTION
UNIQUE (DESCRIPTION);

INSERT INTO NO_DELETE VALUES(1,'PERMANENT_1');                                                                              
INSERT INTO NO_DELETE VALUES(2,'PERMANENT_2');                                                                              
INSERT INTO NO_DELETE VALUES(3,'PERMANENT_3');                                                                              
INSERT INTO NO_DELETE VALUES(4,'PERMANENT_4');                                                                              
INSERT INTO NO_DELETE VALUES(5,'PERMANENT_5');                                                                              

INSERT INTO NO_DELETE VALUES(6,'USER_CONFIG_6');                                                                              
INSERT INTO NO_DELETE VALUES(7,'USER_CONFIG_7');                                                                              
INSERT INTO NO_DELETE VALUES(8,'USER_CONFIG_8');                                                                              
INSERT INTO NO_DELETE VALUES(9,'USER_CONFIG_9');                                                                              
INSERT INTO NO_DELETE VALUES(10,'USER_CONFIG_10');

Now, create a materialized view, using the parent table as source.

CREATE MATERIALIZED VIEW NO_DELETE_MV
REFRESH ON COMMIT
AS

SELECT COUNT(*) NUM_PERMANENT_RECORDS
  FROM NO_DELETE 
 WHERE DESCRIPTION IN ('PERMANENT_1',
	                   'PERMANENT_2',
					   'PERMANENT_3',
					   'PERMANENT_4',
					   'PERMANENT_5');
					   
ALTER TABLE NO_DELETE_MV
ADD CONSTRAINT CHK_ALL_PERM_RECORDS_OK
CHECK (NUM_PERMANENT_RECORDS = 5);

Now try to delete a required record

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 15 21:16:02 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> delete from no_delete where description = 'PERMANENT_1';

1 row deleted.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (WINPATH.CHK_ALL_PERM_RECORDS_OK) violated

Now try to alter the description of a permanent record

SQL> update no_delete set description = 'PERM_1' WHERE DESCRIPTION = 'PERMANENT_1';

1 row updated.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (WINPATH.CHK_ALL_PERM_RECORDS_OK) violated

Now edit a user-configurable record

SQL> update no_delete set description = 'USER_CONFIG_66' WHERE DESCRIPTION = 'USER_CONFIG_6';

1 row updated.

SQL> COMMIT;

Commit complete.

I would welcome a critiqe of this technique.

Regards

Tharg

Grinding away at things Oracular