Privileges enable users to create or access database resources. Authority levels provide a method of grouping privileges and higher-level database manager maintenance and utility operations. Together, these act to control access to the database manager and its database objects. Users can access only those objects for which they have the appropriate authorization, that is, the required privilege or authority.
The following authorities exist:
The following types of privileges exist:
Figure 24 illustrates the relationship between authorities and their span of control (database, database manager).
Figure 24. Hierarchy of Authorities
|
A user or group can have one or more of the following levels of authorization:
Users with administrative authority (SYSADM or DBADM) or ownership privileges (CONTROL) can grant and revoke privileges to and from others, using the GRANT and REVOKE statements. (See Controlling Access to Database Objects.) It is also possible to grant a table, view, or schema privilege to another user if that privilege is held WITH GRANT OPTION. However, the WITH GRANT OPTION does not allow the person granting the privilege to revoke the privilege once granted. You must have SYSADM authority, DBADM authority, or CONTROL privilege to revoke the privilege.
A user or group can be authorized for any combination of individual privileges or authorities. When a privilege is associated with a resource, that resource must exist. For example, a user cannot be given the SELECT privilege on a table unless that table has previously been created.
| Note: | Care must be taken when an authorization name is given authorities and privileges and there is no user created with that authorization name. At some later time, a user can be created with that authorization name and automatically receive all of the authorities and privileges associated with that authorization name. |
Refer to the Command Reference, the Administrative API Reference, or the SQL Reference for information about what authorization is required for a particular command, API, or SQL statement.
SYSADM authority is the highest level of administrative authority. Users with SYSADM authority can run utilities, issue database and database manager commands, and access the data in any table in any database within the database manager instance. It provides the ability to control all database objects in the instance, including databases, tables, views, indexes, packages, schemas, servers, aliases, data types, functions, procedures, triggers, table spaces, nodegroups, buffer pools, and event monitors.
SYSADM authority is assigned to the group specified by the sysadm_group configuration parameter (refer to "Configuring DB2" in Administration Guide, Performance). Membership in that group is controlled outside the database manager through the security facility used on your platform. Refer to the Quick Beginnings for information on how to use your system security facility to create, change, or delete SYSADM authorities.
Only a user with SYSADM authority can perform the following functions:
In addition, a user with SYSADM authority can perform the functions of users with the following authorities:
SYSCTRL authority is the highest level of system control authority. This authority provides the ability to perform maintenance and utility operations against the database manager instance and its databases. These operations can affect system resources, but they do not allow direct access to data in the databases. System control authority is designed for users administering a database manager instance containing sensitive data.
SYSCTRL authority is assigned to the group specified by the sysctrl_group configuration parameter (refer to "Configuring DB2" in Administration Guide, Performance). If a group is specified, membership in that group is controlled outside the database manager through the security facility used on your platform.
Only a user with SYSCTRL authority or higher can do the following:
In addition, a user with SYSCTRL authority can perform the functions of users with System Maintenance Authority (SYSMAINT) authority.
Users with SYSCTRL authority also have the implicit privilege to connect to a database.
SYSMAINT authority is the second level of system control authority. This authority provides the ability to perform maintenance and utility operations against the database manager instance and its databases. These operations can affect system resources, but they do not allow direct access to data in the databases. System maintenance authority is designed for users maintaining databases within a database manager instance that contains sensitive data.
SYSMAINT authority is assigned to the group specified by the sysmaint_group configuration parameter (refer to "Configuring DB2" in Administration Guide, Performance). If a group is specified, membership in that group is controlled outside the database manager through the security facility used on your platform.
Only a user with SYSMAINT or higher system authority can do the following:
A user with SYSMAINT, DBADM, or higher authority can do the following:
Users with SYSMAINT authority also have the implicit privilege to connect to a database.
DBADM authority is the second highest level of administrative authority. It applies only to a specific database, and allows the user to run certain utilities, issue database commands, and access the data in any table in the database. When DBADM authority is granted, BINDADD, CONNECT, CREATETAB, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA privileges are granted as well. Only a user with SYSADM authority can grant or revoke DBADM authority. Users with DBADM authority can grant privileges on the database to others and can revoke any privilege from any user regardless of who granted it.
Only a user with DBADM or higher authority can do the following:
A user with DBADM, SYSMAINT, or higher authority can do the following:
| Note: | A DBADM can only perform the above functions on the database for which DBADM authority is held. |
Figure 25 shows the database privileges.
Figure 25. Database Privileges
 |
Database privileges involve actions on a database as a whole:
Only users with SYSADM or DBADM authority can grant and revoke these privileges to and from other users.
| Note: | When a database is created, the following privileges are automatically
granted to PUBLIC:
|
To remove any privilege, a DBADM or SYSADM must explicitly revoke the privilege from PUBLIC.
When a new database is created, or when a database is migrated from the previous release, PUBLIC is given IMPLICIT_SCHEMA database authority. With this authority, any user can create a schema by creating an object and specifying a schema name that does not already exist. SYSIBM becomes the owner of the implicitly created schema and PUBLIC is given the privilege to create objects in this schema.
If control of who can implicitly create schema objects is required for the database, IMPLICIT_SCHEMA database authority should be revoked from PUBLIC. Once this is done, there are only three (3) ways that a schema object is created:
A user always has the ability to explicitly create their own schema using their own authorization name.
Schema privileges are in the object privilege category. Object privileges are shown in Figure 26.
 |
