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
» 
Comment and Contribute