ISO/IEC 7816-7:1999

Identification cards — Integrated circuit(s) cards with contacts — Part 7: Interindustry commands for Structured Card Query Language (SCQL)

This part of ISO/IEC 7816 specifies the concept of a SCQL database (SCQL = Structured Card Query Language based on SQL, see ISO 9075) and the related interindustry enhanced commands.

Cartes d'identification — Cartes à circuit(s) intégré(s) à contacts — Partie 7: Commandes intersectorielles pour langage d'interrogation de carte structurée (SCQL)

General Information

• Status: Published
• Publication Date: 10-Mar-1999
• ICS: 35.240.15 - Identification cards. Chip cards. Biometrics
• Technical Committee: ISO/IEC JTC 1/SC 17 - Cards and security devices for personal identification
• Drafting Committee: ISO/IEC JTC 1/SC 17/WG 4 - Generic interfaces and protocols for security devices
• Current Stage: 9093 - International Standard confirmed
• Completion Date: 05-Dec-2018

Relations

• Consolidated By: ISO 3218:2014 - Essential oils — Principles of nomenclature
• Effective Date: 06-Jun-2022

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 7816-7
First edition
1999-03-01
Identification cards — Integrated circuit(s)
cards with contacts —
Part 7:
Interindustry commands for Structured Card
Query Language (SCQL)
Cartes d’identification — Cartes à circuit(s) intégré(s) à contacts —
Partie 7: Commandes intersectorielles pour langage d'interrogation de carte
structurée (SCQL)
Reference number
B C
ISO/IEC 7816-7:1999(E)
---------------------- Page: 1 ----------------------
ISO/IEC 7816-7:1999(E)
Contents

1 Scope ........................................................................................................................................................................1

2 Normative references ..............................................................................................................................................1

3 Terms and definitions .............................................................................................................................................1

4 Symbols (and abbreviated terms) ..........................................................................................................................2

5 SCQL database concept .........................................................................................................................................2

5.1 SCQL database .....................................................................................................................................................2

5.2 SCQL tables...........................................................................................................................................................3

5.3 SCQL views ...........................................................................................................................................................4

5.4 SCQL system tables and dictionaries ................................................................................................................5

5.5 SCQL user profiles ...............................................................................................................................................7

6 SCQL related commands ........................................................................................................................................7

6.1 General aspects ....................................................................................................................................................7

6.2 Grouping and encoding of commands...............................................................................................................8

6.3 Notation and special codings..............................................................................................................................9

6.4 Status bytes.........................................................................................................................................................10

6.5 Coding of identifiers...........................................................................................................................................11

6.6 Security attributes of tables, views and users.................................................................................................12

6.7 Linking user ids to INSERT and UPDATE operations.....................................................................................12

7 Database operations..............................................................................................................................................12

7.1 CREATE TABLE ..................................................................................................................................................12

7.2 CREATE VIEW .....................................................................................................................................................13

7.3 CREATE DICTIONARY........................................................................................................................................15

7.4 DROP TABLE.......................................................................................................................................................16

7.5 DROP VIEW .........................................................................................................................................................17

© 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 the publisher.

ISO/IEC Copyright Office • Case postale 56 • CH-1211 Genève 20 • Switzerland
Printed in Switzerland
---------------------- Page: 2 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)

7.6 GRANT ................................................................................................................................................................. 18

7.7 REVOKE............................................................................................................................................................... 19

7.8 DECLARE CURSOR............................................................................................................................................ 20

7.9 OPEN.................................................................................................................................................................... 22

7.10 NEXT .................................................................................................................................................................. 23

7.11 FETCH................................................................................................................................................................ 23

7.12 FETCH NEXT ..................................................................................................................................................... 24

7.13 INSERT............................................................................................................................................................... 25

7.14 UPDATE ............................................................................................................................................................. 26

7.15 DELETE.............................................................................................................................................................. 27

8 Transaction management ..................................................................................................................................... 28

8.1 General concept.................................................................................................................................................. 28

8.2 Transaction operations...................................................................................................................................... 29

9 User management.................................................................................................................................................. 31

9.1 General concept.................................................................................................................................................. 31

9.2 User operations .................................................................................................................................................. 32

Annex A (informative) Usage of SCQL operations ................................................................................................ 36

iii
---------------------- Page: 3 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
Foreword

ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission)

form the specialized system for worldwide standardization. National bodies that are members of ISO or IEC

participate in the development of International Standards through technical committees established by the

respective organization to deal with particular fields of technical activity. ISO and IEC technical committees

collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental, in

