subsets of SQL are ddl, dml, and dcl and where are they defined

Patricia Timm
Patricia Timm used Ask the Experts™
the subsets of SQL are ddl, dml, and dcl. These allow you access to perform actions in the database. Where are they defined and can anyone change them or are they admin rights???
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
They are more like categories or groups of the commands available in the thing called SQL (Structured Query Language).

They are:
Data Definition Language
Data Manipulation Language
Data Control Language

What user can perform what SQL command is controlled by grants.

Not sure exactly what you are asking?
These subsets are artificial and they can differ dialect from dialect… but the Wiki description is correct.

In fact (and with appropriate rights and/or sufficient level of imagination) you may convert everything to DML, ie. SELECT, UPDATE, DELETE, INSERT. Any command belonging to DDL and DCL has its own representation in data structures which are most likely stored in data tables or in structures equal to data tables. This is because SQL engines do have resources to efficiently maintain such structures.

So CREATE TABLE can be converted to appropriate INSERT INTO sys.objects, ALTER TABLE means  UPDATE sys.objects etc. etc.


Where would one define user rights - DDL or DCL. Would they be stored somewhere in the DBMS? IF I wanted to limit someone to DML where they can only manipulate data in the tables (insert, update, delete) Where would I define the user as DML so they could not manipulate object in the database - like deleting tables, altering tables...
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Most Valuable Expert 2012
Distinguished Expert 2018
>>Where would I define the user as DML

You don't.

The terms DML, DDL, DCL are abstract terms.  They are not physical privileges/roles/permissions.

As pcelba pointed out, those terms are different between different database vendors.

To try and answer the last question asked:
You limit a user's database permissions through grants.  If the product supports it, preferably through ROLES. What gets granted is defined by the project you are working on.
User rights are defined by commands falling into the DCL group. Some (not so advanced) SQL engines do not support such commands and you have to restrict user rights in the client application. User rights are stored in the DBMS obviously but that's not important. We just need to know commands which we may use to set/change the rights.

Development environment can ignore user rights so all development users do have full administration rights with no restrictions. User roles are necessary in the test and production environments primarily.

Roles are great at the SQL engine level. Many applications are implementing security at the application level so they use just one common SQL user to access the database and all the decisions whether the user can or cannot do something are coded in the middle tier of the application. Of course, to have restricted rights at the SQL engine level is safer.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial