Authorities and Privileges
| Once a user has been authenticated and an attachment to an instance or a connection to a database has been established, the DB2 Database Manger evaluates any authorities and privileges that have been assigned to the user (these can be assigned directly to a user, or they can be obtained indirectly from group privileges that have been assigned to a group the user is a member of) to determine what operations the user is allowed to perform. Authorities convey a set of privileges and/or the right to perform high-level administrative and maintenance/utility operations against an instance or a database. Privileges, on the other hand, convey the rights to perform certain actions against specific database resources (such as tables and views). Together, authorities and privileges act to control access to the DB2 Database Manager for an instance, to one or more databases running under that instance's control, and to a particular database's objects. Users can only work with those objects for which they have been given the appropriate authorization ”that is, the required authority or privilege. Figure 3-3 provides a hierarchical view of the authorities and privileges that are recognized by DB2 UDB. Figure 3-3. Hierarchy of the authorities and privileges available with DB2 UDB. AuthoritiesDB2 UDB uses five different levels of authority to control how users perform administrative and/or maintenance operations against an instance or a database. These five levels are:
The first three of these levels apply to the DB2 Database Manager instance (and to all databases that are managed by that instance), while the remaining two apply only to specific databases within an instance. Furthermore, the three instance-level authorities can only be assigned to groups; the names of the groups that are assigned these authorities are stored in the DB2 Database Manager configuration file that is associated with the instance. Conversely, the two database-level authorities can be assigned to an individual user and/or to a group of users; groups and users that have been assigned database-level authorities are recorded in the system catalog tables of the database to which the authority applies. System Administrator authoritySystem Administrator (SYSADM) authority is the highest level of administrative authority available with DB2 UDB. Users that have been given this authority are allowed to run any available DB2 UDB utilities, execute any DB2 UDB command, perform any SQL operation, and control all objects within an instance, including databases, database partition groups, buffer pools, tablespaces, tables, views, indexes, schemas, aliases, data types, functions, procedures, triggers, packages, servers, and event monitors . In addition, users who have been given this authority are allowed to perform the following tasks :
SYSADM authority can only be assigned to a group, and this assignment is made by storing the appropriate group name in the sysadm_group parameter of the DB2 Database Manager configuration file associated with a particular instance. Individual membership in the group itself is controlled through the security facility used on the workstation where the instance has been defined. System Control authoritySystem Control (SYSCTRL) authority is the highest level of system/instance control authority available with DB2 UDB. Users that have been given this authority are allowed to perform maintenance and utility operations against both a DB2 Database Manager instance and any databases that fall under that instance's control. However, because SYSCTRL authority is designed to allow special users to maintain an instance containing sensitive data that they most likely do not have the right to access, users who are granted this authority do not implicitly receive authority to access the data stored in the databases they are allowed to perform maintenance and utility operations on. On the other hand, because a connection to a database must exist before some utility operations can be performed, users who are granted SYSCTRL authority for a particular instance also receive the privileges needed to connect to each database under that instance's control. Users with SYSCTRL authority (or higher) are allowed to perform the following tasks:
Like SYSADM authority, SYSCTRL authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysctrl_group parameter of the DB2 Database Manager configuration file that is associated with a particular instance. Again, individual membership in the group itself is controlled through the security facility that is used on the workstation where the instance has been defined. System Maintenance authoritySystem Maintenance (SYSMAINT) authority is the second highest level of system/instance control authority available with DB2 UDB. Users that have been given this authority are allowed to perform maintenance and utility operations against any database that falls under an instance's control ”but not against the instance itself. Like SYSCTRL authority, SYSMAINT authority is designed to allow special users to maintain a database containing sensitive data that they most likely do not have access to. Therefore, users who are granted this authority do not implicitly receive authority to access the data stored in the databases they are allowed to perform maintenance and utility operations on. However, because a connection to a database must exist before some utility operations can be performed, users who are granted SYSMAINT authority for a particular instance automatically receive the privileges needed to connect to each database under that instance's control. Users with SYSMAINT authority (or higher) are allowed to perform the following tasks:
Like SYSADM and SYSCTRL authority, SYSMAINT authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysmaint_group parameter of the DB2 Database Manager configuration file that is associated with a particular instance. Again, individual membership in the group itself is controlled through the security facility that is used on the workstation where the instance has been defined. Database Administrator authorityDatabase Administrator (DBADM) authority is the second highest level of administrative authority (below SYSADM) available with DB2 UDB. Users that have been given this authority are allowed to run most DB2 UDB utilities, issue database-specific DB2 commands, perform most SQL operations, and access data stored in any table in a database. However, they can only perform these functions on the database for which DBADM authority is held. Users with DBADM authority (or higher) are allowed to perform the following tasks:
On the other hand, only users with DBADM authority (or SYSADM authority) are allowed to:
Unlike SYSADM, SYSCTRL, and SYSMAINT authority, DBADM authority can be assigned to both individual users and groups. This assignment is made by executing the appropriate form of the GRANT SQL statement (which we will look at shortly). When a user is given DBADM authority for a particular database, they automatically receive CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA database privileges for that database as well.
Load authorityLoad (LOAD) authority is a special database level of administrative authority that has a much smaller scope that the DBADM authority. Users that have been given this authority, along with INSERT and in some cases DELETE privileges on a particular table are allowed to bulk-load data into that table, using either the AutoLoader utility ( db2atld command) or the LOAD command/API. LOAD authority is designed to allow special users to perform bulk-load operations against a database that they most likely cannot do anything else with. This authority level provides a way for Database Administrators to allow more users to perform special database operations without having to sacrifice control. In addition to being able to load data into a database table, users with LOAD authority (or higher) are allowed to perform the following tasks:
Like DBADM authority, LOAD authority can be assigned to both individual users and groups. This assignment is made by executing the appropriate form of the GRANT SQL statement. PrivilegesAs mentioned earlier, privileges are used to convey the rights to perform certain actions on specific database resources to both individual users and groups. With DB2 UDB, two distinct types of privileges exist: database privileges and object privileges. Database privilegesDatabase privileges apply to a database as a whole, and for most users, they act as identification that gets verified at the second security checkpoint that must be cleared before access to data is provided. Figure 3-4 shows the different types of database privileges available. Figure 3-4. Database privileges available with DB2 UDB. As you can see in Figure 3-4, eight different database privileges exist. They are:
At a minimum, a user must have CONNECT privilege on a database before they can work with any object in that database. Object privilegesUnlike database privileges, which apply to a database as a whole, object privileges only apply to specific objects within a database. These objects include schemas, tablespaces, tables, indexes, views, packages, routines, sequences, servers, and nicknames. Because the nature of each available database object varies, the individual privileges that exist for each object can vary as well. The following sections describe the different sets of object privileges that are available with DB2 UDB. Schema privilegesSchema privileges control what users can and cannot do with a particular schema. (A schema is an object that is used to logically classify and group other objects in the database; most objects are named using a naming convention that consists of a schema name, followed by a period, followed by the object name.) Figure 3-5 shows the different types of schema privileges available. Figure 3-5. Schema privileges available with DB2 UDB. As you can see in Figure 3-5, three different schema privileges exist. They are:
Objects that can be manipulated within a schema include tables, views, indexes, packages, user-defined data types, user-defined functions, triggers, stored procedures, and aliases. The owner of a schema (usually the individual who created the schema) automatically receives these privileges, along with the right to grant any combination of these privileges to other users and groups. Tablespace privilegesTablespace privileges control what users can and cannot do with a particular tablespace. (Tablespaces are used to control where data in a database physically resides.) Figure 3-6 shows the different types of tablespace privileges available. Figure 3-6. Tablespace privileges available with DB2 UDB. As you can see in Figure 3-6, two different tablespace privileges exist. They are:
The owner of a tablespace (usually the individual who created the tablespace) automatically receives CONTROL privilege and USE privilege for that tablespace. By default, whenever a new database is created, the USE privilege for tablespace USERSPACE1 is given to the group PUBLIC; however, this privilege can be revoked.
Table privilegesTable privileges control what users can and cannot do with a particular table in a database. (A table is a logical structure that is used to present data as a collection of unordered rows with a fixed number of columns .) Figure 3-7 shows the different types of table privileges available. Figure 3-7. Table privileges available with DB2 UDB. As you can see in Figure 3-7, eight different table privileges exist. They are:
The owner of a table (usually the individual who created the table) automatically receives CONTROL privilege, along with all other available table privileges, for that table. If the CONTROL privilege is later revoked from the table owner, all other privileges that were automatically granted to the owner for that particular table are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations. Index privilegesThe index privilege controls what users can and cannot do with a particular index. (An index is an ordered set of pointers that refer to one or more key columns in a base table; indexes are used to improve query performance.) Figure 3-8 shows the only index privilege available. Figure 3-8. Index privilege available with DB2 UDB. As you can see in Figure 3-8, only one index privilege exists. That privilege is the CONTROL privilege, which allows a user to remove (drop) the index from the database. Unlike the CONTROL privilege for other objects, the CONTROL privilege for an index does not provide a user with the ability to grant to or revoke from other users and groups any available index privilege. That's because only users who hold SYSADM or DBADM authority are allowed to grant and revoke CONTROL privileges for an object. The owner of an index (usually the individual who created the index) automatically receives CONTROL privilege for that index. View privilegesView privileges control what users can and cannot do with a particular view. (A view is a virtual table residing in memory that provides an alternative way of working with data that resides in one or more base tables.) Figure 3-9 shows the different types of view privileges available. Figure 3-9. View privileges available with DB2 UDB. As you can see in Figure 3-9, five different view privileges exist. They are:
In order to create a view, a user must hold appropriate privileges on each base table the view references. Once a view is created, the owner of that view (usually the individual who created the view) automatically receives all available view privileges ”with the exception of the CONTROL privilege ”for that view. A view owner will only receive CONTROL privilege for the view if they also hold CONTROL privilege for every base table the view references. Package privilegesPackage privileges control what users can and cannot do with a particular package. (A package is an object that contains the information needed by the DB2 Database Manager to process SQL statements in the most efficient way possible on behalf of an embedded SQL application.) Figure 3-10 shows the different types of package privileges available. Figure 3-10. Package privileges available with DB2 UDB. As you can see in Figure 3-10, three different package privileges exist. They are:
The owner of a package (usually the individual who created the package) automatically receives CONTROL privilege, along with all other available package privileges, for that package. If the CONTROL privilege is later revoked from the package owner, all other privileges that were automatically granted to the owner for that particular package are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations.
Routine privilegesRoutine privileges control what users can and cannot do with a particular routine. (A routine can be a user-defined function, a stored procedure, or a method that can be invoked by several different users.) Figure 3-11 shows the different types of routine privileges available. Figure 3-11. Routine privileges available with DB2 UDB. As you can see in Figure 3-11, two different routine privileges exist. They are:
The owner of a routine (usually the individual who created the routine) automatically receives CONTROL and EXECUTE privileges for that routine. If the CONTROL privilege is later revoked from the owner, the EXECUTE privilege will be retained and must be explicitly revoked in a separate operation. Sequence privilegesSequence privileges control what users can and cannot do with a particular sequence. (A sequence is an object that can be used to generate values automatically ”sequences are ideal for generating unique key values. Applications can use sequences to avoid the possible concurrency and performance problems that can occur when unique counters residing outside the database are used for data generation.) Figure 3-12 shows the different types of sequence privileges available. Figure 3-12. Sequence privileges available with DB2 UDB. As you can see in Figure 3-12, two different sequence privileges exist. They are:
The owner of a sequence (usually the individual who created the sequence) automatically receives CONTROL and USAGE privilege for that sequence. If the CONTROL privilege is later revoked from the owner, the USAGE privilege will be retained and must be explicitly revoked in a separate operation. Server privilegesThe server privilege controls what users can and cannot do with a particular federated database server. (A DB2 federated system is a distributed computing system that consists of a DB2 server, known as a federated server, and one or more data sources to which the federated server sends queries. Each data source consists of an instance of some supported relational database management system ”such as Oracle ”plus the database or databases that the instance supports.) Figure 3-13 shows the only type of server privilege available. Figure 3-13. Server privilege available with DB2 UDB. As you can see in Figure 3-13, only one server privilege exists. That privilege is the PASSTHRU privilege, which allows a user to issue Data Definition Language (DDL) and Data Manipulation Language (DML) SQL statements (as pass-through operations) directly to a data source via a federated server. Nickname privilegesNickname privileges control what users can and cannot do with a particular nickname. (When a client application submits a distributed request to a federated database server, the server forwards the request to the appropriate data source for processing. However, such a request does not identify the data source itself; instead, it references tables and views within the data source by using nicknames that map to specific table and view names at the data source. Nicknames are not alternate names for tables and views in the same way that aliases are; instead, they are pointers by which a federated server references external objects.) Figure 3-14 shows the different types of nickname privileges available. Figure 3-14. Nickname privileges available with DB2 UDB. As you can see in Figure 3-14, four different nickname privileges exist. They are:
The owner of a nickname (usually the individual who created the nickname) automatically receives CONTROL privilege, along with all other available nickname privileges, for that nickname. If the CONTROL privilege is later revoked from the nickname owner, all other privileges that were automatically granted to the owner for that particular nickname are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations. Requirements for Granting and Revoking Authorities and PrivilegesNot only do authorization levels and privileges control what a user can and cannot do, they also control what authorities and privileges a user can grant to and revoke from other users and groups. A list of the authorities and privileges a user who has been given a specific authority level or privilege is allowed to grant and revoke is shown in Table 3-1. Table 3-1. Requirements for Granting/Revoking Authorities and Privileges
|
EAN: 2147483647
Pages: 68