liaison with ISO and IEC, also take part in the work.

In the field of information technology, ISO and IEC have established a joint technical committee, ISO/IEC JTC 1.

Draft International Standards adopted by the joint technical committee are circulated to national bodies for voting.

Publication as an International Standard requires approval by at least 75 % of the national bodies casting a vote.

International Standard ISO/IEC 7816-7 was prepared by Joint Technical Committee ISO/IEC JTC 1, Information

technology, Subcommitte SC 17, Identification cards and related devices.

ISO/IEC 7816 consists of the following parts, under the general title Identification cards — Integrated circuit(s) cards

with contacts :
— Part 1: Physical characteristics
— Part 2: Dimensions and location of the contacts
— Part 3: Electronic signals and transmission protocols
— Part 4: Interindustry commands for interchange

— Part 5: Numbering system and registration procedure for application identifiers

— Part 6: Interindustry data elements
— Part 7: Interindustry commands for Structured Card Query Language (SCQL)
— Part 8: Security related interindustry commands
Annex A of this part of ISO/IEC 7816 is for information only.
---------------------- Page: 4 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
Introduction

This part of ISO/IEC 7816 is one of a series of standards describing the parameters for integrated circuit(s) cards

with contacts and the use of such cards for international interchange.

These cards are identification cards intended for information exchange negotiated between the outside and the

integrated circuit in the card. As a result of an information exchange, the card delivers information (computation

results, stored data), and/or modifies its content (data storage, event memorization).

During the preparation of this part of ISO/IEC 7816, information was gathered concerning relevant patents upon

which application of this part of ISO/IEC 7816 might depend. Relevant patents were identified in France, the patent

holder is Gemplus. However, ISO cannot give authoritative or comprehensive information about evidence, validity or

scope of patents or like rights.

The patent holder has stated that licenses will be granted in appropriate terms to enable application of this part of

ISO/IEC 7816, provided that those who seek licenses agree to reciprocate.
Further information is available from
GEMPLUS
B.P. 100
13881 GEMENOS CEDEX
FRANCE
---------------------- Page: 5 ----------------------
INTERNATIONAL STANDARD © ISO/IEC ISO/IEC 7816-7:1999(E)
Identification cards — Integrated circuit(s) cards with contacts —
Part 7:
Interindustry commands for Structured Card Query Language (SCQL)
1 Scope
This part of ISO/IEC 7816 specifies

 the concept of a SCQL database (SCQL = Structured Card Query Language based on SQL, see ISO 9075)

and
 the related interindustry enhanced commands.
2 Normative references

The following normative documents contain provisions which, through reference in this text, constitute provisions of

this part of ISO/IEC 7816. For dated references, subsequent amendments to, or revisions of, any of these

publications do not apply. However, parties to agreements based on this part of ISO/IEC 7816 are encouraged to

investigate the possibility of applying the most recent editions of the normative documents indicated below. For

undated references, the latest edition of the normative document referred to applies. Members of ISO and IEC

maintain registers of currently valid International Standards.
ISO/IEC 9075:1992, Information technology — Database languages — SQL2.

ISO/IEC 7816-4:1995, Information technology — Identification cards — Integrated circuit(s) cards with contacts —

Part 4: Interindustry commands for interchange.

ISO/IEC 7816-6:1996, Identification cards — Integrated circuit(s) cards with contacts — Part 6: Interindustry data

elements.
3 Terms and definitions
For the purposes of this part of ISO/IEC 7816, the following definitions apply.
3.1
database basic user
SCQL user with no inherent rights
3.2
database file

structured set of database objects (tables, views, dictionaries) representing the content of a database

3.3
database object owner

SCQL user with the special right to create and drop objects and to manage privileges on these objects

3.4
database owner
initial SCQL user which manages objects and users of the database
---------------------- Page: 6 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
3.5
dictionary
view on a system table
3.6
system table

table maintained by the card for managing the database structure and database access

3.7
table
database object with a unique name and structured in columns and rows
3.8
view
logical subset of a table
4 Symbols (and abbreviated terms)

For the purposes of this part of ISO/IEC 7816, the following abbreviations apply:

APDU Application protocol data unit
API Application programming interface
DB Database
DB_O Database owner
DBBU Database basic user
DBF Database file
DBOO Database object owner
DF Dedicated file
DO Data object
ICC Integrated circuit(s) card
IFD Interface device
MF Master file
SCQL Structured card query language
SQL Structured query language
TLV Tag, length, value
5 SCQL database concept
5.1 SCQL database

