Authorization Considerations for Embedded SQL

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.

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 to start them. You can use the Db2 APIs to perform the Db2 administrative functions from within your application program. For example, to re-create a package stored in the database without the need for a bind file, you can use the sqlarbnd (or REBIND) API.

Groups provide a convenient means of performing authorization for a collection of users without having to grant or revoke privileges for each user individually. Group membership is considered for the execution of dynamic SQL statements, but not for static SQL statements. PUBLIC privileges are, however, considered for the execution of static SQL statements. For example, suppose you have an embedded SQL stored procedure with statically bound SQL queries against a table called STAFF. If you try to build this procedure with the CREATE PROCEDURE statement, and your account belongs to a group that has the select privilege for the STAFF table, the CREATE statement will fail with a SQL0551N error. For the CREATE statement to work, your account directly needs the select privilege on the STAFF table.

When you design your application, consider the privileges your users will need to run the application. The privileges required by your users depend on:

Whether your application uses dynamic SQL, including JDBC and CLI, or static SQL. For information about the privileges required to issue a statement, see the description of that statement.
Which APIs the application uses. For information about the privileges and authorities required for an API call, see the description of that API.

Groups provide a convenient means of performing authorization for a collection of users without having to grant or revoke privileges for each user individually. In general, group membership is considered for dynamic SQL statements, but is not considered for static SQL statements. The exception to this general case occurs when privileges are granted to PUBLIC: these are considered when static SQL statements are processed.

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.

Because PAYROLL issues many different SELECT statements, the application you design for PAYROLL could probably make good use of dynamic SQL. The dynamic SQL would require that PAYROLL have SELECT privilege on the STAFF table. This requirement is not a problem because PAYROLL requires full access to the table.

However, BUDGET, should not have access to each employee's salary. This means that you should not grant SELECT privilege on the STAFF table to BUDGET. Because 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 enables BUDGET to obtain the required information, without exposing the information that BUDGET should not see.