Like ancient Gaul, SQL is divided into three sub- languages. The DDL (Data Declaration Language) declares the data. This is where we find the data types, constraints, references and other structures that have to do with how the data stored . The DML (Data Manipulation Language) uses those declarations to change their contents or to invoke them. It does not change structures and schema objects.

I’ve been telling people for years that the DDL is the most important sub- language in SQL because it defines the universe. My main gripe with the way people write DDL is that they forget the basics. Go to any SQL forum and you will find table declarations that do not have a key or any references. Then what they want think of as a table is basically old COBOL or Fortran file definition written with SQL.

There are usually several ways to get the same query results in the DML. This is where most developers spend their time writing SQL, trying to . This is also where most books on SQL spend their time teaching you only this sublanguage. The assumption is that you are a USER and want to get some results, but you don’t have the authority to create a schema or to pass out database privileges.

The Truth

But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.

The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL. The basic statement looks like

The ADMIN user is a master of the universe. He brings other users into existence with the statement:

Note: syntaxes change amongst the many database platforms, and the code in this argument is for illustration. However, all the main database platforms have basically the same principles.

At this point, the user doesn’t have any privileges on the database. Each product will have slightly different rules as to what password and username can be. This is one reason that DCL can be very product specific. At this point, a user might not even be allowed to ask for help. ADMIN can GRANT the user privileges, but the user does not create privileges.

We can DROP USER < user name>, just as we do with any other CREATE <schema object> statement to remove the user from the schema.

The Privileges

The security model and SQL has the concept of table and column privileges. The names explain themselves, but today SQL has more schema objects that way for set this up

The most basic table privileges are:

• SELECT: this privilege let you run a SELECT statement on a table. However, since a VIEW is just how SQL encapsulates a query, you will need this privilege on every table or VIEW referenced in the query, which defines the VIEW. Since doing a query doesn’t change any structures or content, want to remember to have it on every table that you’re working with.
• INSERT: This privilege explains itself. But remember that your insertion must follow all the constraints of the table into which you are inserting. It does not override the DDL.
• DELETE: This privilege explains itself. It lets you do a basic operation on table. In case you forgot, it is possible to delete everything out of the table, but unlike files, the structure defined by the DDL remains even though there are no rows in the table.
• UPDATE: This privilege defines itself. Again, you cannot override the DDL.
• ALTER: This privilege allows you to use the ALTER statement on the table, which allows you to make changes to the structure of the table. The statement is worth an article in its own right. A quick word of warning. Please be careful about the references to the altered column.

This is usually where management would like to stop. Privileges that come after this basic set gets a bit fancy and very proprietary. Anyone that has the stronger privileges could potentially destroy your schema. If you’re going to grant them, you have to do it very carefully and selectively.

There is another far lesser used privilege for tables.

• REFERENCES [<column list]: This privilege allows the user to create a FOREIGN KEY that references this table. The columns involved are either given an explicit list or understood to be the PRIMARY KEY of this table.

I am bothered by the fact that most users do not seem to know how to use a REFERENCES clause in the DDL.

Some Of The Other Options

In this section, I will look at some of the interesting keywords and commands that you can use when granting privileges.

ALL [PRIVILEGES]:

This keyword explains itself but be careful. You may have just granted privileges more things than you think. I have a horror story about a consulting job I did decades ago. Two factions in the company had different sources for the encoding schemes of doctor’s specialties. One was an alphabetic abbreviation encoding and the other was numeric.

One faction would log onto the database, run a routine to change all the encodings over to the one that they liked, and run the reports. Shortly thereafter the next faction would log on to the machine and run the routine they had written to flip everything over to the other encoding scheme they liked. All developers had been granted ALL privileges, so the database was never quite the same from report to report.

Since these basic privileges were set up, SQL has grown. Dave McGovern once said that a standards committee never met the feature didn’t like. Today, an SQL Schema can have Collations, Procedures, Routines, Extended Datatypes, Translations and other objects. It’s a lot more than just a bunch of tables reference each other.

REVOKE

