|Oracle Data Definition Language (DDL)
|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
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.
|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 Demo||
|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.