Preventing DDL Operations On Tables

In occasions we need have tables that are not modified in its structure by anyone, to do this, the first thing to do is configure or define a policy-based security schemes, privileges and roles, this should be sufficient to that can not be altered structures. We will see another method, less known and not almost never used, and also serves to perform such functions. We can even say that goes beyond, because although we have privileges on a table do not want to touch it by accident.

Oracle provides the clause “DISABLE TABLE LOCK” to perform this function, this tells the engine that must disable table locks, which are necessary to perform actions of type DDL.

The command is as follows;

ALTER TABLE TAB1 DISABLE TABLE LOCK;

 

DDL operations as below will be failed;

  • DROP TABLE TAB1
  • ALTER TABLE TAB1 MODIFY COL1 VARCHAR2(5)
  • ALTER TABLE TAB1 DROP COLUMN COL1
  • TRUNCATE TABLE TAB1

 

If you run any of the above commands will receive the following error;

ORA-00069: can not acquire lock – table locks disabled for tab1

 

Although in my opinion, add a column to a table is a DDL command (it should fail), in this case for Oracle is possible to change the table structure.

 

HTH – Antonio NAVARRO

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s