CODEWITHSUNDEEP
oracle-databaseoracle10g
Shahid Hussain
Shahid Hussain

Reputation: 1779

Make row in a table read only on oracle?

I have a table with many rows.
For testing purpose my colleagues are also using same table. The problem is that some time he is deleting the row which I was testing and some time I.
So is there any way in oracle so I can make some specific rows to be read only so other should not delete and edit that?

Thanks.

Upvotes: 3

Views: 2475

Answers (3)

APC
APC

Reputation: 146179

There are a number of differnt ways to tackle this problem.

As Sun Tzu said, the best thing would be if you and your colleagues use data sets which do not collide.

For instance perhaps you could each have your own database instance, on local PCs; whether this will suit depends on a number of factors, not the least of which is your licensing arrangements with Oracle. Alternatively, you could have separate schemas in a shared database; depending on your application you may need to you synonyms or special connectioms.

Another approach: everybody builds their own data sets, known as test fixtures. This is a good policy, because testing is only truly valid when it runs against a known state; if we make assumptions regarding the presence or absence of data how valid are our test results? The point is, the tests should clean up after themselves, removing any data created in fixtures and by the running of tests. With this tactic you need to agree ranges of IDs for each team member: they must only use records within their ranges for testing or development work.

I prefer these sorts of approach because they don't really change the way the application works (arguably except using different schemas and synonyms). More draconian methods are available.

If you have Enterprise Edition you can use Row Level Security to protect your records. This is a extension of the last point: you will need a mechanism for identifying your records, and some infrastructure to identify ownership within the session. But in addition to preventing other users rom deleting your data you can also prevent them inserting, updating or even viewing records which are with your range of IDs. Find out more.

A lighter solution is use a trigger as A B Cade suggests. You will still need to identifying your records and who is connected (because presumably from time-to-time you will still want to delete your records.

One last strategy: take your ball home. Get the table in the state you want it and make a data pump export. For extra vindictiveness you can truncate the table at this point. Then any time you want to use the table you run a data pump import. This will reset the table's state, wiping out any existing data. This is just an extreme version of test scripts creating their own data.

Upvotes: 4

sri
sri

Reputation: 1

Oracle supports row level locking. you can prevent the others to delete the row, which one you are using. for knowing better check this link.

Upvotes: -1

A.B.Cade
A.B.Cade

Reputation: 16905

You can create a trigger that prevents deleting some specific rows. 

CREATE OR REPLACE TRIGGER trg_dont_delete
   BEFORE DELETE
   ON <your_table_name>
   FOR EACH ROW
BEGIN
   IF :OLD.ID in (<IDs of rows you dont want to be deleted>) THEN
       raise_application_error (-20001, 'Do not delete my records!!!');
   END IF;
END;

Of course you can make it smarter - make the if statement rely on user, or get the records IDs from another table and so on

Upvotes: 3

Related Questions