DQL versus SQL
James has often compared DQL and SQL, assigning the security weaknesses of one to the other. While there may be valid concerns for some ECM query languages, DQL is actually fairly secure from this type of attack. Don’t get me wrong, it isn’t foolproof, but it isn’t an apples to apples comparison. Let’s compare and look. Be sure to add comments to question or add.
DQL is Not SQL
This point needs to be made. DQL is not just a minor addition to SQL, but an interface similar to, though simpler than, the DFC. When one issues a DQL statement, it is parsed by Documentum into a corresponding SQL statement. The user’s authorization to act on any object is also verified. Some key differences are:
- In DQL, each item in a From statement is an object that is represented by two tables of different names in the database.
- If you try and query tables directly in DQL, it fails. DQL will only allow the querying of objects and registered tables.
- If an administrator registers a database table with Documentum, then that table can be directly queried. Rights must be assigned, within Documentum, to allow users to read or update a table. By default access is restricted to the registering owner and not the user community at large. While the registration of tables is typically done with lookup tables, this can be done with the underlying tables. I’ve never seen that done nor can I imagine a valid reason for doing registering those tables directly. See point 5 in the next section.
- A semicolon does terminate a DQL statement. Everything after the semicolon is discarded. This makes it hard to piggyback a destructive statement. The only exception I am aware of is the command line tool idql where you can run DQL in a batch. Of course if you have access to a Repository with an idql tool, you don’t need to mount an attack through a web interface. See point 6 below.
- You can execute a special command to pass a SQL statement through, but you need a fully functional DQL interface to execute it. You also have to be a super user, which is usually limited to a very small number of system accounts and not actually granted to users.
Because of these differences, DQL can be challenging and I sometimes connect to the servers directly to open a SQL window for some fancy select statements.
- DQL to create a user
- DQL to execute stored procedure
- DQL to see sessions
- DQL to list objects having duplicate names
DQL Security Practices
There are a few things that I do when deploying Documentum solutions that make life more secure from the SQL-style attacks:
- Don’t allow non-administrators access to a DQL window. This is pretty easy to achieve with the standard WDK-based interfaces.
- Limit the number of “super users”. I basically limit this to one or two system accounts, depending on the auditing requirements. The only way to pass SQL through to Documentum directly is if you are a super user, so limiting that to system accounts helps out here. Remember, your system administrators can probably hack your database directly anyway. You have to trust a few people, but not the world.
- Validate all input. By default, the WDK search screens are all parameterized. If you implement your own search options, do the same. Validating the entries helps, if for no other reason than to stop uncontrolled usage of wildcards.
- Only allow Read Queries from interfaces. When executing a DQL statement through code, you have to specify the type of query as a parameter on the call. Always pick READ_QUERY when you are using direct user input as variables. To be fair, I can’t think of a single instance where this wouldn’t be the case naturally.
- NEVER setup a core Documentum table as a registered table. If you do, you should be taken out back and shot. No exaggeration or exceptions.
- Keep your Repositories behind a firewall for all production environments to block un-approved clients. This is just basic common sense. If you need a few exceptions, make a few exceptions. Make the default policy restrictive.