Preventing Public Access to the DB2 Database - Part 2 - Page 2

PUBLIC and support for Static SQL and Views

Static SQL programs are packages and views. A bind procedure will prepare all SQL statements stored into the bind file, generated by precompiler. As a result, a bind process will create a database package and save in the database access plan. During the bind process, package authorization is checked against database and actual schema privileges. The package creator must have a full set of privileges on the underlying tables and views to execute all embedded SQL statements. These privileges have to be granted directly to the user or over the PUBLIC group. The same conditions have to exist for the package rebind operation. Let's look at the example:

The user group db2users have been granted select right, the user ARTIST has been granted update right and the group PUBLIC (ARTIST is member) has been granted insert right on the table XFILES. A bind file has following content:

#cat secure.bnd
...
insert into table XFILES;
update table XFILES; 
select from table XFILES;    
...

Connecting with authorization ARTIST and run a bind command:

db2 =< connect to artist user artist using xxx123
   Database Connection Information

 Database server        = DB2/UNIX 7.1.0
 SQL authorization ID   = ARTIST
 Local database alias   = ARTIST

db2=< BIND secure.bnd blocking all grant public

The bind will fail with an error message. The user ARTIST could not bind the package to the database because the select privilege is granted to the OS group db2user. Group db2user is the operating system group and group privileges will not be checked during bind process.

Having granted a select privilege to the PUBLIC group, binding will succeed.

The same behavior works for the views, while a dynamic SQL or CLP commands will normally work, using the granted privilege to UNIX group db2user. Modifying the working, production database and removing the PUBLIC grants for some system catalogue views referenced in the static SQL code, could invalidate some database packages and make views inoperative. For this reason a DBA has to clear what to change, before actually changing the default property of the PUBLIC group.

Conclusion

Having enforced various levels of authentication, do we really need to be cautious with PUBLIC? The answer is a clear yes. There can never be enough security. Big Blue has clarified DB2 database as a simple, but the very secure database. So far, there are no plans to implement the security auto-configuration wizards into a self-managed database. When this becomes a reality, it will be a very complicated process and many companies may have a potential problem participating since they will probably need to modify their applications.

» See All Articles by Columnist Marin Komadina