When an application requires users to authenticate for DriverManager.getConnection(String username, String password) or DataSource.getConnection(), and the application runs SQL statements dynamically, users must have the required privileges on the database objects that the statements reference. Otherwise, they cannot run the SQL statements against those objects.
When an application uses these methods and runs SQL statements statically, users must have EXECUTE privilege on the packages that contain the SQL statements. When a user has EXECUTE privilege on a package, the user can run all of the SQL statements that are in that package. For example, a user might have privileges on only one database object that is referenced by a group of SQL statements. If you bind those SQL statements into a package along with other statements and grant the user EXECUTE privilege on the package, the user can now run all the statements in the package, not only the ones that refer to the one database object that the user originally had privileges for.
Therefore, during the installation of the application, either the owner of the application or the DBA must determine whether to grant EXECUTE privilege on the needed packages to all of the users who already have the required privileges to run SQL statements dynamically. If the owner or DBA do grant EXECUTE to the current users, some users might then be able to run SQL statements that they could not run previously. If the owner or DBA do not grant EXECUTE to a user, that user will not be able to run the SQL statements that he could run previously.
If multiple applications use the same DataSource object and require users to authenticate, a user able to run the SQL statements for one of those applications could become able to run SQL statements from any of the applications that use that DataSource. Malicious users could hack together a new application that uses client optimization in order to run statically any of the SQL statements that are issued by all of the applications that share that DataSource object. Therefore, if individual users are to have access only to specific applications or only to specific SQL within the set of applications, then that level of security must be handled by the application server or other container.
For these applications, authorization is managed by the application server or other container. Users with access to an application can use it whether it runs SQL statements dynamically or statically.
After you configure the application to run with client optimization and bind the SQL statements, you can revoke privileges on database objects from the user that is specified in the DataSource object. You can also grant the EXECUTE privilege on the application's DB2 packages to that same user. Therefore, all users with access to the application can run all of the SQL statements that are in those packages.