DB2 controls access to its resources by explicitly or implicitly granting privileges to an ID. GRANT and REVOKE statements explicitly give and remove privileges. Ownership of a resource is implicitly granted and cannot be revoked. All privileges are recorded in the DB2 catalog. Each row in the catalog table represents one or more privileges that have been granted to an authorization ID. This ID can be your primary authorization ID or one of your secondary authorization IDs. This secondary ID is associated with your primary ID through the signon or connection exit at connection time or by the SET CURRENT SQLID statement. When a DB2 object is accessed, DB2 checks the appropriate catalog table for each ID until one has the required privilege assigned to it. Obviously, the more secondary IDs assigned to the primary ID, the slower DB2 processes.
For some privileges, it is more complicated to determine which users are granted the privilege. With the UPDATE privileges, DB2 must check two tables if UPDATE is restricted to a column level: one for the table privileges and one for the column privileges.
Authorization checking for resources when a plan is bound can take place at two different times: at bind time and at execution time.
Authorization checking for the Distributed Data Facility (DDF) is dynamic and performed by the DB2 subsystem that owns the data.
Dynamic SQL statements can be created when the program runs. These statements contain information that is furnished only at execution time. They are created automatically when you request an action through, for example, SPUFI (SQL Processor Using File Input), a TSO utility that enables you to execute SQL statements without imbedding them in an application program. DB2 checks the authorization ID making the request (the one executing the SQL statements) against the catalog tables when the request is made. This authorization ID is the value of the CURRENT SQLID register. This value can be your primary ID or one of your secondary IDs. If you have SYSADM authority, you can set the current SQL ID to any authorization ID. The catalog must state that the ID is authorized to perform the requested function on the resource for DB2 to allow the action.
Dynamic SQL statements can increase the amount of I/O to the catalog tables and increase performance overhead.
When you create an application plan from static SQL statements imbedded in a program, you are binding a plan. You can specify the owner of the plan when you bind or rebind it. The default owner is your primary authorization ID. During the bind, DB2 checks whether the ID that you specify as the owner is your primary ID or one of your secondary IDs. (Of course, system DBADM, SYSCTRL, or SYSADM can specify any ID as owner.) In addition, DB2 checks whether this ID has all the appropriate privileges needed by the plan including the BIND privilege.
You can also specify when the owner must have all the privileges required by the binding of the plan—at bind time or at execution time.
For other users to use the plan, they require only the EXECUTE privilege on the plan. No other privileges or authorities are necessary. DB2 uses the value of the current SQL ID to authorize use of the plan. A user then exercises some of the owner's privileges when he executes the plan.
If the owner of the plan changes or some of the privileges are revoked from the owner, the plan is invalidated. It must be rebound and new owner must be granted the required privileges.
DB2 enables client applications that run in a remote environment to access data in a local DB2 server. It also enables local DB2 applications to access data at remote relational database systems. A local subsystem is the subsystem to which the ID is connected. The rest of the subsystems are considered remote to the local subsystem.
DB2 records all privileges to access a remote object in the catalog of the subsystem that manages the object. Only a local authorization ID can issue the pertinent GRANT statement for a subsystem.
|
Copyright © 2011 CA Technologies.
All rights reserved.
|
|