This topic provides information about the SQL statement, SQL security access rights and any authorization implications for DDL.
Statement | Authorization Required | Authorization Implications |
COMMENT ON | Creator of the view or table. | None |
ALTER AREA | ALTER access for the area | None |
ALTER DATABASE | ALTER access for the database | None |
ALTER TABLE | ALTER access for the table. * | None |
CREATE AREA | Accessor must be a Global Owner and have the access rights required to execute statements [such as CREATE TABLE]. | None |
CREATE DATABASE | Accessor must be a Global Owner and have the access rights required to execute statements [such as CREATE TABLE]. | None |
CREATE INDEX | INDEX access for the table being indexed. | None |
CREATE SCHEMA | Accessor must be a Global Owner and have the access rights required to execute statements [such as CREATE TABLE] included in the CREATE SCHEMA statement. | The only security effects are those of the statements included in the CREATE SCHEMA statement. |
CREATE SYNONYM | None | The owner of the synonym [the ID executing the CREATE VIEW statement] is recorded in Datacom Datadictionary . |
CREATE TABLE | CATALOG access for the database in which the table is created. [CATALOG rights are granted with the Online Security Maintenance Facility.] | The user is the owner of the created table with grantable SELECT, UPDATE, INSERT, DELETE, and ALTER access rights. |
CREATE VIEW | SELECT access for each table or view in the statement [or PUBLIC must have the SELECT access right for that table or view]. | The creator of the view always acquires the SELECT access right on the view. The SELECT access right is grantable only if the creator has the grantable SELECT access right on every table or view identified in the first FROM clause of the SELECT statement of the view. The creator also acquires any other access right that can apply to the view and that is an access right which the creator has been granted on the tables or views identified in the first FROM clause of the SELECT statement of the view. The access right is grantable only if all of the access rights from which it is derived are grantable. No column-level access rights are automatically granted. If the accessor has UPDATE access rights at only the column level for a table or view in the subselect, the UPDATE access right is not inherited for the view. |
DROP INDEX | INDEX access for the table being indexed. | None |
DROP SYNONYM | Creator of the synonym or a Global Owner. * | None |
DROP TABLE | Creator of the table or owner of the database containing the table. * | All owner definitions and all authorizations involving the table are revoked. |
DROP VIEW | Creator of the view or a Global Owner. * | All authorizations on the view are revoked. |
* Datacom/DB does not process a DROP or ALTER statement and returns a -118 SQL return code when theDatacom Datadictionary entity-occurrence definition of the table, view, or synonym specified is protected with a password or a Lock Level 1 or 2. For more information about passwords and lock levels, see theDatacom Datadictionary documentation. |
Improve Article
Save Article
Prerequisite – DDL, DQL, DML, DCL and TCL Commands
DDL:
DDL is Data Definition Language which is used to define data structures. For example: create table, alter table are instructions in SQL.
DML:
DML is Data Manipulation
Language which is used to manipulate data itself. For example: insert, update, delete are instructions in SQL.
Difference between DDL and DML:
It stands for Data Definition Language. | It stands for Data Manipulation Language. |
It is used to create database schema and can be used to define some constraints as well. | It is used to add, retrieve or update the data. |
It basically defines the column [Attributes] of the table. | It add or update the row of the table. These rows are called as tuple. |
It doesn’t have any further classification. | It is further classified into Procedural and Non-Procedural DML. |
Basic command present in DDL are CREATE, DROP, RENAME, ALTER etc. | BASIC command present in DML are UPDATE, INSERT, MERGE etc. |
DDL does not use WHERE clause in its statement. | While DML uses WHERE clause in its statement. |