A database in a card according to this part of ISO/IEC 7816 is called a SCQL database (SCQL = Structured Card

Query Language), since the commands for accessing are based on SQL-functionality (see ISO 9075) and coded

according to the principles of interindustry commands as defined in ISO/IEC 7816-4. The database itself is a

structured set of database objects called a database file DBF. Under a DF there shall be not more than one DBF

which is accessible after selection of the respective DF. A database may be also directly attached to the MF.

Fig.1 shows an example for the embedding of a database in the card.
---------------------- Page: 7 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
Application
DF DF
with a 1 2
database
Any
application
. . .
DBF
Database
Internal elementary files
file
and/or
working elementary files
Figure 1 — Application with a database in a multi-application card (example)

An application system may interwork with a SQL database as well as with a SCQL database using the same SQL-

API (API = Application Programming Interface). Thus, a card carrying a SCQL database may appear as a part of a

distributed SQL database environment. Fig. 2 shows a typical SQL configuration with a card integrated in the

system design.
Application
System
SQL-API
SQL-
SQL- SQL-
SCQL-
data-
commands commands
data-
base
base
SCQL-
ICC
commands
IFD/ICC
interface

Figure 2 — SCQL database as part of a distributed SQL database environment (example)

5.2 SCQL tables

A SCQL database contains objects called tables, views and dictionaries. Each object can be referenced by a unique

identifier.

A table is a structured data object with a unique name within a database. It consists of named columns and a

sequence of rows. The number of rows may be conceptually unlimited (i.e., only restricted by the available memory

space in the card), or limited. The table and the main characteristics are shown in fig. 3.

---------------------- Page: 8 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
Table name
Column Column Column
name 1 name 2 name 3
Row 1
Row 2
Row 3
Characteristics:
- Table name: unique, max. 8 characters
- No. of tables: not regulated by this standard
- Column name in table: unique, max. 8 char.
- No. of columns within a table: 1 - 15
- Max. no. of rows: not determined or fixed
- Column size: 0 - 254 bytes, if not specified
- Column data type: string
Figure 3 — SCQL table (example) and its main characteristics

After creation the table structure is persistent, i.e. neither an existing column can be withdrawn nor a new column

can be inserted. On a table the following actions can be performed:
 read (select)
 insert
 update
 delete.
5.3 SCQL views

A view is a logical subset of a table, which defines the part of the table accessible. Two types of views are to be

distinguished:

 a view (see fig. 4), which by definition fixes the accessible columns, is called in this context a static view and

 a view (see fig. 5), which restricts the access to those rows whose contents matches defined conditions (e.g. to

rows the value of which is greater '20'), is called in this context a dynamic view.

Table name
Column Column Column
name 1 name 2 name 3
View name
Column Column
Static view name 2 name 3
with access
restricted to the
2nd and 3rd column
Figure 4 — SCQL static view (example)
---------------------- Page: 9 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
Table name
Column Column Column
name 1 name 2 name 3
View name
Column Column Column
name 1 name 2 name 3
Dynamic view
with access
restricted to rows where the content of the
row matches one or more defined conditions
Figure 5 — SCQL dynamic view (example)

A combination of static view and dynamic view in the same view definition is also possible.

A view has like a table a unique name in a SCQL database. Several views may be defined on the same table.

On a view the following actions can be performed:
 read (select)
 update.
5.4 SCQL system tables and dictionaries

A system table is maintained by the card and contains information necessary to manage the database structure and

access. There are three system tables:
 the object description table (name *O)
the user description table (name *U)
 the privilege description table (name *P)

The object description table contains information about the tables and views stored in the database.

The user description table contains information about the users which have access to the database.

The privilege description table contains information about the privileges onto the database tables and views.

Privileges describe which tables and views can be accessed by which users, and which actions can be performed

by those users on the respective table or view.
The figures 6 - 8 show the system tables with their mandatory columns.
---------------------- Page: 10 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
(Object description table)
OBJDES OBJOPT
OBJNAMOBJOWN OBJTYP
Object Object Object Object Object
name owner type descriptor options
(table (user id) (T = table, (column (secu-
name or V = view) names in rity re-
view case of lated
name, table, data
unique) view de- objects,
finition in e.g. for
case of authenti-
view) cation)
Note: This system table may contain
additional implementation specific columns.
Figure 6 — Object description table
(User description table)
USROWN USROPT
USERID USRPRO
User User profile: User id User
iden- DB_O = of user options
tifier DB owner owner (security
(unique) DBOO = (person related
DB object owner who data
DBBU = assigns objects)
DB basic user the
user id)
Note: This system table may contain
additional implementation specific columns.
Figure 7 — User description table
(Privilege description table)
OBJNAM OBJUSR
USRPRI OBJOWN
Table name, User id Privileges User id
view name of the of the
or object user object
dictionary (grantee) owner
name (grantor)
Note: This system table may contain
additional implementation specific columns.
Figure 8 — Privilege description table
---------------------- Page: 11 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)

