0

In Oracle PL/SQL language, I would like to prevent user from using deleting * from table unless primary key specified.

How could I achieve this?

I've just found a similar question, but in a different language: prevent delete * from table unless primary key specified

Could you please help me to find a solution in Oracle PL/SQL language?

EDIT:

SELECT * FROM V$VERSION;

BANNER,CON_ID
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production,0
PL/SQL Release 12.1.0.2.0 - Production,0
CORE    12.1.0.2.0  Production,0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production,0
NLSRTL Version 12.1.0.2.0 - Production,0
Community
  • 1
  • 1
UltraCommit
  • 2,236
  • 7
  • 43
  • 61
  • 1
    As the given solution (in comments) on that question you linked, you have to do this in triggers. To make it usable for all tables, you would have to create a function that receives the table name and look into the dictionary to see what are the primary keys columns and with it check if they were filled. – Jorge Campos May 10 '17 at 12:49
  • 1
    What's the structure of the primary key in your table? Is it just and auto-increment integer field? – alejandrogiron May 10 '17 at 13:04
  • The trigger has to be general, and appliable to any table. – UltraCommit May 10 '17 at 13:34
  • How will you delete all employees from a department (for example)? Don't you just want to restrict `delete` without `where` clauses, or without `where` clauses referencing indexed columns? – William Robertson May 10 '17 at 18:00
  • If you want to restrict someone from deleting records you should revoke the privilege; you can then create an API in PL/SQL which allows them to perform the deletes you want to allow. – Jeffrey Kemp May 11 '17 at 02:38

2 Answers2

2

As someone observed in the referenced thread, this requirement amounts to I want to slow down every deletion executed in my database. Not to mention that sometimes bulk deletions are necessary.

This is a classic example of a trying to apply a technical fix to what is a political situation, a failure of process. I once knew a developer who initiated a delete of a massive (multi-million row) table because they didn't know how to scope variables in PL/SQL. But it would be wrong to penalize the entire development team because of one person's silly mistake.

The correct solution is to implement one or both of these:

  • make sure your developers (or any user with raw SQL access) understand how to write proper SQL
  • withdraw DELETE privileges from people who can't be trusted with them
  • have decent back-ups so it doesn't matter whether somebody deletes all the records
APC
  • 144,005
  • 19
  • 170
  • 281
1

not sure, but a delete will fail if a constraint is violated, so I'd look to see if you can create a constraint that will be violated (e.g. foreign key constraint where child record exists that must be deleted first).

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • But that won't change the behavior of the delete itself. A user could zap all the child records then delete all the parent records. – APC May 10 '17 at 14:38
  • @apc If OP's users have that much access to the DB that they don't want, they're in trouble. – Roger Cornejo May 10 '17 at 18:35