Page 86 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 5: Integrity Constraints

In general DCL statements begin with one of the following keywords: GRANT, or REVOKE. Notes
There are different sub-categorizations of DCL statements based on the type of action to be
granted or revoked. For example there are DCL statements related to statements, packages, and
utilities. These statements generally all contain clauses referring to the name of a database
privilege or authority, the name of the database object associated with the privilege if there is
one, and the name of the user that will be modified. DCL statements can also be used to delegate
the authority to grant and revoke specific privileges to other users.
DCL statements can be executed from a variety of interactive and application interfaces although
they are most commonly executed in scripts or from DB2(R) tools that support SQL statement
execution.
For security reasons it is important that privilege management be used to minimize the number
of users that can modify the privileges in order to prevent data from being data accidentally or
maliciously modified, retrieved, or lost. It follows therefore that to retrieve, insert, update, or
delete data in a database users require particular authorities which should generally be restricted
to the smallest sub-set of database users possible.
For the specific authorities required to execute a DCL statement, refer to the specific SQL Reference
syntax topic for that statement.

GRANT

This command is used for gives access privileges to users for database. The syntax is:
GRANT dba to username;

REVOKE

This command is used for withdraws access privileges to users for database. The syntax is:
REVOKE permissions on tablename from username;

5.4 Embedded SQL

The SQL standard defines embeddings of SQL in a variety of programming languages, such as
Pascal, PL/I, Fortran, C, and Cobol. A language in which SQL queries are embedded is referred
to as a host language, and the SQL structures permitted in the host language constitute embedded
SQL.

Programs written in the host language can use the embedded SQL syntax to access and update
data stored in a database. This embedded form of SQL extends the programmer’s ability to
manipulate the database even further. In embedded SQL, all query processing is performed by
the database system. The result of the query is then made available to the program one tuple
(record) at a time.
An embedded SQL program must be processed by a special preprocessor prior to compilation.
Embedded SQL requests are replaced with host-language declarations and procedure calls that
allow run-time execution of the database accesses. Then the resulting program is compiled by
the host-language compiler. To identify embedded SQL requests to the preprocessor, we use the
EXEC SQL statement; it has the form:
EXEC SQL <embedded SQL statement > END-EXEC
The exact syntax for embedded SQL requests depends on the language in which SQL is embedded.
For instance, a semi-colon is used instead of END-EXEC when SQL is embedded in C or Pascal.

LOVELY PROFESSIONAL UNIVERSITY 79