ISO/IEC 9075-2:1999
(Main)Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)
Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)
Technologies de l'information — Langages de base de données — SQL — Partie 2: Fondations (SQL/Fondations)
General Information
Relations
Frequently Asked Questions
ISO/IEC 9075-2:1999 is a standard published by the International Organization for Standardization (ISO). Its full title is "Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)". This standard covers: Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)
Information technology - Database languages - SQL - Part 2: Foundation (SQL/Foundation)
ISO/IEC 9075-2:1999 is classified under the following ICS (International Classification for Standards) categories: 35.060 - Languages used in information technology. The ICS classification helps identify the subject area and facilitates finding related standards.
ISO/IEC 9075-2:1999 has the following relationships with other standards: It is inter standard links to ISO/IEC 9075-2:1999/Amd 1:2001, ISO/IEC 9075-2:1999/Cor 1:2000, ISO/IEC 9075-2:1999/Cor 2:2003, ISO/IEC 9075:1992/Cor 3:1999, ISO/IEC 9075:1992/Cor 1:1996, ISO/IEC 9075:1992; is excused to ISO/IEC 9075-2:1999/Amd 1:2001, ISO/IEC 9075-2:1999/Cor 2:2003, ISO/IEC 9075-2:1999/Cor 1:2000. Understanding these relationships helps ensure you are using the most current and applicable version of the standard.
You can purchase ISO/IEC 9075-2:1999 directly from iTeh Standards. The document is available in PDF format and is delivered instantly after payment. Add the standard to your cart and complete the secure checkout process. iTeh Standards is an authorized distributor of ISO standards.
Standards Content (Sample)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12 Access control
12.1
Function
Define privileges and role authorizations.
Format
::=
|
Syntax Rules
None.
Access Rules
None.
General Rules
1) For every involved grantee G and for every domain D1 owned by G, if all of the following are
true:
a) The applicable privileges of G include the grantable REFERENCES privilege on every
column referenced in the SC included in a domain constraint descriptor
included in the domain descriptor of D1.
b) The applicable privileges of G include the grantable EXECUTE privileges on all SQL-
invoked routines that are subject routines of s contained in SC.
c) The applicable privileges of G include the grantable SELECT privilege on every table T1
and every method M such that there is a MR contained in SC such that
T1 is in the scope of the of MR and M is the method identified
by the of MR included in a domain constraint descriptor included in the
domain descriptor of D1.
d) The applicable privileges of G include the grantable SELECT privilege WITH HIERARCHY
OPTION on at least one supertable of the scoped table of every
contained in SC.
e) The applicable privileges of G include the grantable USAGE privilege on all domains,
character sets, collations, and translations whose s, s,
s, and s, respectively, are included in the domain
descriptor of D1.
Access control 583
12.1
then for every privilege descriptor with USAGE, a grantor of ‘‘_SYSTEM’’, object D1,
and grantee G that is not grantable, the following is effectively executed
with a current user identifier of ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT USAGE ON DOMAIN D1 TO G WITH GRANT OPTION
2) For every involved grantee G and for every collation C1 owned by G, if the applicable privileges
of G include a grantable USAGE privilege for the character set name included in the collation
descriptor of C1 and a grantable USAGE privilege for the translation name, if any, included
in the collation descriptor of C1, then for every privilege descriptor with USAGE, a
grantor of ‘‘_SYSTEM’’, object of C1, and grantee G that is not grantable, the following
statement> is effectively executed with a current user identifier of ‘‘_SYSTEM’’ and without
further Access Rule checking:
GRANT USAGE ON COLLATION C1 TO G WITH GRANT OPTION
3) For every involved grantee G and for every translation T1 owned by G, if the applicable priv-
ileges of G contain a grantable USAGE privilege for every character set identified by a
acter set specification> contained in the of T1, then for every privilege
descriptor with P, a grantor of ‘‘_SYSTEM’’, object of T1, and grantee G that is not
grantable, the following is effectively executed as though the current user
identifier were ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT P ON TRANSLATION T1 TO G WITH GRANT OPTION
4) For every table T specified by some involved privilege descriptor and for each view V owned
by some involved grantee G such that T or some column CT of T is referenced in the
expression> QE of V,or T is a supertable of the scoped table of a con-
tained in QE, let RT , for i ranging from 1 (one) to the number of tables identified by the
i
reference>s contained in QE, be the s of those tables. For every column CV of V:
a) Let CRT , for j ranging from 1 (one) to the number of columns of RT that are underlying
ij i
columns of CV, be the s of those columns.
b) If, following successful execution of the , all of the following are true:
i) The applicable privileges of G include grantable SELECT privileges on all of the columns
CRT .
ij
ii) The applicable privileges of G include grantable EXECUTE privileges on all SQL-
invoked routines that are subject routines of s contained in QE.
iii) The applicable privileges of G include grantable SELECT privilege on every table T1
and every method M such that there is a
that T1 is in the scope of the of MR and M is the method
identified by the of MR.
iv) The applicable privileges of G include grantable SELECT privilege WITH HIERARCHY
OPTION on at least one supertable of the scoped table of every
that is contained in QE.
then the following is effectively executed as though the current user
identifier were ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT SELECT (CV)ON V TO G WITH GRANT OPTION
584 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.1
c) If, following successful execution of the , the applicable privileges of G
will include REFERENCES(CRT ) for all i and for all j, and will include a REFERENCES
ij
privilege on some column of RT for all i, then:
i
i) Case:
1) If all of the following are true:
A) The applicable privileges of G will include grantable REFERENCES(CRT ) for
ij
all i and for all j, and will include a grantable REFERENCES privilege on some
column of RT for all i.
i
B) The applicable privileges of G include grantable EXECUTE privileges on all
SQL-invoked routines that are subject routines of s con-
tained in QE.
C) The applicable privileges of G include grantable SELECT privilege on every table
T1 and every method M such that there is a
in QE such that T1 is in the scope of the of MR and
M is the method identified by the of MR.
D) The applicable privileges of G include grantable SELECT privilege WITH
HIERARCHY OPTION on at least one supertable of the scoped table of every
that is contained in QE.
then let WGO be ‘‘WITH GRANT OPTION’’.
2) Otherwise, let WGO be a zero-length string.
ii) The following is effectively executed as though the current user
identifier were ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT REFERENCES (CV)ON V TO G WGO
d) If, following successful execution of the , the applicable privileges of
G include grantable SELECT privilege on every column of V, then the following
statement> is effectively executed as though the current user identifier were ‘‘_SYSTEM’’
and without further Access Rule checking:
GRANT SELECT ON V TO G WITH GRANT OPTION
e) Following successful execution of the ,
Case:
i) If the applicable privileges of G include REFERENCES privilege on every column of V,
then let WGO be a zero-length string.
ii) If the applicable privileges of G include grantable REFERENCES privilege on every
column of V, then let WGO be ‘‘WITH GRANT OPTION’’.
iii) The following is effectively executed as though the current user
identifier were ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT REFERENCES ON V TO G WITH GRANT OPTION
Access control 585
12.1
5) Following the successful execution of the , for every table T specified by some
involved privilege descriptor and for every updatable view V owned by some grantee G such that
T is some leaf underlying table of the of V:
a) Let VN be the
b) If QE is fully updatable with respect to T, and the applicable privileges of G include PA,
where PA is either INSERT, UPDATE, or DELETE, then the following
is effectively executed as though the current user identifier were ‘‘_SYSTEM’’ and without
further Access Rule checking:
GRANT PA ON VN TO G
c) If QE is fully updatable with respect to T, and the applicable privileges of G include
grantable PA privilege on T, where PA is either INSERT, UPDATE, or DELETE, then
the following is effectively executed as though the current user identifier
were ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT PA ON VN TO G WITH GRANT OPTION
d) For each column CV of V, named CVN, that has a counterpart CT in T, named CTN,if
QE is fully or partially updatable with respect to T, and the applicable privileges of G
include PA(CTN) privilege on T, where PA is INSERT or UPDATE, then the following statement> is effectively executed as though the current user identifier were ‘‘_SYSTEM’’
and without further Access Rule checking:
GRANT PA(CVN)ON VN TO G
e) For each column CV of V, named CVN, that has a counterpart CT in T, named CTN,if QE
is fully or partially updatable with respect to T, and the applicable privileges of G include
grantable PA(CTN) privilege on T, where PA is INSERT or UPDATE, then the following
is effectively executed as though the current user identifier were ‘‘_
SYSTEM’’ and without further Access Rule checking:
GRANT PA(CVN)ON VN TO G WITH GRANT OPTION
6) For every involved grantee G and for every referenceable view V, named VN, owned by G,
if following the successful execution of the , the applicable privileges of G
include grantable UNDER privilege on the direct supertable of V, then the following statement> is effectively executed with a current authorization identifier of ‘‘_SYSTEM’’ and
without further Access Rule checking:
GRANT UNDER ON VN TO G WITH GRANT OPTION
7) For every involved grantee G and for every schema-level SQL-invoked routine R1 owned by G,
if the applicable privileges of G contain all of the privileges necessary to successfully execute
every contained in the of R1 are grantable, then for
every privilege descriptor with EXECUTE, a grantor of ‘‘_SYSTEM’’, object of R1, and
grantee G that is not grantable, the following is effectively executed with a
current authorization identifier of ‘‘_SYSTEM’’ and without further Access Rule checking:
GRANT EXECUTE ON R1 TO G WITH GRANT OPTION
NOTE 268 – The privileges necessary include the EXECUTE privilege on every subject routine of every
contained in the .
8) If two privilege descriptors are identical except that one indicates that the privilege is grantable
and the other indicates that the privilege is not grantable, then both privilege descriptors are
set to indicate that the privilege is grantable.
586 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.1
9) If two privilege descriptors are identical except that one indicates WITH HIERARCHY OPTION
and the other does not, then both privilege descriptors are set to indicate that the privilege has
the WITH HIERARCHY OPTION.
10) Redundant duplicate privilege descriptors are removed from the multiset of all privilege descrip-
tors.
Conformance Rules
None.
Access control 587
12.2
12.2
Function
Define privileges.
Format
::=
GRANT
TO [ { }. ]
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
[ GRANTED BY ]
Syntax Rules
1) Let O be the object identified by the contained in .
2) Let U be the current user identifier and let R be the current role name.
3) Case:
a) If GRANTED BY is not specified, then
Case:
i) If U is not the null value, then let A be U.
ii) Otherwise, let A be R.
b) If GRANTED BY CURRENT_USER is specified, then let A be U.
c) If GRANTED BY CURRENT_ROLE is specified, then let A be R.
4) A set of privilege descriptors is identified. The privilege descriptors identified are those defining,
for each explicitly or implicitly in , that on O held by A with
grant option.
5) If the is not contained in a , then the schema identified
by the explicit or implicit qualifier of the shall include the descriptor of O. If the
is contained in a S, then the schema identified by the
explicit or implicit qualifier of the shall include the descriptor of O or S shall
include a that creates the descriptor of O.
6) If WITH HIERARCHY OPTION is specified, then:
a) shall specify an of SELECT without a and
without a .
b) O shall be a table of a structured type.
588 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.2
Access Rules
1) The applicable privileges shall include a privilege identifying O.
NOTE 269 – ‘‘applicable privileges’’ are defined in Subclause 10.5, ‘‘’’.
General Rules
1) The specify one or more privileges on the object identified by the name>.
2) For every identified privilege descriptor IPD, a privilege descriptor is created for each ,
that specifies grantee , action , object O, and grantor A. Let CPD be the set of
privilege descriptors created.
3) For every privilege descriptor in CPD whose action is INSERT, UPDATE, or REFERENCES
without a column name, privilege descriptors are also created and added to CPD for each
column C in O for which A holds the corresponding privilege with grant option. For each such
column, a privilege descriptor is created that specifies the identical , the identical
, object C, and grantor A.
4) For every privilege descriptor in CPD whose action is SELECT without a column name or
method name, privilege descriptors are also created and added to CPD for each column C in
O for which A holds the corresponding privilege with grant option. For each such column, a
privilege descriptor is created that specifies the identical , the identical ,
object C, and grantor A.
5) For every privilege descriptor in CPD whose action is SELECT without a column name or
method name, if the table T identified by the object of the privilege descriptor is a table of a
structured type TY, then table/method privilege descriptors are also created and added to CPD
for each method M of TY for which A holds the corresponding privilege with grant option. For
each such method, a table/method privilege descriptor is created that specifies the identical
, the identical , object consisting of the pair of table T and method M, and
grantor A.
6) If WITH GRANT OPTION was specified, then each privilege descriptor also indicates that the
privilege is grantable.
7) Let SWH be the set of privilege descriptors in CPD whose action is SELECT WITH HIERARCHY
OPTION, and let ST be the set of subtables of O, then for every grantee G in SWH and for ev-
ery table Tin ST, the following is effectively executed without further Access
Rule checking:
GRANT SELECT ON T TO G GRANTED BY A
8) For every combination of and on O specified in , if there is no
corresponding privilege descriptor in CPD, then a completion condition is raised: warning —
privilege not granted.
9) If ALL PRIVILEGES was specified, then for each grantee G, if there is no privilege descriptor
in CPD specifying grantee G, then a completion condition is raised: warning — privilege not
granted.
10) The set of involved privilege descriptors is defined to be CPD.
Access control 589
12.2
11) The set of involved grantees is defined as the set of specified s.
Conformance Rules
1) Without Feature S024, ‘‘Enhanced structured types’’, a contained
in a shall not identify a method.
2) Without Feature S081, ‘‘Subtables’’, conforming SQL language shall not specify WITH
HIERARCHY OPTION.
590 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.3
12.3
Function
Define a role.
Format
::=
CREATE ROLE
[ WITH ADMIN ]
Syntax Rules
1) The specified shall not be equivalent to any other in the
SQL-environment.
Access Rules
1) The privileges necessary to execute the are implementation-defined.
General Rules
1) A defines a role.
2) Let U be the current user identifier and R be the current role name.
3) Case:
a) If WITH ADMIN is not specified, then
Case:
i) If U is not the null value, then let A be U.
ii) Otherwise, let A be R.
b) If WITH ADMIN CURRENT_USER is specified, then let A be U.
c) If WITH ADMIN CURRENT_ROLE is specified, then let A be R.
4) A role authorization descriptor is created that identifies that the role identified by
has been granted to A WITH ADMIN OPTION, with a grantor of ‘‘_SYSTEM’’.
Conformance Rules
1) Without Feature T331, ‘‘Basic roles’’, conforming SQL language shall contain no tion>.
2) Without Feature T332, ‘‘Extended roles’’, conforming SQL language shall not specify WITH
ADMIN.
Access control 591
12.4
12.4
Function
Define role authorizations.
Format
::=
GRANT [ { }. ]
TO [ { }. ]
[ WITH ADMIN OPTION ]
[ GRANTED BY ]
::=
Syntax Rules
1) No role identified by a specified shall be contained in any role identified by a specified
; that is, no cycles of role grants are allowed.
2) Let U be the current user identifier and R be the current role name.
3) Case:
a) If GRANTED BY is not specified, then
Case:
i) If U is not the null value, then let A be U.
ii) Otherwise, let A be R.
b) If GRANTED BY CURRENT_USER is specified, then let A be U.
c) If GRANTED BY CURRENT_ROLE is specified, then let A be R.
Access Rules
1) Every role identified by shall be contained in the applicable roles for A and the
corresponding role authorization descriptors shall specify WITH ADMIN OPTION.
General Rules
1) For every specified, a set of role authorization descriptors is created that defines the
grant of each role identified by a to the with a grantor of A.
2) If WITH ADMIN OPTION is specified, then each role authorization descriptor also indicates
that the role is grantable with the WITH ADMIN OPTION.
3) If two role authorization descriptors are identical except that one indicates that the role is
grantable WITH ADMIN OPTION and the other indicates that the role is not, then both role
authorization descriptors are set to indicate that the role is grantable with the WITH ADMIN
OPTION.
592 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.4
4) Redundant duplicate role authorization descriptors are removed from the multiset of all role
authorization descriptors.
5) The set of involved privilege descriptors is the union of the sets of privilege descriptors corre-
sponding to the applicable privileges of every specified.
6) The set of involved grantees is the union of the set of s and the set of s
that contain at least one of the s that is possibly specified as a .
Conformance Rules
1) Without Feature T331, ‘‘Basic roles’’, conforming SQL language shall contain no statement>.
2) Without Feature T332, ‘‘Extended roles’’, conforming SQL language shall not specify .
Access control 593
12.5
12.5
Function
Destroy a role.
Format
::= DROP ROLE
Syntax Rules
1) Let R be the role identified by the specified .
Access Rules
1) At least one of the enabled authorization identifiers shall have a role authorization identifier
that authorizes R with the WITH ADMIN OPTION.
General Rules
1) Let A be any identified by a role authorization descriptor as having
been granted to R.
2) The following is effectively executed without further Access Rule
checking:
REVOKE R FROM A
3) The descriptor of R is destroyed.
Conformance Rules
1) Without Feature T331, ‘‘Basic roles’’, conforming SQL language shall contain no statement>.
594 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.6
12.6
Function
Destroy privileges and role authorizations.
Format
::=
|
::=
REVOKE [ ]
FROM [ { }. ]
[ GRANTED BY ]
::=
GRANT OPTION FOR
| HIERARCHY OPTION FOR
::=
REVOKE [ ADMIN OPTION FOR ]
[ { }. ]
FROM [ { }. ]
[ GRANTED BY ]
::=
Syntax Rules
1) Let O be the object identified by the contained in . If O is a table T,
then let S be the set of subtables of O.If T is a table of a structured type, then let TY be that
type.
2) If WITH HIERARCHY OPTION is specified, the shall specify an of
SELECT without a and without a and O shall be
a table of a structured type.
3) Let U be the current user identifier and R be the current role name.
4) Case:
a) If GRANTED BY is not specified, then
Case:
i) If U is not the null value, then let A be U.
ii) Otherwise, let A be R.
b) If GRANTED BY CURRENT_USER is specified, then let A be U.
c) If GRANTED BY CURRENT_ROLE is specified, then let A be R.
Access control 595
12.6
5) SELECT is equivalent to specifying both the SELECT table privilege and SELECT ( column list>) for all columns of
SELECT also specifies SELECT () for all columns inherited from T in
each of the subtables of T, and SELECT () for all methods of TY in each
of the subtables of T.
6) INSERT is equivalent to specifying both the INSERT table privilege and INSERT ( column list>) for all columns of
7) UPDATE is equivalent to specifying both the UPDATE table privilege and UPDATE ( column list>) for all columns of
all columns inherited from T in each of the subtables of T.
8) REFERENCES is equivalent to specifying both the REFERENCES table privilege and
REFERENCES () for all columns of
() for all columns inherited from T in each of the subtables of T.
9) Case:
a) If the is a , then for every
specified, a set of privilege descriptors is identified. A privilege descriptor P is said to be
identified if it belongs to the set of privilege descriptors that defined, for any
explicitly or implicitly in , that on O, or any of the objects in S, granted
by A to .
NOTE 270 – Column privilege descriptors become identified when explicitly or implic-
itly contains a . Table/method descriptors become identified when
explicitly or implicitly contains a .
b) If the is a , then for every specified,
a set of role authorization descriptors is identified. A role authorization descriptor is said to
be identified if it defines the grant of any of the specified s to with
grantor A.
10) A privilege descriptor D is said to be directly dependent on another privilege descriptor P if
either:
a) All of the following conditions hold:
i) P indicates that the privilege that it represents is grantable.
ii) The grantee of P is the same as the grantor of D or the grantee of P is PUBLIC, or, if
the grantor of D is a , the grantee of P belongs to the set of applicable roles
of the grantor of D.
iii) Case:
1) P and D are both column privilege descriptors. The action and the identified column
of P are the same as the action and identified column of D, respectively.
2) P and D are both table privilege descriptors. The action and the identified table of P
are the same as the action and the identified table of D, respectively.
3) P and D are both execute privilege descriptors. The action and the identified SQL-
invoked routine of P are the same as the action and the identified SQL-invoked
routine of D, respectively.
596 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.6
4) P and D are both usage privilege descriptors. The action and the identified domain,
character set, collation, translation, or user-defined type of P are the same as the
action and the identified domain, character set, collation, translation, or user-defined
type of D, respectively.
5) P and D are both under privilege descriptors. The action and the identified user-
defined type or table of P are the same as the action and the identified user-defined
type or table of D, respectively.
6) P and D are both table/method privilege descriptors. The action and the identified
method and table of P are the same as the action and the identified method and
table of D, respectively.
b) All of the following conditions hold:
i) The privilege descriptor for D indicates that its grantor is the special grantor value
‘‘_SYSTEM’’.
ii) The action of P is the same as the action of D.
iii) The grantee of P is the owner of the table, collation, or translation identified by D or the
grantee of P is PUBLIC.
iv) One of the following conditions hold:
1) P and D are both table privilege descriptors, the privilege descriptor for D identi-
fies the
underlying table of the of V.
2) P and D are both column privilege descriptors, the privilege descriptor D identifies a
CVN explicitly or implicitly contained in the of a
V, and one of the following is true:
A) V is an updatable view. For every column CV identified by a
CVN, there is a corresponding column in the underlying table of the expression> TN. Let CTN be the of the column of the expression> from which CV is derived. The action for P is UPDATE or INSERT
and the identified column of P is TN.CTN.
B) For every table T identified by a
column of CV, the action for P is REFERENCES and either the identified column
of P is CT or the identified table of P is T.
C) For every table T identified by a
column of CV, the action for P is SELECT and either the identified column of P
is CT or the identified table of P is T.
3) The privilege descriptor D identifies the of a
CO and the identified character set name of P is included in the collation descriptor
for CO, or the identified translation name of P is included in the collation descriptor
for CO.
Access control 597
12.6
4) The privilege descriptor D identifies the of a inition> TD and the identified character set name of P is contained in the character set specification> or the immediately
contained in TD.
c) All of the following conditions hold:
i) The privilege descriptor for D indicates that its grantor is the special grantor value
‘‘_SYSTEM’’.
ii) The grantee of P is the owner of the domain identified by D or the grantee of P is
PUBLIC.
iii) The privilege descriptor D identifies the of a DO
and either the column privilege descriptor P has an action of REFERENCES and identi-
fies a column referenced in the included in the domain descriptor for
DO, or the privilege descriptor P has an action of USAGE and identifies a domain, colla-
tion, character set, or translation whose , , set name> or , respectively, is contained in the of
the domain descriptor for DO.
11) The privilege dependency graph is a directed graph such that all of the following are true:
a) Each node represents a privilege descriptor.
b) Each arc from node P1 to node P2 represents the fact that P2 directly depends on P1.
An independent node is a node that has no incoming arcs.
12) A privilege descriptor P is said to be modified if all of the following are true:
a) P indicates that the privilege that it represents is grantable.
b) P directly depends on an identified privilege descriptor or a modified privilege descriptor.
c) Case:
i) If P is neither a SELECT nor a REFERENCES column privilege descriptor that identi-
fies a CVN explicitly or implicitly contained in the
of a V, then let XO and XA respectively be the identifier of the object
identified by a privilege descriptor X and the action of X. Within the set of privilege
descriptors upon which P directly depends, there exist some XO and XA for which the
set of identified privilege descriptors unioned with the set of modified privilege descrip-
tors include all privilege descriptors specifying the grant of XA on XO WITH GRANT
OPTION.
ii) If P is a column privilege descriptor that identifies a column CV named by a name> CVN explicitly or implicitly contained in the of a definition> V with an action PA of REFERENCES or SELECT, then let SP be the set
of privileges upon which P directly depends. For every table T identified by a reference> contained in the of V, let RT be the
There exists a column CT whose is CRT, such that all of the following
are true:
1) CT is a column of T and an underlying column of CV.
598 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.6
2) Every privilege descriptor PD that is the descriptor of some member of SP that
specifies the action PA on CRT WITH GRANT OPTION is either an identified
privilege descriptor for CRT or a modified privilege descriptor for CRT.
d) At least one of the following is true:
i) GRANT OPTION FOR is specified and the grantor of P is the special grantor value
‘‘_SYSTEM’’.
ii) There exists a path to P from an independent node that includes no identified or modi-
fied privilege descriptors. P is said to be a marked modified privilege descriptor.
iii) P directly depends on a marked modified privilege descriptor, and the grantor of P is the
special grantor value ‘‘_SYSTEM’’. P is said to be a marked modified privilege descriptor.
13) A role authorization descriptor D is said to be directly dependent on another role authorization
descriptor RD if all of the following conditions hold:
a) RD indicates that the role that it represents is grantable.
b) The role name of D is the same as the role name of RD.
c) The grantee of RD is the same as the grantor of D or the grantee of RD is PUBLIC, or, if
the grantor of D is a , the grantee of RD belongs to the set of applicable roles of
the grantor of D.
14) The role dependency graph is a directed graph such that all of the following are true:
a) Each node represents a role authorization descriptor.
b) Each arc from node R1 to node R2 represents the fact that R2 directly depends on R1.
An independent node is one that has no incoming arcs.
15) A role authorization descriptor RD is said to be abandoned if it is not an independent node, and
it is not itself an identified role authorization descriptor, and there exists no path to RD from
any independent node other than paths that include an identified role authorization descriptor.
16) An arc from a node P to a node D of the privilege dependency graph is said to be unsupported if
all of the following are true:
a) The grantor of D and the grantee of P are both s.
b) The destruction of all abandoned role authorization descriptors and, if ADMIN OPTION
FOR is not specified, all identified role authorization descriptors would result in the grantor
of D no longer having in its applicable roles the grantee of P.
17) A privilege descriptor P is abandoned if:
Case:
a) It is not an independent node, and P is not itself an identified or a modified privilege de-
scriptor, and there exists no path to P from any independent node other than paths that
include an identified privilege descriptor or a modified privilege descriptor or an unsup-
ported arc and, if specifies WITH HIERARCHY OPTION, then P has
the WITH HIERARCHY OPTION.
Access control 599
12.6
b) All of the following conditions hold:
i) P is a column privilege descriptor that identifies a CVN explicitly or
implicitly contained in the of a V, with an action
PA of REFERENCES or SELECT.
ii) Letting SP be the set of privileges upon which P directly depends, at least one of the
following is true:
1) There exists some table name RT such that all of the following are true:
A) RT is the name of the table identified by some
of V.
B) For every column privilege descriptor CPD that is the descriptor of some member
of SP that specifies the action PA on RT, CPD is either an identified privilege
descriptor for RT or an abandoned privilege descriptor for RT.
2) There exists some column name CRT such that all of the following are true:
A) CRT is the name of some column of the table of some
in the of V.
B) For every column privilege descriptor CPD that is the descriptor of some member
of SP that specifies the action PA on CRT, CPD is either an identified privilege
descriptor for CRT or an abandoned privilege descriptor for CRT.
18) The revoke destruction action is defined as
Case:
a) If the is a , then
Case:
i) If the specifies the WITH HIERARCHY OPTION, then the re-
moval of the WITH HIERARCHY OPTION from all identified and abandoned privilege
descriptors.
ii) Otherwise, the destruction of all abandoned privilege descriptors and, if GRANT
OPTION FOR is not specified, all identified privilege descriptors.
b) If the is a , then the destruction of all aban-
doned role authorization descriptors, all abandoned privilege descriptors and, if GRANT
OPTION FOR is not specified, all identified role authorization descriptors.
19) Let S1 be the name of any schema and A1 be the that owns the
schema identified by S1.
20) Let V be any view descriptor included in S1. Let QE be the of V. V is said
to be abandoned if the revoke destruction action would result in A1 no longer having in its
applicable privileges all of the following:
a) SELECT privileges on every table whose name is contained in QE.
b) SELECT privileges on every column whose name is contained in QE.
600 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.6
c) USAGE privilege on every domain, every collation, every character set, and every transla-
tion whose names are contained in QE.
d) EXECUTE privilege on every SQL-invoked routine that is the subject routine of any tine invocation>, , , or
that is contained in QE.
e) The table/method privileges on every table T1 and every method M such that there is
a MR contained in QE such that T1 is in the scope of the expression primary> of MR and M is subject routine of MR.
f) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in QE.
g) If V is the descriptor of a referenceable table, then USAGE privilege on the structured type
associated with the view described by V.
h) UNDER privilege on every direct supertable of the view described by V.
21) Let T be any table descriptor included in S1. T is said to be abandoned if the revoke destruction
action would result in A1 no longer having all of the following:
a) If T is the descriptor of a referenceable table, then USAGE privilege on the structured type
associated with the table described by T.
b) UNDER privilege on every direct supertable of the table described by T.
22) Let TC be any table constraint descriptor included in S1. TC is said to be abandoned if the
revoke destruction action would result in A1 no longer having in its applicable privileges all of
the following:
a) REFERENCES privilege on every column identified by a contained in
the of TC.
b) USAGE privilege on every domain, every collation, every character set, and every transla-
tion whose names are contained in any of TC.
c) EXECUTE privilege on every SQL-invoked routine that is the subject routine of any tine invocation>, , , or
that is contained in any of TC.
d) The table/method privilege on every table T1 and every method M such that there is a
MR contained in any of TC such that T1 is in the
scope of the of MR and M is the subject routine of MR.
e) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any of TC.
23) Let AX be any assertion descriptor included in S1. AX is said to be abandoned if the revoke
destruction action would result in A1 no longer having all in its applicable privileges of the
following:
a) REFERENCES privilege on every column identified by a contained in
the of AX.
b) USAGE privilege on every domain, every collation, every character set, and every transla-
tion whose names are contained in any of AX.
Access control 601
12.6
c) EXECUTE privilege on every SQL-invoked routine that is the subject routine of any tine invocation>, , , or
that is contained in any of AX.
d) The table/method privilege on every table T1 and every method M such that there is a
MR contained in AX such that T1 is in the scope of the sion primary> of MR and M is the subject routine of MR.
e) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any of AX.
24) Let TR be any trigger descriptor included in S1. TR is said to be abandoned if the revoke
destruction action would result in A1 no longer having in its applicable privileges all of the
following:
a) TRIGGER privilege on the subject table of TR.
b) SELECT privilege on every column identified by a contained in any
of TR.
c) USAGE privilege on every domain, collation, character set, and translation whose name is
contained in any of TR.
d) The table/method privilege on every table T1 and every method M such that there is a
MR contained in any of TR such that T1 is in the
scope of the of MR and M is the subject routine of MR.
e) EXECUTE privilege on the SQL-invoked routine that is the subject routine of any invocation>, , , or that
is contained in any of TR.
f) EXECUTE privilege on the SQL-invoked routine that is the subject routine of any invocation>, , , or that
is contained in the of TR.
g) SELECT privilege on every
contained in a or an contained in the SQL statement> of TR.
h) SELECT privilege on every
i) SELECT privilege on every
contained in a or an searched> contained in the of TR.
j) SELECT privilege on every
k) INSERT privileges on every column
Case:
i) Named in the of an contained in the SQL statement> of TR.
602 Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
12.6
ii) Of the table identified by the
of TR.
l) UPDATE privileges on every column whose name is contained in an con-
tained in either an or an
contained in the of TR.
m) DELETE privileges on every table whose name is contained in a
either a or a contained in the
of TR.
n) USAGE privilege on every domain, collation, character set, and translation whose name is
contained in the of TR.
o) The table/method privilege on every table T1 and every method M such that there is a
MR contained in any of TR such that T1 is
in the scope of the of MR and M is the subject routine of MR.
p) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any of TR.
q) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any simply
contained in a or an contained in the SQL statement> of TR.
r) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any
s) SELECT privilege WITH HIERARCHY OPTION on at least one supertable of the scoped
table of any that is contained in any ...
INTERNATIONAL ISO/IEC
STANDARD 9075-2
First edition
1999-12-01
Information technology — Database
languages — SQL —
Part 2:
Foundation (SQL/Foundation)
Technologies de l’information — Langages de base de données — SQL —
Partie 2: Fondations (SQL/Fondations)
Reference number
©
ISO/IEC 1999
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but shall not
be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In downloading this
file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat accepts no liability in this
area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation parameters
were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In the unlikely event
that a problem relating to it is found, please inform the Central Secretariat at the address given below.
© ISO/IEC 1999
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means, electronic
or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 � CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 734 10 79
E-mail copyright@iso.ch
Web www.iso.ch
Printed in Switzerland
i-2 © ISO/IEC 1999 – All rights reserved
© ISO/IEC 1999 – All rights reserved i-3
Contents Page
Foreword . xv
Introduction . . . . xvii
1 Scope . 1
2 Normative references. 3
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.1.1 Definitions taken from ISO/IEC 10646 . . . 5
3.1.2 Definitions taken from Unicode . 5
3.1.3 Definitions taken from ISO 8601 . 6
3.1.4 Definitions taken from Part 1 . 6
3.1.5 Definitions provided in Part 2 . 6
3.2 Notation. 10
3.3 Conventions . 10
3.3.1 Use of terms. 10
3.3.1.1 Syntactic containment . . . 10
4 Concepts. 11
4.1 Data types . 11
4.2 Character strings . . 13
4.2.1 Character strings and collating sequences . 13
4.2.2 Operations involving character strings . . 14
4.2.2.1 Operators that operate on character strings and return character strings. 14
4.2.2.2 Other operators involving character strings . . . 15
4.2.2.3 Operations involving large object character strings . 15
4.2.3 Rules determining collating sequence usage . 16
4.2.4 Named character sets . 18
4.3 Binary strings . . . . 20
4.3.1 Binary string comparison. 21
4.3.2 Operations involving binary strings . 21
4.3.2.1 Operators that operate on binary strings and return binary strings . 21
4.3.2.2 Other operators involving binary strings . 21
4.4 Bit strings . 21
4.4.1 Bit string comparison and assignment . . . 22
ii Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
4.4.2 Operations involving bit strings. 22
4.4.2.1 Operators that operate on bit strings and return bit strings . 22
4.4.2.2 Other operators involving bit strings . 22
4.5 Numbers . 22
4.5.1 Characteristics of numbers . . . 22
4.5.2 Operations involving numbers . 23
4.6 Boolean types. 24
4.6.1 Comparison and assignment of booleans . 24
4.6.2 Operations involving booleans . 24
4.6.2.1 Operations on booleans that return booleans . . 24
4.6.2.2 Other operators involving booleans . 24
4.7 Datetimes and intervals. 24
4.7.1 Datetimes. 25
4.7.2 Intervals . 27
4.7.3 Operations involving datetimes and intervals . 29
4.8 User-defined types . 30
4.8.1 Observers and mutators. 32
4.8.2 Constructors. 32
4.8.3 Subtypes and supertypes . 32
4.8.4 User-defined type comparison and assignment . . . . 33
4.8.5 Transforms for user-defined types . 34
4.9 Row types. 35
4.10 Reference types . . . 35
4.10.1 Operations involving references . 36
4.11 Collection types . . . 36
4.11.1 Arrays . . 36
4.11.2 Collection comparison . 37
4.11.3 Operations involving collections. 37
4.11.3.1 Operators that operate on array values and return array elements . . 37
4.11.3.2 Operators that operate on array values and return array values . . . . 37
4.12 Type conversions and mixing of data types . 37
4.13 Data conversions . . 39
4.14 Domains. 40
4.15 Columns, fields, and attributes . 40
4.16 Tables . . . 42
4.16.1 Types of tables . . . . 44
4.16.2 Referenceable tables, subtables, and supertables . . . 45
4.16.3 Operations involving tables . . . 46
4.17 Integrity constraints . 48
4.17.1 Checking of constraints . 48
4.17.2 Table constraints . . 49
4.17.3 Domain constraints . 50
4.17.4 Assertions . 50
4.18 Functional dependencies . 50
4.18.1 General rules and definitions . . 51
Contents iii
4.18.2 Known functional dependencies in a base table . . . . 52
4.18.3 Known functional dependencies in . 53
4.18.4 Known functional dependencies in a . 53
4.18.5 Known functional dependencies in a
4.18.6 Known functional dependencies in the result of a . 55
4.18.7 Known functional dependencies in the result of a . 55
4.18.8 Known functional dependencies in the result of a . . . . 56
4.18.9 Known functional dependencies in the result of a . 56
4.18.10 Known functional dependencies in a . . . 56
4.18.11 Known functional dependencies in a . 57
4.19 Candidate keys . . . 58
4.20 SQL-schemas . 59
4.21 SQL-client modules . 59
4.22 Externally-invoked procedures. 60
4.23 SQL-invoked routines . 61
4.24 Built-in functions . . 67
4.25 SQL-paths . 67
4.26 Host parameters . . 68
4.26.1 Status parameters . 68
4.26.2 Data parameters . . 69
4.26.3 Indicator parameters . 69
4.26.4 Locators . 69
4.27 Diagnostics area . . . 70
4.28 Standard programming languages . 70
4.29 Cursors . 71
4.30 SQL-statements . . . 73
4.30.1 Classes of SQL-statements . . . . 73
4.30.2 SQL-statements classified by function . . . 73
4.30.3 SQL-statements and transaction states . . 76
4.30.4 SQL-statement atomicity . 77
4.31 Basic security model . 77
4.31.1 Authorization identifiers . 77
4.31.1.1 SQL-session authorization identifiers. 78
4.31.1.2 SQL-client module authorization identifiers . . . 79
4.31.1.3 SQL-schema authorization identifiers . 79
4.31.2 Privileges . 79
4.31.3 Roles . . . 81
4.31.4 Security model definitions . . . . 81
4.32 SQL-transactions . . 82
4.33 SQL-connections . . . 86
4.34 SQL-sessions . 87
4.34.1 Execution contexts . 89
4.35 Triggers . 90
4.35.1 Triggered actions . . 90
4.35.2 Execution of triggers . 91
iv Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
4.36 Client-server operation. 92
5 Lexical elements . 93
5.1 . . . . 93
5.2 and . 96
5.3 . 105
5.4 Names and identifiers . 113
6 Scalar expressions . 121
6.1 . 121
6.2 <field definition> . . 130
6.3 and . . . 132
6.4 . 136
6.5 . . 138
6.6 . 141
6.7 . . . 143
6.8 <field reference> . . 144
6.9 . 145
6.10 . 146
6.11 . 147
6.12 . . . 149
6.13 . 151
6.14 . 152
6.15 . 153
6.16 . . . 155
6.17 . . . . 159
6.18 . 164
6.19 . . . . 175
6.20 . 177
6.21 . 178
6.22 . 181
6.23 . 197
6.24 . 200
6.25 . 201
6.26 . . . 202
6.27 . . . . 204
6.28 . . 209
6.29 . . . 212
6.30 . . . 216
6.31 . 219
6.32 . . . . 221
7 Query expressions . 223
7.1 . 223
7.2 . 226
7.3
Contents v
7.4
7.5 . . . . 230
7.6
7.7 . . . . 238
7.8 . . . 244
7.9 . 245
7.10 . . . 256
7.11 . 258
7.12 . 265
7.13 . 279
7.14 . 283
8 Predicates . 285
8.1 . 285
8.2 . 287
8.3 . 295
8.4 . . . . 296
8.5 . . . 298
8.6 . 304
8.7 . . . 309
8.8 . 310
8.9 . 312
8.10 . 313
8.11 . 314
8.12 . 316
8.13 . 318
8.14 . . 320
8.15 . 322
9 Data assignment rules and routine determination . 323
9.1 Retrieval assignment . 323
9.2 Store assignment . . 328
9.3 Data types of results of aggregations . . . . 333
9.4 Subject routine determination . 336
9.5 Type precedence list determination . 337
9.6 Host parameter mode determination . . . . 342
9.7 Type name determination . . . . 344
10 Additional common elements . 347
10.1 . 347
10.2 . 351
10.3 . 353
10.4 . 354
10.5 . 374
10.6 . . 379
10.7 . . 381
vi Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
10.8 . . . 384
10.9 and . 385
10.10 Execution of BEFORE triggers . 387
10.11 Execution of AFTER triggers . . 388
10.12 Execution of triggers . 389
10.13 Execution of array-returning functions . . 390
10.14 Data type identity . 393
10.15 Determination of a from-sql function . . . . 395
10.16 Determination of a from-sql function for an overriding method . 396
10.17 Determination of a to-sql function . 397
10.18 Determination of a to-sql function for an overriding method . . 398
11 Schema definition and manipulation. 399
11.1 . 399
11.2 . . . . 402
11.3
11.4 . 412
11.5 . . . 418
11.6
11.7 . 424
11.8 . 426
11.9 . . 440
11.10 . 442
11.11 . 444
11.12 . 445
11.13 . . . 446
11.14 . 447
11.15 . . . 448
11.16 . . . 449
11.17 . 451
11.18 . 453
11.19 . 454
11.20 . 456
11.21 . . 459
11.22 . 469
11.23 . 471
11.24 . . . . 474
11.25 . . . 475
11.26 . 476
11.27 . 477
11.28 . . . . 478
11.29 . . . . 479
11.30 . . . . 481
11.31 . 483
11.32 . 485
11.33 . . . 487
Contents vii
11.34 . 489
11.35 . 491
11.36 . 493
11.37 . . . 495
11.38 . 497
11.39 . 501
11.40 . 502
11.41 . 517
11.42 . 520
11.43 . . . . 521
11.44 . . . . 523
11.45 . 525
11.46 . . . 531
11.47 . . . 535
11.48 . . . 537
11.49 . 541
11.50 . . . . 562
11.51 . . . . 565
11.52 . 567
11.53 . 569
11.54 . 571
11.55 . 574
11.56 . 576
11.57 . . 579
12 Access control . 583
12.1 . 583
12.2 . . . 588
12.3 . . . 591
12.4 . 592
12.5 . 594
12.6 . 595
13 SQL-client modules . 611
13.1 . 611
13.2 . 615
13.3 . 616
13.4 Calls to an . 619
13.5 . . 633
13.6 Data type correspondences . . . . 641
14 Data manipulation . 651
14.1 . . . 651
14.2 . 657
14.3 . 659
14.4 . 663
viii Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
14.5 . 665
14.6 . 667
14.7 . 670
14.8 . 673
14.9 . 677
14.10 . 684
14.11 . 690
14.12 . 692
14.13 . 693
14.14 Effect of deleting rows from base tables . . 694
14.15 Effect of deleting some rows from a derived table . . 696
14.16 Effect of deleting some rows from a viewed table . . . 698
14.17 Effect of inserting tables into base tables. 699
14.18 Effect of inserting a table into a derived table . 701
14.19 Effect of inserting a table into a viewed table . 703
14.20 Effect of replacing rows in base tables . . . 704
14.21 Effect of replacing some rows in a derived table . . . 706
14.22 Effect of replacing some rows in a viewed table . . . . 708
15 Control statements . 711
15.1 . . 711
15.2 . 712
16 Transaction management . 715
16.1 . 715
16.2 . . . 717
16.3 . 719
16.4 . 721
16.5 . 722
16.6 . 723
16.7 . 725
17 Connection management . 727
17.1 . 727
17.2 . . . 730
17.3 . 732
18 Session management . 735
18.1 . . 735
18.2 . . 736
18.3 . 737
18.4 . 738
19 Diagnostics management. 739
19.1 . . . 739
Contents ix
20 Information Schema. 751
20.1 Introduction to Information Schema and Definition Schema . . 751
20.2 INFORMATION_SCHEMA Schema. 752
20.3 INFORMATION_SCHEMA_CATALOG_NAME base table . . . . 753
20.4 CARDINAL_NUMBER domain . 754
20.5 CHARACTER_DATA domain . . 754
20.6 SQL_IDENTIFIER domain . . . 755
20.7 TIME_STAMP domain . 755
20.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view . 756
20.9 APPLICABLE_ROLES view . . . 757
20.10 ASSERTIONS view . 758
20.11 ATTRIBUTES view . 759
20.12 CHARACTER_SETS view . . . . 761
20.13 CHECK_CONSTRAINTS view. 762
20.14 COLLATIONS view . 763
20.15 COLUMN_DOMAIN_USAGE view . 764
20.16 COLUMN_PRIVILEGES view . 765
20.17 COLUMN_UDT_USAGE view . 766
20.18 COLUMNS view . . 767
20.19 CONSTRAINT_COLUMN_USAGE view . 769
20.20 CONSTRAINT_TABLE_USAGE view . . . 770
20.21 DATA_TYPE_PRIVILEGES view . 771
20.22 DIRECT_SUPERTABLES view . 772
20.23 DIRECT_SUPERTYPES view . 773
20.24 DOMAIN_CONSTRAINTS view. 774
20.25 DOMAIN_UDT_USAGE view . 775
20.26 DOMAINS view . . . 776
20.27 ELEMENT_TYPES view . 777
20.28 ENABLED_ROLES view . 778
20.29 FIELDS view . 779
20.30 KEY_COLUMN_USAGE view . 780
20.31 METHOD_SPECIFICATION_PARAMETERS view . 781
20.32 METHOD_SPECIFICATIONS view . 783
20.33 PARAMETERS view . 785
20.34 REFERENCED_TYPES view . . 787
20.35 REFERENTIAL_CONSTRAINTS view . . 788
20.36 ROLE_COLUMN_GRANTS view . 789
20.37 ROLE_ROUTINE_GRANTS view . 790
20.38 ROLE_TABLE_GRANTS view . 791
20.39 ROLE_TABLE_METHOD_GRANTS view . 792
20.40 ROLE_USAGE_GRANTS view. 793
20.41 ROLE_UDT_GRANTS view . . . 794
20.42 ROUTINE_COLUMN_USAGE view. 795
20.43 ROUTINE_PRIVILEGES view. 796
20.44 ROUTINE_TABLE_USAGE view. 797
x Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
20.45 ROUTINES view . . 798
20.46 SCHEMATA view . . 800
20.47 SQL_FEATURES view . 801
20.48 SQL_IMPLEMENTATION_INFO view . . 802
20.49 SQL_LANGUAGES view . 803
20.50 SQL_PACKAGES view. 804
20.51 SQL_SIZING view . 805
20.52 SQL_SIZING_PROFILES view . 806
20.53 TABLE_CONSTRAINTS view . 807
20.54 TABLE_METHOD_PRIVILEGES view . . 808
20.55 TABLE_PRIVILEGES view . . . 809
20.56 TABLES view. 810
20.57 TRANSFORMS view . 811
20.58 TRANSLATIONS view . 812
20.59 TRIGGERED_UPDATE_COLUMNS view . 813
20.60 TRIGGER_COLUMN_USAGE view. 814
20.61 TRIGGER_TABLE_USAGE view . 815
20.62 TRIGGERS view . . 816
20.63 USAGE_PRIVILEGES view . . . 817
20.64 UDT_PRIVILEGES view . 818
20.65 USER_DEFINED_TYPES view . 819
20.66 VIEW_COLUMN_USAGE view . 821
20.67 VIEW_TABLE_USAGE view . . 822
20.68 VIEWS view. 823
20.69 Short name views . . 824
20.70 Definition of SQL built-in functions . 835
21 Definition Schema . 847
21.1 Introduction to the Definition Schema . . . 847
21.2 DEFINITION_SCHEMA Schema. 848
21.3 EQUAL_KEY_DEGREES assertion . 849
21.4 KEY_DEGREE_GREATER_THAN_OR_EQUAL_TO_1 assertion. 850
21.5 UNIQUE_CONSTRAINT_NAME assertion . 851
21.6 ASSERTIONS base table . 852
21.7 ATTRIBUTES base table . 853
21.8 CHARACTER_SETS base table . 855
21.9 CHECK_COLUMN_USAGE base table . . 857
21.10 CHECK_TABLE_USAGE base table . . . . 858
21.11 CHECK_CONSTRAINTS base table . . . . 859
21.12 COLLATIONS base table . 860
21.13 COLUMN_PRIVILEGES base table. 862
21.14 COLUMNS base table . 864
21.15 DATA_TYPE_DESCRIPTOR base table . . 867
21.16 DIRECT_SUPERTABLES base table . . . . 874
21.17 DIRECT_SUPERTYPES base table . 876
21.18 DOMAIN_CONSTRAINTS base table . . . 878
Contents xi
21.19 DOMAINS base table. 880
21.20 ELEMENT_TYPES base table . 881
21.21 FIELDS base table. 882
21.22 KEY_COLUMN_USAGE base table. 884
21.23 METHOD_SPECIFICATION_PARAMETERS base table . 886
21.24 METHOD_SPECIFICATIONS base table. 888
21.25 PARAMETERS base table . . . . 891
21.26 REFERENCED_TYPES base table . 893
21.27 REFERENTIAL_CONSTRAINTS base table . 894
21.28 ROLE_AUTHORIZATION_DESCRIPTORS base table . 896
21.29 ROLES base table . 898
21.30 ROUTINE_COLUMN_USAGE base table . 899
21.31 ROUTINE_PRIVILEGES base table . . . . 901
21.32 ROUTINE_TABLE_USAGE base table . . 903
21.33 ROUTINES base table . 905
21.34 SCHEMATA base table . 910
21.35 SQL_FEATURES base table . . 911
21.36 SQL_IMPLEMENTATION_INFO base table . 913
21.37 SQL_LANGUAGES base table. 914
21.38 SQL_SIZING base table. 918
21.39 SQL_SIZING_PROFILES base table . . . . 919
21.40 TABLE_CONSTRAINTS base table . 920
21.41 TABLE_METHOD_PRIVILEGES base table . 922
21.42 TABLE_PRIVILEGES base table . 924
21.43 TABLES base table . 926
21.44 TRANSFORMS base table . . . . 928
21.45 TRANSLATIONS base table . . 929
21.46 TRIGGERED_UPDATE_COLUMNS base table . . . . 931
21.47 TRIGGER_COLUMN_USAGE base table . 932
21.48 TRIGGER_TABLE_USAGE base table . . . 934
21.49 TRIGGERS base table . 936
21.50 USAGE_PRIVILEGES base table . 938
21.51 USER_DEFINED_TYPE_PRIVILEGES base table . 940
21.52 USER_DEFINED_TYPES base table . . . . 942
21.53 USERS base table . 945
21.54 VIEW_COLUMN_USAGE base table . . . . 946
21.55 VIEW_TABLE_USAGE base table . 947
21.56 VIEWS base table . 948
22 Status codes. 951
22.1 SQLSTATE. 951
22.2 Remote Database Access SQLSTATE Subclasses . . . 958
22.3 SQL Multimedia and Application Packages SQLSTATE Subclasses . 958
xii Foundation (SQL/Foundation)
©ISO/IEC ISO/IEC 9075-2:1999 (E)
23 Conformance . 961
23.1 General conformance requirements . 961
23.2 Claims of conformance . 962
Annex A SQL Conformance Summary . 965
Annex B Implementation-defined elements . 1017
Annex C Implementation-dependent elements . 1027
Annex D Deprecated features . 1033
Annex E Incompatibilities with ISO/IEC 9075:1992 and ISO/IEC 9075-4:1996 . 1035
Annex F SQL feature and package taxonomy . 1041
Index . 1065
Contents xiii
TABLES
Tables Page
1 Collating coercibility rules for monadic operators . . 17
2 Collating coercibility rules for dyadic operators . . . . 17
3 Collating sequence usage for comparisons . 18
4 Fields in datetime values . 25
5 Datetime data type conversions . 27
6 Fields in year-month INTERVAL values . 27
7 Fields in day-time INTERVAL values . . . 28
8 Valid values for fields in INTERVAL values. 28
9 Valid operators involving datetimes and intervals . . 29
10 SQL-transaction isolation levels and the three phenomena . . . 84
11 Valid values for datetime fields . 127
12 Valid absolute values for interval fields . . 128
13 Truth table for the AND boolean operator . 218
14 Truth table for the OR boolean operator . 218
15 Truth table for the IS boolean operator . . 218
16 ...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.
Loading comments...