For access to the information contained in the system tables, views on these system tables can be created. A view

on a system table is called a SCQL dictionary. The only action which a user can perform on a dictionary is reading

(select).
5.5 SCQL user profiles

SCQL user profiles are characterized by special permissions. A user profile is attached to a user identifier stored in

the user description table. Table 1 shows the profiles and the attached permissions.

Table 1 — SCQL user profiles and attached permissions
Profile User Permission
DB_O Database owner - Adding/dropping of users with profile
DBOO or DBBU
- Creation/deletion of objects
(tables/views)
- Granting/revoking of privleges for objects
owned
- Creation/deletion of dictionaries with
access to all rows in the system tables
- Access to objects not owned according
to the privileges granted
DBOO Database object - Adding/dropping of users with profile
owner DBBU
- Creation/deletion of objects
(tables/views)
- Granting/revoking of privileges for
objects owned
- Creation/deletion of dictionaries with
access to rows where the DBOO is
registered as OBJOWN in *O, USROWN
in *U or OBJOWN in *P
- Access to objects not owned according
to the privileges granted
DBBU Database basic - Access to objects according to the
user with specific privileges granted
user id or the
general user id
PUBLIC

NOTE A user with the profile DB_O can only be inserted in the user description table during the SCQL database

installation.
6 SCQL related commands
6.1 General aspects

The 'Structured Card Query Language (SCQL)' is based on the functionality of the standardized 'Structured Query

Language (SQL)'. SQL statements are mapped onto SCQL operations within the PERFORM SCQL OPERATION

command (see fig. 9 and table 2).
---------------------- Page: 12 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
SQL statement SCQL operation
SQL SQL SQL SQL SQL
state- attri- word attri- word
...
ment bute bute
opera-
tion tag
Para- Para-
CLA INS P1 P2 Lc Lp
Lp meter meter ...
'10' = PERFORM Lp Lp
SCQL OPERATION
NOTES -
1. The coding schema for the data field is a sim-
plified TLV structure. Since meaning and posi-
tion of the data objects are fixed, tags are not
needed and therefore not present in the coding.
2. SQL words are not encoded in the data field.
3. If several items are grouped, then the dimension
has to be present before the grouped items.
Figure 9 — Mapping principle of a SQL statement onto a SCQL operation

The mandatory parameters of a command occur always in the sequence defined in the related command table.

Their tag is therefore not present. The optional parameters are - if not indicated otherwise - presented in TLV

format.

As well as the PERFORM SCQL OPERATION command two other commands belong to the SCQL environment,

but may be used also outside an SCQL environment:
 the PERFORM TRANSACTION OPERATION command (see fig. 10) and
 the PERFORM USER OPERATION command (see fig. 10).
6.2 Grouping and encoding of commands
The SCQL related commands can be grouped as shown in fig. 10.
SCQL Related Commands
PERFORM
PERFORM PERFORM
TRANS-
SCQL USER
ACTION
OPERATION OPERATION
OPERATION
CREATE TABLE BEGIN PRESENT USER
CREATE VIEW COMMIT CREATE USER
DROP TABLE ROLLBACK DELETE USER
DROP VIEW
CREATE DICTIONARY
GRANT
REVOKE
DECLARE CURSOR
OPEN
NEXT
FETCH
FETCH NEXT
INSERT
UPDATE
DELETE
Figure 10 — SCQL related commands
---------------------- Page: 13 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)

For the commands defined in this part of the standard the instruction codes and the coding of the respective

operations are shown in table 2.
Table 2 — Instruction codes and operations
INS-code Meaning
'10'
PERFORM SCQL OPERATION
P2 coding and meaning:
'80' = CREATE TABLE
'81' = CREATE VIEW
'82' = CREATE DICTIONARY
'83' = DROP TABLE
'84' = DROP VIEW
'85' = GRANT
'86' = REVOKE
'87' = DECLARE CURSOR
'88' = OPEN
'89' = NEXT
'8A' = FETCH
'8B' = FETCH NEXT
'8C' = INSERT
'8D' = UPDATE
'8E' = DELETE
'12' PERFORM TRANSACTION OPERATION
P2 coding and meaning:
'80' = BEGIN
'81' = COMMIT
'82' = ROLLBACK
'14'
PERFORM USER OPERATION
P2 coding and meaning:
'80' = PRESENT USER
'81' = CREATE USER
'82' = DELETE USER
The usage of these commands and encoding examples are shown in annex A.
6.3 Notation and special codings