It should come as no surprise, but there is a corresponding statement to remove privileges. It should come as no surprise that the syntax follows the usual SQL format.

As a somewhat embarrassing historical note, the first ANSI SQL standard did not have a revoke statement. Frankly, I think we just forgot. We were in a rush to get out an ANSI standard, So that vendors would not come out with their own versions of the language. This meant we rushed a minimal specification through the X3H2 committee. The goal was to keep SQL from mutating into dialects.in the way that BASIC had.

WITH GRANT OPTION

The optional WITH GRANT OPTION on the GRANT statement grants this user the ability to extend their privileges on to another user. This gets a little tricky and we had to set up rules that say you cannot make circular references. In particular, a user cannot preserve his privileges by granting them back to himself so that ADMIN can’t revoke them. Nor can user X and user Y conspire together to grant each other privileges. However, it’s perfectly legal for users X1 and X2 to grant the same privileges to user Y. Another possibility is that user X1 grants to user X2, who grants to user Y. The general principle is that privileges “flow downhill”.

Iin these scenarios, the privileges are revoked from whichever user was granted them. Think of water flowing downhill; if it’s cut off upstream, it doesn’t reach the users downstream.

DENY

The DENY statement was not part of the original SQL standard, The extension is from Microsoft for their SQL Server and Azure SQL Database products. Here is a minimal skeleton of the syntax:

DENY is an interesting extension in that it says that a user cannot do what they are denied doing even if they are granted it in some other way. It is also kind of confusing in that in SQL Server, a user can be denied the right to a table, say table X.

But it does not deny them access to objects that use that table. For example:

UserY would be able to execute a SELECT statement on TableXView and retrieve data from TableX, no matter the DENY.

CASCADE

The CASCADE option is part of the REVOKE statement that not only removes the privileges from the target user, but also any privileges which had been granted with the WITH GRANT OPTION. This only makes sense, so that you cannot reconstruct tables from their subordinates from whom you still have privileges.

ALL option of DENY

The ALL option does not deny all possible permissions. It lets you get to the data, but you really can’t create any objects in the schema, nor can you execute schema objects that can change data. For practical purposes, using DENY ALL on a table will mean the user cannot DELETE, INSERT, REFERENCES, SELECT, nor UPDATE data in this table.

Likewise, if this table is a view, ALL means you cannot perform DELETE, INSERT, REFERENCES, SELECT, nor UPDATE.

From the DENY article in Microsoft Docs:

“The DENY ALL syntax has been scheduled to be deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. DENY specific permissions instead.”

The DENY statement is a mess, and you need to read the documentation for the version of SQL Server you’re using before using it. For example, at one point. a table-level DENY did not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy was preserved for the sake of backward compatibility. It is also a part of why this feature will be removed in a future release.

Using roles for Security

Using CREATE ROLE is a useful conceptual security modelling tool. Essentially what you do is you give a name to a bundle of different privileges. ADMIN can then assign this role to a new user. But more than that, it lets the database designer define schema objects at a higher level of abstraction than specific users.

That means I don’t have to say that Willie Loman is a salesman with certain privileges; instead, I can define the role of a salesman role with the tables, correlations, sequences, views, and whatever else I wanted in the schema for a generic salesman. It really changes the way you start thinking about things.

Using roles in this way is great for testing your security, because roles can be the same in all of your environments, whereas you will not want the same people working in your development databases to have the same access in your production databases that contain real people’s data.

I didn’t mention anything about encryption or other security things. Those are all going to be implementation defined. Database security becomes a whole issue and is rather elaborate at times.

My favorite example of how complicated this can get the system that actively lies to its users. If you have a certain level clearance, then you could find out that Clark Kent is a mild-mannered reporter for great metropolitan newspaper. When you have a higher-level clearance, you can find out that he’s actually Superman. Then there are systems that redo the computations, so that a user cannot find out what the actual value of sensitive information is. He’s not kept from it, but he’s lied to.

Using roles can help you out in this way, but that doesn’t make security easy. As complicated as queries can be, security can get even worse.