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 20 illustrates the hierarchical relationship between authorities and privileges. In the hierarchy, the lower level authorities and privileges are subsets of those above them.
Figure 20. Hierarchy of Authorities and Privileges
|
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. |
See the Command Reference, the 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, 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 (see "System Administration Authority Group Name (sysadm_group)"). Membership in that group is controlled outside the database manager through the security facility used on your platform. See the Quick Beginningsfor 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 (see "System Control Authority Group Name (sysctrl_group)"). 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 (see "System Maintenance Authority Group Name (sysmaint_group)"). 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. |
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 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:
See "User Update-Capable Catalog Statistics" for information about the authorization required to update catalog statistics.
For information about how view privileges are determined, see the CREATE VIEW statement in the SQL Reference manual.
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.
The creator of an index 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").