Schema privileges involve actions on schemas in a database. A user may be granted any of the following privileges:
The owner of the schema has all of these privileges and the ability to grant them to others. The objects that are manipulated within the schema object include: tables, views, indexes, packages, data types, functions, triggers, procedures, and aliases.
Table and view privileges involve actions on tables or views in a database. A user must have CONNECT privilege on the database to use any of the following privileges:
The privilege to grant these privileges to others may also be granted using the WITH GRANT OPTION on the GRANT statement.
| Note: | When a user or group is granted CONTROL privilege on a table, all other
privileges on that table are automatically granted WITH GRANT OPTION.
If you subsequently revoke the CONTROL privilege on the table from a user,
that user will still retain the other privileges that were automatically
granted. To revoke all the privileges that are granted with the CONTROL
privilege, you must either explicitly revoke each individual privilege or
specify the ALL keyword on the REVOKE statement, for example:
REVOKE ALL
ON EMPLOYEE FROM USER HERON
|
When working with typed tables, there are implications regarding table and view privileges.
| Note: | Privileges may be granted independently at every level of a table hierarchy. As a result, a user granted a privilege on a supertable within a hierarchy of typed tables may also indirectly affect any subtables. However, a user can only operate directly on a subtable if the necessary privilege is held on that subtable. |
The supertable/subtable relationships among the tables in a table hierarchy mean that operations such as SELECT, UPDATE, and DELETE will affect the rows of the operation's target table and all its subtables (if any). This behavior can be called "substitutability". For example, suppose that you have created an Employee table of type Employee_t with a subtable Manager of type Manager_t. A manager is a (specialized) kind of employee, as indicated by the type/subtype relationship between the structured types Employee_t and Manager_t and the corresponding table/subtable relationship between the tables Employee and Manager. As a result of this relationship, the SQL query:
SELECT * FROM Employee
will return the object identifier and Employee_t attributes for both employees and managers. Similarly, the update operation:
UPDATE Employee SET Salary = Salary + 1000
will give a thousand dollar raise to managers as well as regular employees.
A user with SELECT privilege on Employee will be able to perform this SELECT operation even if they do not have an explicit SELECT privilege on Manager. However, such a user will not be permitted to perform a SELECT operation directly on the Manage subtable, and will therefore not be able to access any of the non-inherited columns of the Manager table.
Similarly, a user with UPDATE privilege on Employee will be able to perform an UPDATE operation on Employee, thereby affecting both regular employees and managers, even without having the explicit UPDATE privilege on the Manager table. However, such a user will not be permitted to perform UPDATE operations directly on the Manager subtable, and will therefore not be able to update non-inherited columns of the Manager table.
The following manuals provide information about the authorizations required to execute specific commands, APIs, or SQL statements:
Refer to Administration Guide, Performance for information about the authorization required to update catalog statistics.
For information about how view privileges are determined, refer to the CREATE VIEW statement in the SQL Reference manual.
Nickname privileges involve actions on nicknames in a database. These privileges do not affect privileges on the data source objects referenced by nicknames. A user must have CONNECT privilege on the database to use any of the following privileges:
The privilege to grant these privileges to others can also be granted using the WITH GRANT OPTION on the GRANT statement.
| Note: | When a user or group is granted CONTROL privilege on a nickname, all other privileges on that nickname are automatically granted WITH GRANT OPTION. If you subsequently revoke the CONTROL privilege on the nickname from a user, that user will still retain the other privileges that were automatically granted. |
To access data source data, you must also have the proper authorization for the objects at data sources referenced by nicknames.
When a user accesses a view that references one or more nicknames, that user must be authorized to access the view and the objects that the nicknames reference at data sources.
There is one server privilege: PASSTHRU. This privilege controls which authorization IDs can issue DDL and DML statements directly (pass-through operations) to data sources.
DB2 provides two SQL statements to control pass-through operations:
A sample statement granting pass-through authorization to the user SHAWN for the server ORACLE1 is:
GRANT PASSTHRU ON SERVER ORACLE1 TO USER SHAWN
For complete information on the syntax of PASSTHRU statements, see the SQL Reference.
A package is a database object that contains the information needed by the database manager to access data in the most efficient way for a particular application program. Package privileges enable a user to create and manipulate packages. The user must have CONNECT privilege on the database to use any of the following privileges:
In addition to these package privileges, the BINDADD database privilege allows users to create new packages or rebind an existing package in the database.
Users with the authority to execute a package containing nicknames don't need additional privileges or an authority level for the nicknames within the package; however, they will need to pass authentication checks at the data sources containing the objects referenced by the nicknames. In addition, package users must have the appropriate privileges or authority levels for data source objects at the data source.
It is possible that packages containing nicknames might require additional authorization steps because DB2 uses dynamic SQL when communicating with DB2 Family data sources. The authorization ID running the package at the data source must have the appropriate authority to execute the package dynamically at that data source. See the SQL Reference for more information about how DB2 processes static and dynamic SQL.
The creator of an index or an index specification automatically receives CONTROL privilege on the index. CONTROL privilege on an index is really the ability to drop the index. To grant CONTROL privilege on an index, a user must have SYSADM or DBADM authority.
The table-level INDEX privilege allows a user to create an index on that table (see Table and View Privileges).