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.