Link to home
Create AccountLog in
Avatar of Patricia Timm
Patricia TimmFlag for United States of America

asked on

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

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???
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of Patricia Timm

ASKER

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...
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
thanks