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.

If you want to see a bunch of really useful DQL statements, check out this old post that I found by Prasad Sombhatta. Some of the queries include:

  • 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:

  1. Don’t allow non-administrators access to a DQL window. This is pretty easy to achieve with the standard WDK-based interfaces.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

5 thoughts on “DQL versus SQL”

  1. Maria says:

    Just a small comment
    5. A number of Documentums core tables gets registered by th e system itself. I think that they are needed from some of Documentums standard jobs. It makes it a bit hard to avoid having core tables registered.

    Cheers
    Maria

    Like

  2. Maria, you are correct. There are 25 registered tables in the standard 5.3 system, including a couple of views. They are restricted in definition so that only the system can use the tables beyond simple information retrieval.

    My comments against doing so was targeted at those implementing the system expanding that usage.

    Like

  3. Maria, you are correct. There are 25 registered tables in the standard 5.3 system, including a couple of views. They are restricted in definition so that only the system can use the tables beyond simple information retrieval.

    My comments against doing so was targeted at those implementing the system expanding that usage.

    Like

    • This command will create a user with an inline password. You will want to change the parameters as needed for your environment.

      create dm_user object
      set user_name = 'Dave User',
      set user_login_name = 'daveuser',
      set user_source = 'inline password',
      set user_password = 'p4ssw0rd'

      Like

Comments are closed.