Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    45
    Provided Answers: 1

    Answered: DML, DDL, and WITH

    I use a SQL-execution GUI provided as part of a DB2-based application package. It provides a nice GUI for doing everything from selecting data to running procedures to all manner of data and object manipulation. They have a setting in which I can configure users for access to different levels of SQL. The stated intent is to have a class of user that cannot alter/add/remove objects or data but can still run select statements.

    But I took them to task when I found that such a user could run this: SELECT (*) FROM MyTable but not this: WITH SelectStuff AS (SELECT (*) FROM MyTable) SELECT * FROM SelectStuff. Setting aside for the moment the obvious fact that one would never use a complex query like this where a simple one would do, their more complete answer was this:

    "This security setting is working by designed to toggle DML vs DDL. Your "with" statement is not of the standard simple SQL language yet it's functionality gives the same results as using [a simple select statement]. [Our application] is designed to work with the standard select. If you are intending to use another "form" of standard DDL then you will see conflicts as the other form is treated as a form of DML as, when used, views are created & temporary tables etc."

    And that is about all they are going to say, but it raised my curiosity regarding that "form of DML".

    1. Do DML and DDL actually represent anything close to "allow changes" and "do not allow changes"?
    2. Is there some other type of "select" aside from a "standard select"?
    3. Why does "WITH" seem to be somehow in a separate class from other non-object/data-affecting keywords?

    That is, when I look up DML vs DML online, I get nothing about the complexity of statements, only the difference between managing objects and managing data. Or are they just trying to dumb all this down in an attempt to pacify ignorant me?

  2. Best Answer
    Posted by db2mor

    "Sounds like an unnecessarily restrictive parser in the application-layer - i.e. failing to recognize all forms of SELECT , e.g. such as those that begin with WITH ... (i.e. may be nothing to do with Db2).
    If the supplier only offers a subset of SELECT., that may be good enough for the requirements.
    And when something special is needed (such as a query with one or more WITH clauses) then deliver it via other means (e.g. a stored procedure that can be called from the restricted GUI) or via other access to the database."


  3. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Sounds like an unnecessarily restrictive parser in the application-layer - i.e. failing to recognize all forms of SELECT , e.g. such as those that begin with WITH ... (i.e. may be nothing to do with Db2).
    If the supplier only offers a subset of SELECT., that may be good enough for the requirements.
    And when something special is needed (such as a query with one or more WITH clauses) then deliver it via other means (e.g. a stored procedure that can be called from the restricted GUI) or via other access to the database.

  4. #3
    Join Date
    Feb 2011
    Posts
    45
    Provided Answers: 1
    Thanks. That confirms what I thought: this is not really based on allowing one set of users DDL and another group DML; it is something more specific that they have coded and then presented it 1) to the end user as though it simply blocks add/modify/delete access and 2) to me in tech support with a description based on misapplication of the terms DDL & DML.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •