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 5 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. It will likely provide little or no assistance recovering the object, recovering the data, and explaining why the database was unavailable for hours.

Related Topics
Data Control Language (DCL)
DDL Event Triggers