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. DB2 uses a set of privileges to provide protection for the information that you store in it. For more information about the different privileges, refer to the Administration Guide: Planning.
Most SQL statements require some type of privilege on the database objects which the statement utilizes. Most API calls usually do not require any privilege on the database objects which the call utilizes, however, many APIs require that you possess the necessary authority in order to invoke them. The DB2 APIs enable you to perform the DB2 administrative functions from within your application program. For example, to recreate a package stored in the database without the need for a bind file, you can use the sqlarbnd (or REBIND) API. For details on each DB2 API, refer to the Administrative API Reference.
For information on the required privilege to issue each SQL statement, refer to the SQL Reference. For information on the required privilege and authority to issue each API call, refer to the Administrative API Reference.
When you design your application, consider the privileges your users will need to run the application. The privileges required by your users depend on:
To use dynamic SQL in a package bound with DYNAMICRULES RUN (default), the person that runs a dynamic SQL application must have the privileges necessary to issue each SQL request performed, as well as the EXECUTE privilege on the package. The privileges may be granted to the user's authorization ID, to any group of which the user is a member, or to PUBLIC.
If you bind the application with the DYNAMICRULES BIND option, DB2 associates your authorization ID with the application packages. This allows any user that runs the application to inherit the privileges associated your authorization ID.
The person binding the application (for embedded dynamic SQL applications) only needs the BINDADD authority on the database, if the program contains no static SQL. Again, this privilege can be granted to the user's authorization ID, to a group of which the user is a member, or to PUBLIC.
When you bind a dynamic SQL package with the DYNAMICRULES BIND option, the user that runs the application only needs the EXECUTE privilege on the package. To bind a dynamic SQL application with the DYNAMICRULES BIND option, you must have the privileges necessary to perform all the dynamic and static SQL statements in the application. If you have SYSADM or DBADM authority and bind packages with DYNAMICRULES BIND, consider using the OWNER BIND option to designate a different authorization ID. OWNER BIND prevents the package from automatically inheriting SYSADM or DBADM privileges on dynamic SQL statements. For more information on DYNAMICRULES BIND and OWNER BIND, refer to the BIND command in the Command Reference.
To use static SQL, the user running the application only needs the EXECUTE privilege on the package. No privileges are required for each of the statements that make up the package. The EXECUTE privilege may be granted to the user's authorization ID, to any group of which the user is a member, or to PUBLIC.
Unless you specify the VALIDATE RUN option when binding the application, the authorization ID you use to bind the application must have the privileges necessary to perform all the statements in the application. If VALIDATE RUN was specified at BIND time, all authorization failures for any static SQL within this package will not cause the BIND to fail and those statements will be revalidated at run time. The person binding the application must always have BINDADD authority. The privileges needed to execute the statements must be granted to the user's authorization ID or to PUBLIC. Group privileges are not used when binding static SQL statements. As with dynamic SQL, the BINDADD privilege can be granted to the user authorization ID, to a group of which the user is a member, or to PUBLIC.
These properties of static SQL give you very precise control over access to information in DB2. See the example at the end of this section for a possible application of this.
Most of the APIs provided by DB2 do not require the use of privileges, however, many do require some kind of authority to invoke. For the APIs that do require a privilege, the privilege must be granted to the user running the application. The privilege may be granted to the user's authorization ID, to any group of which the user is a member, or to PUBLIC. For information on the required privilege and authority to issue each API call, see the Administrative API Reference.
Consider two users, PAYROLL and BUDGET, who need to perform queries against the STAFF table. PAYROLL is responsible for paying the employees of the company, so it needs to issue a variety of SELECT statements when issuing paychecks. PAYROLL needs to be able to access each employee's salary. BUDGET is responsible for determining how much money is needed to pay the salaries. BUDGET should not, however, be able to see any particular employee's salary.
Since PAYROLL issues many different SELECT statements, the application you design for PAYROLL could probably make good use of dynamic SQL. This would require that PAYROLL have SELECT privilege on the STAFF table. This is not a problem since PAYROLL needs full access to the table anyhow.
BUDGET, on the other hand, should not have access to each employee's salary. This means that you should not grant SELECT privilege on the STAFF table to BUDGET. Since BUDGET does need access to the total of all the salaries in the STAFF table, you could build a static SQL application to execute a SELECT SUM(SALARY) FROM STAFF, bind the application and grant the EXECUTE privilege on your application's package to BUDGET. This lets BUDGET get the needed information without exposing the information that BUDGET should not see.