Invariant Properties

What are DDL, DML, DCL and TCL?

SQL contains four distinct types of statements.

Data Definition Language

Data Definition Language (DDL) statements define the database structure. Think of this as the landlord that builds the warehouse but turns over the keys to the renter.

Statements:

• create – create tables, views, indexes, etc.
• alter – alter tables, views, indexes, columns, etc.
• drop – delete tables, views, indexes, etc.
• truncate – remove all of the records from a table
• comment – add comments to tables, columns, views, etc.
• rename – rename a table, view, etc.

Data Manipulation Language

Data Manipulation Language (DML) statements manage the data within the structure created by the DDL. Think of this as the tenant of the warehouse – it can use the warehouse but can’t knock down walls.

Statements:

• select – retrieve data
• insert – insert new data into a table
• update – update existing data within a table
• delete – delete data from a table
• call – call a PL/SQL or other stored procedure
• explain plan – explain how a querywill be executed
• lock table – lock a table to limit concurrency

Data Control Language

Data Control Language (CDL) statements control access rights to the data and schema. Think of these as locks on the doors, permission to move walls within the warehouse, etc.

Statements:

• grant – give the user additional privileges
• revoke – remove user privileges

Transaction Control Language

Transaction Control Language (TCL) statement are used to control transactions.

Statements:

• commit – save completed work
• rollback – undo completed work
• savepoint – mark a point that we can rollback to later without necessarily rolling back the entire transaction
• set transaction – set transaction options

Use Different Database User for Schema And Data Ownership

The schema should be owned by one database user, e.g., app_owner and the data should be owned by a different database user, e.g., app_user.

The owner should:

• have the ability to run DDL and DCL statements
• arguably not have the ability to run DML statements
• never be accessed via the webapp

The user should

• have the ability to run DML and TCL statements
• not have the ability to run DDL or DCL statements
• be accessible via the webapp

Cost/Benefit Analysis

There is a very favorable cost/benefit ratio for separating the ownership of schema and database. There is a slightly higher cost when creating and maintaining the database but it essentially eliminates the ability of a web intruder to destroy the database schema itself. The data, on the other hand, can still be nuked.