DBSecWorx: Data Definition Language (DDL)

Oracle Data Definition Language (DDL) Versions All

Security Advisory

When viewed from the standpoint of data and database security, DDL, the ability to create and modify, and drop objects does not get the attention it deserves as a part of the attack surface.

The DDL command, CREATE is a dual threat security threat. Misused in one way it could be used to create a database edition and then to hide malicious code within that edition where it might be days, weeks, or months before it would be discovered, perhaps never. CREATE can also be used to exfiltrate data from a database, for example creating an external table.

Two DDL commands, ALTER and RENAME can be used to effect a Denial of Service attack How many hours will a database be down, and data unavailable if someone uses an ALTER statement to add or remove a column from a table? How long if they RENAME an essential function, package or procedure?

Two DDL commands, DROP and TRUNCATE, are more obvious issues from the standpoint of security. Even in a world of perfect backups where everything can be recovered with zero data loss, the base case scenario if a table is truncated or dropped, in the overwhelming majority of databases, would be a substantial outage during which the system might be unavailable or producing misinformation.

Syntax	Risk Rating	Comment
ALTER	4	Misuse can result in a Denial of Service attack.
ANALYZE	1	Zero risk but we still rate it a "1" because attackers can be remarkably creative.
COMMENT	1	Zero risk but we still rate it a "1" because attackers can be remarkably creative.
CREATE	8	Can be used to create and hide objects as well as assist in data exfiltration.
DROP	7	Misuse can result in data loss and a substantial system outage.
RENAME	4	Misuse can result in a Denial of Service attack.
TRUNCATE	7	Misuse can result in data loss and a substantial system outage.

DDL_LOCK_TIMEOUT

This startup (initialization, pfile, spfile) parameter is used to control the time limit for how many seconds a DDL statement can wait in a DML lock queue before failing.

The Oracle installation default value for this parameter is 0 which means NOWAIT: If the object is locked, for example DDL attempting to add a column to a table locked by another user, the statement will fail immediately. The maximum possible value is 1000000 which means the statement will wait forever to obtain a lock. The optimum setting for this parameter should be based on the system's maintenance cycle as no one should be performing any DDL on an application database, if possible, when the system is open and available.

Lock Timeout DDL_LOCK_TIMEOUT(<number_of_seconds>);

conn / as sysdba

      

      set linesize 121

      col name format a30

      col value format a30

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE 'ddl_lock%';

      

      -- valid range is 0 (the default) to 1,000,000

      ALTER SYSTEM SET ddl_lock_timeout=20;

      -- or

      ALTER SESSION SET ddl_lock_timeout=20;

Lock Timeout Demo

conn uwclass/uwclass@pdbdev

      

      CREATE TABLE test1 AS

      SELECT table_name, tablespace_name

      FROM user_tables;

      
      
      
        
          Step
          Session 1
          Session 2
        
        
          1
          conn uwclass/uwclass@pdbdev
          conn uwclass/uwclass@pdbdev
        
        
          2
          desc test1

          

          SELECT * FROM test1;
          desc test1

          

          SELECT * FROM test1;
        
        
          3
          LOCK TABLE test1

          IN exclusive MODE NOwAIT;
          RENAME test1 TO test2;
        
        
          4
          COMMIT;
          RENAME test1 TO test2;
        
        
          5
           
          ALTER SYSTEM SET ddl_lock_timeout=60;

          

          SELECT name, value

          FROM gv$parameter

          WHERE name LIKE 'ddl_lock%';
        
        
          6
          LOCK TABLE test2

          IN exclusive MODE nowait;
          RENAME test2 TO test3;
        
        
          7
          COMMIT;
           
        
      
      
      ALTER SYSTEM SET ddl_lock_timeout=0;

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE 'ddl_lock%';

To address issues related to DDL statements the best line of defense is DDL triggers. No database should exist without them.

Auditing in this situation is slightly better than useless but not by much as catching a DROP or TRUNCATE statements will tell only that something really bad just happened. Auditing will provide no assistance recovering the object, recovering the data, and explaining why the database was unavailable for hours.