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
Buy Standard
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
---------------------- Page: 1 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 2 ----------------------
©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
---------------------- Page: 3 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 4 ----------------------
©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
---------------------- Page: 5 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 6 ----------------------
©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
---------------------- Page: 7 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 8 ----------------------
©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
---------------------- Page: 9 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 10 ----------------------
©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
---------------------- Page: 11 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 12 ----------------------
©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
---------------------- Page: 13 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 14 ----------------------
©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
---------------------- Page: 15 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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
...
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 9075-2:1999(E)
©
ISO/IEC 1999
---------------------- Page: 1 ----------------------
ISO/IEC 9075-2:1999(E)
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
---------------------- Page: 2 ----------------------
ISO/IEC 9075-2:1999(E)
© ISO/IEC 1999 – All rights reserved i-3
---------------------- Page: 3 ----------------------
ISO/IEC 9075-2:1999 (E)
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)
---------------------- Page: 4 ----------------------
©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
---------------------- Page: 5 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 6 ----------------------
©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
---------------------- Page: 7 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 8 ----------------------
©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
---------------------- Page: 9 ----------------------
ISO/IEC 9075-2:1999 (E) ©ISO/IEC
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)
---------------------- Page: 10 ----------------------
©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 . ...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.