SQL RDBMS: Don't cross the streams

As Egon Spengler (Harold Ramis) said in Ghostbusters, "don't cross the streams", while in the SQL realm it thankfully won't cause total protonic reversal, it still is something that should be avoided.

DDL and DML are two different types of SQL Statements that can be sent from a client to the database. DDL stands for Data Definition Language, these are the statements used to create objects in a database, tables, triggers, stored procedures, etc. DML, Data Manipulation Language, is used to manage the data in tables via select, update, insert, and delete. 

tldr: When writing applications that use a SQL database, the SQL logins used by the applications should not execute DDL.

Architecturally, it is best to isolate the user that is creating objects in the database from the users that executing DML queries. This follows both the principles of Least Privilege and Separation of Concerns. Unless you are working with an application that manages databases, the deployment of the app should execute DDL and the application(s) that interact with the database should not. 

You may have noticed I said user (singular) that is creating/updating the objects in the database. As part of the deployment there should be a single user that owns all the objects in the schema/database because this will avoid broken ownership chains. Having a single owner for all objects means that only that user needs to be granted permissions to create objects in the schema/database, the other users, only need writes to access some or all of the objects in the database. 

As with all guidelines there are a couple of caveats. Many deployments will record information about the currently installed version in a table, so the install will run DDL for this. Additionally, when running the DDL to create the objects, the deployment may need to put default data in some tables.

Application logins may need to create temporary tables, although all else being equal, I'd rather see that in a stored procedure or function if possible. Putting that logic in a stored procedure or function creates an API for the database, which makes it easier to refactor the database without impacting the applications that use it.

Lastly, you may have noticed that the discussion the above elides away the difference between schema and database. In general only a single schema is needed in a database. However, on rare occasions, more often in research than in business, there may be reasons to create multiple schemas in a database. This will increase complexity and should be avoided unless needed. When multiple schemas are needed, objects should still only be created as part of the deployment/ Each schema may have its own owner, or all schemas and their deployed objects may be created and owned by a single SQL User.

Not following the above guidelines leads to unnecessary complexity that affects the maintainability of the database and the applications that use the database. Also, it often leads to unintended consequences because of that complexity.

Happy Coding.