Related documentation ISO/EC 9075-4: 1999, Database Language SQL--Part 4: Persistent Stored Modules (SQL/PSM) Contains the 1999 ISO standard definition for SQL procedure control statements ISO/EC 9075-5: 1999, Database Language SQL -- Part 4: Host Language Bindings (SQL/Bindings) Contains the 1999 ISo standard definition for host language bindings and dynamic SQL XXll SQL Reference, volume 1
v ISO/IEC 9075-4:1999, Database Language SQL -- Part 4: Persistent Stored Modules (SQL/PSM) – Contains the 1999 ISO standard definition for SQL procedure control statements. v ISO/IEC 9075-5:1999, Database Language SQL -- Part 4: Host Language Bindings (SQL/Bindings) – Contains the 1999 ISO standard definition for host language bindings and dynamic SQL. Related documentation xxii SQL Reference, Volume 1
Chapter 1. Concepts This chapter provides a high-level view of concepts that are important to understand when using Structured Query Language(SQL). The reference material contained in the rest of this manual provides a more detailed view. Relational databases a relational database is a database that is treated as a set of tables and manipulated in accordance with the relational model of data. It contains a set of objects used to store, manage, and access data. Examples of such objects are tables, views, indexes, functions, triggers, and packages A partitioned relational database is a relational database whose data is managed across multiple partitions(also called nodes). This separation of data across partitions is transparent to users of most SQL statements. However, some data definition language(DDL)statements take partition information into consideration(for example, CREATE DATABASE PARTITION GROUP) Data definition language is the subset of SQL statements used to describe data relationships in a database. A federated database is a relational database whose data is stored in multiple data sources(such as separate relational databases). The data appears as if it were all in a single large database and can be accessed through traditional SQL queries. Changes to the data can be explicitly directed to the appropriate data source Structured Query Language(sQl SQL is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is treated as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more base tables QL statements are executed by a database manager. One of the functions of the database manager is to transform the specification of a result table into a uence of internal operations that optimize data retrieval. The transformation occurs in two phases: preparation and binding ht IBM Corp. 1993-2002
Chapter 1. Concepts This chapter provides a high-level view of concepts that are important to understand when using Structured Query Language (SQL). The reference material contained in the rest of this manual provides a more detailed view. Relational databases A relational database is a database that is treated as a set of tables and manipulated in accordance with the relational model of data. It contains a set of objects used to store, manage, and access data. Examples of such objects are tables, views, indexes, functions, triggers, and packages. A partitioned relational database is a relational database whose data is managed across multiple partitions (also called nodes). This separation of data across partitions is transparent to users of most SQL statements. However, some data definition language (DDL) statements take partition information into consideration (for example, CREATE DATABASE PARTITION GROUP). (Data definition language is the subset of SQL statements used to describe data relationships in a database.) A federated database is a relational database whose data is stored in multiple data sources (such as separate relational databases). The data appears as if it were all in a single large database and can be accessed through traditional SQL queries. Changes to the data can be explicitly directed to the appropriate data source. Structured Query Language (SQL) SQL is a standardized language for defining and manipulating data in a relational database. In accordance with the relational model of data, the database is treated as a set of tables, relationships are represented by values in tables, and data is retrieved by specifying a result table that can be derived from one or more base tables. SQL statements are executed by a database manager. One of the functions of the database manager is to transform the specification of a result table into a sequence of internal operations that optimize data retrieval. The transformation occurs in two phases: preparation and binding. © Copyright IBM Corp. 1993 - 2002 1
Structured Query Language(SQL) All executable SQL statements must be prepared before they can be executed The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. Authorization and privileges An authorization allows a user or group to perform a general task, such as onnecting to a database, creating tables, or administering a system. a privilege gives a user or group the right to access one specific database object in a specified way The database manager requires that a user be specifically authorized, either implicitly or explicitly, to use each database function needed to perform a specific task. Explicit authorities or privileges are granted to the user (GRANTEETYPE of U). Implicit authorities or privileges are granted to a group to which the user belongs(GRANTEETYPE of G). Thus, to create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table and so on SYSADM (System Administrator) DBADM SYSCTRL (Database Administrator System Resource Administrator) SYSMAINT (System Maintenance Administrator) Database Users with Privileges Figure 1. Hierarchy of Authorities and Privileges Persons with administrative authority have the task of controlling the database manager and are responsible for the safety and integrity of the data They control who will have access to the database manager and to what extent each user has access The database manager provides two administrative authorities SYSADM-system administrator authority 2 SQL Reference, Volume 1
All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. Authorization and privileges An authorization allows a user or group to perform a general task, such as connecting to a database, creating tables, or administering a system. A privilege gives a user or group the right to access one specific database object in a specified way. The database manager requires that a user be specifically authorized, either implicitly or explicitly, to use each database function needed to perform a specific task. Explicit authorities or privileges are granted to the user (GRANTEETYPE of U). Implicit authorities or privileges are granted to a group to which the user belongs (GRANTEETYPE of G). Thus, to create a table, a user must be authorized to create tables; to alter a table, a user must be authorized to alter the table; and so on. Persons with administrative authority have the task of controlling the database manager and are responsible for the safety and integrity of the data. They control who will have access to the database manager and to what extent each user has access. The database manager provides two administrative authorities: v SYSADM - system administrator authority SYSADM (System Administrator) DBADM (Database Administrator) Database Users with Privileges SYSCTRL (System Resource Administrator) SYSMAINT (System Maintenance Administrator) Figure 1. Hierarchy of Authorities and Privileges Structured Query Language (SQL) 2 SQL Reference, Volume 1
Authorization and privileges SYSADM authority is the highest level of authority and has control over all he resources created and maintained by the database manager. SYSADM authority includes all the authorities of DBADM, SYSCTRL, and SYSMAINT, and the authority to grant or revoke DBADM authorities DBADM-database administrator authority DBADM authority is the administrative authority specific to a single database. This authority includes privileges to create objects, issue database commands, and access the data in any of its tables through SQL statements DBADM authority also includes the authority to grant or revoke CONTROL and individual privileges The database manager also provides two system control authorities SYSCTRL- system control authority SYSCTRL authority is the higher level of system control authority applies only to operations affecting system resources. It does not direct access to data. This authority includes privileges to create, update, or drop a database; to stop an instance or a database; and to create or drop a table space SYSMAINT-system maintenance authority SYSMAINT authority is the second level of system control authority. A user with SYSMAINT authority can perform maintenance operations on all databases associated with an instance. It does not allow direct access to data. This authority includes privileges to update database configuration and to monitor a database Database authorities apply to activities that an administrator has allowed a user to perform within a database; they do not apply to a specific instance of a database object. For example, a user may be granted the authority to create packages but not to create tables. rivileges apply to activities that an administrator or an object owner has allowed a user to perform on database objects. Users with privileges can may have the privilege to create a view on a table but not a trigger on theser create objects, strictly defined by the privileges they hold. For example, a ame table. Users with privileges have access to the objects they own, and can pass privileges on their own objects to other users through the grant statement CONTROL Privilege allows a user to access a specific database object, as required, and to grant and revoke privileges to and from other users on that object. DBADM authority is required to grant CONTROL Privilege Chapter 1. Concepts 3
SYSADM authority is the highest level of authority and has control over all the resources created and maintained by the database manager. SYSADM authority includes all the authorities of DBADM, SYSCTRL, and SYSMAINT, and the authority to grant or revoke DBADM authorities. v DBADM - database administrator authority DBADM authority is the administrative authority specific to a single database. This authority includes privileges to create objects, issue database commands, and access the data in any of its tables through SQL statements. DBADM authority also includes the authority to grant or revoke CONTROL and individual privileges. The database manager also provides two system control authorities: v SYSCTRL - system control authority SYSCTRL authority is the higher level of system control authority and applies only to operations affecting system resources. It does not allow direct access to data. This authority includes privileges to create, update, or drop a database; to stop an instance or a database; and to create or drop a table space. v SYSMAINT - system maintenance authority SYSMAINT authority is the second level of system control authority. A user with SYSMAINT authority can perform maintenance operations on all databases associated with an instance. It does not allow direct access to data. This authority includes privileges to update database configuration files; to back up a database or a table space; to restore an existing database; and to monitor a database. Database authorities apply to activities that an administrator has allowed a user to perform within a database; they do not apply to a specific instance of a database object. For example, a user may be granted the authority to create packages but not to create tables. Privileges apply to activities that an administrator or an object owner has allowed a user to perform on database objects. Users with privileges can create objects, strictly defined by the privileges they hold. For example, a user may have the privilege to create a view on a table but not a trigger on the same table. Users with privileges have access to the objects they own, and can pass privileges on their own objects to other users through the GRANT statement. CONTROL privilege allows a user to access a specific database object, as required, and to grant and revoke privileges to and from other users on that object. DBADM authority is required to grant CONTROL privilege. Authorization and privileges Chapter 1. Concepts 3
Authorization and privileges Individual privileges and database authorities allow a specific function but do not include the right to grant the same privileges or authorities to other users The right to grant table, view, or schema privileges to others can be extended to other users through the WITH GRANT OPTION on the GRANT statement. Schemas A schema is a collection of named objects. Schemas provide a logical lassification of objects in the database. A schema can contain tables, views, nicknames, triggers, functions, packages, and other objects A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA Statement with the current user recorded as the schema owner. It can also be implicitly created when another object is created, provided that the user has IMPLICIT_SCHEMA authority. A schema name is used as the high order part of a two-part object name. If the object is specifically qualified with a schema name when created, the object is assigned to that schema. If no schema name is specified when the object is created, the default schema name is used. For example, a user with DBADM authority creates a schema called C for user CREATE SCHEMA C AUTHORIZATION A User A can then issue the following statement to create a table called X in chema C: CREATE TABLE C X(COLl INT) Some schema names are reserved. For example, built-in functions belong to the SYSIBM schema, and the pre-installed user-defined functions belong to the SYSFUN schema When a database is created, all users have IMPLICIT_SCHEMA authority. This illows any user to create objects in any schema not already in existence. An implicitly created schema allows any user to create other objects in this chema. The ability to create aliases, distinct types, functions, and triggers is extended to implicitly created schemas. The default privileges on an implicitly created schema provide backward compatibility with previous versions If IMPLICIT_ SCHEMA authority is revoked from PUBLIC, schemas can be explicitly created using the CREATE SCHEMA statement, or implicitly created by users(such as those with DBADM authority) who have been granted IMPLICIT_SCHEMA authority. Although revoking IMPLICIT_SCHEMA 4 SQL Reference, Volume 1
Individual privileges and database authorities allow a specific function but do not include the right to grant the same privileges or authorities to other users. The right to grant table, view, or schema privileges to others can be extended to other users through the WITH GRANT OPTION on the GRANT statement. Schemas A schema is a collection of named objects. Schemas provide a logical classification of objects in the database. A schema can contain tables, views, nicknames, triggers, functions, packages, and other objects. A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA statement with the current user recorded as the schema owner. It can also be implicitly created when another object is created, provided that the user has IMPLICIT_SCHEMA authority. A schema name is used as the high order part of a two-part object name. If the object is specifically qualified with a schema name when created, the object is assigned to that schema. If no schema name is specified when the object is created, the default schema name is used. For example, a user with DBADM authority creates a schema called C for user A: CREATE SCHEMA C AUTHORIZATION A User A can then issue the following statement to create a table called X in schema C: CREATE TABLE C.X (COL1 INT) Some schema names are reserved. For example, built-in functions belong to the SYSIBM schema, and the pre-installed user-defined functions belong to the SYSFUN schema. When a database is created, all users have IMPLICIT_SCHEMA authority. This allows any user to create objects in any schema not already in existence. An implicitly created schema allows any user to create other objects in this schema.The ability to create aliases, distinct types, functions, and triggers is extended to implicitly created schemas. The default privileges on an implicitly created schema provide backward compatibility with previous versions. If IMPLICIT_SCHEMA authority is revoked from PUBLIC, schemas can be explicitly created using the CREATE SCHEMA statement, or implicitly created by users (such as those with DBADM authority) who have been granted IMPLICIT_SCHEMA authority. Although revoking IMPLICIT_SCHEMA Authorization and privileges 4 SQL Reference, Volume 1