In subsequent chapters the following notation is used for describing SQL statements:

 words in capital letters are SQL words (fixed expressions of the SQL language)
 [ ] means optional
 <...> means attribute string
 ::= means consists of
 | means or
 * means all
For encoding of parameters, the following notation is used:
 Lp = Length (coded in one byte) of the subsequent parameter

 <...> = parameter string of bytes with the length Lp and the meaning given in <...>

For encoding of a dimension D (e.g. no. of columns or no. of conditions), the following rule applies:

D ::= N
with N = no. of subsequent items, coded on one byte
D ::= Ln
with Ln = '01' (N coded in one byte).
---------------------- Page: 14 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)

An item consists of one or several consecutive parameters. The null dimension is coded on one byte set at '00'. The

meaning of a null dimension is either 'all columns' or 'no conditions' according to the command.

For the comparison operators which occur in search conditions, the coding according table 3 is used.

Table 3 — Coding of comparison operators
Comparison Coding Meaning
operator
= '3D' equal to
< '3C'
less than
> '3E' greater than
≤ '4C' less than or equal to
≥ '47' greater than or equal to
≠ '23' not equal to
6.4 Status bytes

The status bytes SW1-SW2 of a response denote the processing state in the card. Table 4 shows the general

meaning of the values of SW1-SW2 defined in this part of ISO/IEC 7816. For each command or performed

operation, an appropriate clause provides more detailed meanings.

The meaning of status bytes defined in part 4 of this standard and listed here are defined more precisely for the

usage of this part of the standard.
Table 4 — Status bytes
SW1-SW2 Defined Meaning
in part
Normal processing
'9000' 4 Command successful
'61xx' 4 Command successful, xx codes the
number of data bytes to be fetched
by GET RESPONSE
Warning processing
'6282' 4 End of table reached
Execution errors
'6500' 4 No information given
'6581' 4 Memory failure (e.g. info corrupted)
Checking errors
'6700' 4 Wrong length
Command not allowed
'6900' 4 No information given
'6982' 4 Security status not satisfied
'6985' 4 Necessary commands or operations
not performed before
Wrong parameters
'6A00' 4 No information given
'6A80' 4 Incorrect parameter in data field
'6A81' 4 Operation not supported
'6A84' 4 Not enough memory space
'6A88' 4 Referenced object not found
'6A89' 7 Object exists already
'6Cxx' 4 Wrong length Le: SW2 indicates the
exact length
'6D00' 4 Instruction code not supported
---------------------- Page: 15 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
6.5 Coding of identifiers
The following conventions for identifiers are defined :
::= [ | | <_>]
::= A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z
::= 0|1|2|3|4|5|6|7|8|9
::=
::=
::= <_>
::=
::= | SYSTAB
::=
|
|

|
|

|


::=
::=
::= |
::= .
::= *
::= |
::= CHOLDER
::= PUBLIC

CHOLDER is the general user id for the cardholder, PUBLIC is the general user id for a database basic user, see

table 1.

The meaning of an asterisk is 'don't care', i.e. the coding of this part is not compared.

For checking a user id, the following cases have to be distinguished:

If the user id is an individual id, then the user id has to be identical with the registered user id.

If the user id consists of a group id in combination with an individual id, then the following steps have to be

performed:
1) check whether the full user id is registered
2) if not, check, whether .* is registered

If the user id consists of a group id in combination with subgroup id and individual id, then the following steps have

to be performed:
a) check whether the full user id is registered
b) if not, check,whether ..* is registered
c) if not, check, whether .*.* is registered

The user id verification is performed, if a PRESENT USER operation is performed, but also in situations where

access control to tables, views and dictionaries is required (see DECLARE CURSOR and INSERT).

NOTE The user group construction mechanism is not part of SQL.
---------------------- Page: 16 ----------------------
© ISO/IEC
ISO/IEC 7816-7:1999(E)
6.6 Security attributes of tables, views and users
The following conventions for security attributes are defined :

::= or access control>

Security attributes associated to tables and views may be related to authentication procedures to be performed

before access or describe secure messaging mechanisms to be applied, if data manipulation operations are

performed (e.g. reading and writing in a confidential mode).
A security attribute attached to a user is related to
...