Preventing Public Access to the DB2 Database - Part 2

Thursday Oct 9th 2003 by Marin Komadina
Share:

Concerned about who is walking unchallenged through the Public group door into your database? Marin Komadina concludes his examination of the security aspects of the DB2 database default PUBLIC group.

Applying Security Restrictions to the Public

The first recommendation for the PUBLIC is to revoke an IMPLICIT_SCHEMA privilege to protect flooding of the user schemas. This will prevent an unknown user from creating a schema, but still allow regular users to create a private schema with the same name as the name of user on the operating system.

The first step, we are going to revoke the IMPLICT_SCHEMA privilege from the PUBLIC group:

db2 => revoke connect,createtab,bindadd,implict_schema on database from public
DB20000I  The SQL command completed successfully.

The user ARTIST is still connected to the database. His current security settings are:

db2 => get authorizations

 Administrative Authorizations for Current User

 Direct SYSADM authority              = NO
 Direct SYSCTRL authority             = NO
 Direct SYSMAINT authority            = NO
 Direct DBADM authority               = NO
 Direct CREATETAB authority           = NO
 Direct BINDADD authority             = NO
 Direct CONNECT authority             = NO
 Direct CREATE_NOT_FENC authority     = NO
 Direct IMPLICIT_SCHEMA authority     = NO
 Direct LOAD authority                = NO

 Indirect SYSADM authority            = NO
 Indirect SYSCTRL authority           = NO
 Indirect SYSMAINT authority          = NO
 Indirect DBADM authority             = NO
 Indirect CREATETAB authority         = NO
 Indirect BINDADD authority           = NO
 Indirect CONNECT authority           = NO
 Indirect CREATE_NOT_FENC authority   = NO
 Indirect IMPLICIT_SCHEMA authority   = N0
 Indirect LOAD authority              = NO

We did not terminate the user connection, so that we can look at the user ARTIST authorization changes. Terminating the session and making a new connect will result with the following error:

$ db2 connect to artist user artist using xxx123
SQL1060N  User "ARTIST   " does not have the CONNECT privilege.  SQLSTATE=08004

The user cannot connect any more, and he has no privileges. This is the first successful step in limiting the user's access to the database. In the next step, we are going to delete the old user schema from the database. This was the user schema ARTIST and NOONE--created using IMPLICT_SCHEMA authority, granted over PUBLIC database group. Before deleting the schema object, the schema has to be clean of database objects.

db2 => drop schema artist restrict
DB20000I  The SQL command completed successfully.
db2 => drop schema noone restrict
DB20000I  The SQL command completed successfully.

We can now use the operating system group db2user, and grant the necessary rights on the group level or we can make the grant directly to the user ARTIST. If we were to make the grant to the group, all users of the group including our test user, ARTIST, would have the same rights implicitly. In this test, we will use group db2user, whose member is the user ARTIST, and make the changes with the instance owner, user db2inst1:

db2 => grant connect, createtab on database to group db2user

A new connection with the user ARTIST is possible, with the following authorizations:

$ db2 connect to artist user artist using xxx123

   Database Connection Information

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

db2 => get authorizations

 Administrative Authorizations for Current User

 Direct SYSADM authority              = NO    
 Direct SYSCTRL authority             = NO
 Direct SYSMAINT authority            = NO
 Direct DBADM authority               = NO
 Direct CREATETAB authority           = NO
 Direct BINDADD authority             = NO
 Direct CONNECT authority             = NO
 Direct CREATE_NOT_FENC authority     = NO
 Direct IMPLICIT_SCHEMA authority     = NO
 Direct LOAD authority                = NO

 Indirect SYSADM authority            = NO 
 Indirect SYSCTRL authority           = NO
 Indirect SYSMAINT authority          = NO
 Indirect DBADM authority             = NO
 Indirect CREATETAB authority         = YES
 Indirect BINDADD authority           = NO
 Indirect CONNECT authority           = YES
 Indirect CREATE_NOT_FENC authority   = NO
 Indirect IMPLICIT_SCHEMA authority   = NO
 Indirect LOAD authority              = NO

When we attempt to create a table in the nonexistent private schema or some other user schema, we get an error, since the user ARTIST no longer has IMPLICIT_SCHEMA authority.

db2 => CREATE TABLE test (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0552N  "ARTIST" does not have the privilege to perform operation "IMPLICIT
CREATE SCHEMA".  SQLSTATE=42502

The user has a right to connect but still cannot create objects in the database. Using the instance owner, we will explicitly create the user schema, and grant to the user ARTIST the privilege to create objects in his schema.

db2 => create schema artist 
DB20000I  The SQL command completed successfully.

The user can now create objects, but only in the local schema. The user has full control over the created objects, and the user cannot create new schemas or objects in another user schema.

$ db2 connect to artist user artist using xxx123

db2 => CREATE TABLE test (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB20000I  The SQL command completed successfully.

Every new schema will be created by the SYSADM authority and the user attempts to create a new table in some other user schema will fail.

db2 => create table vip333.COPY (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0551N  "ARTIST" does not have the privilege to perform operation "CREATE
TABLE" on object "VIP333.COPY".  SQLSTATE=42501

A select right on the system catalogues, given to the user ARTIST over the PUBLIC group is still active. The user can select from as many catalogue tables such as SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, or SYSCAT.SCHEMAAUTH that was implicitly granted over the PUBLIC group, but never explicitly revoked from user ARTIST. At minimum, we should restrict the user access to several main system catalogue views using the following syntax:

db2 => revoke select on syscat.dbauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.packageauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.indexauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.colauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.passthruauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.schemaauth from public
DB20000I  The SQL command completed successfully.
db2 => revoke select on syscat.tabauth from public
DB20000I  The SQL command completed successfully.

Further restriction has to be tailored according to individual company regulation standards.

Note: Wouldn't it be nice to restrict access to the catalog tables with the statistics columns. Some of this statistics information might be sensitive information regarding a customer's daily activity. In that case, a DBA could revoke a select privilege from the SYSCAT.COLUMNS and SYSCAT.COLDIST views from the PUBLIC group.

Another solution would be to disable completely the PUBLIC group and to create private views for all the necessary catalog objects.

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved