Preventing Public Access to the DB2 Database – Part 2

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.

Marin Komadina
Marin Komadina
Marin was born June 27, 1968 in Zagreb, Croatia. He graduated in 1993 form The Faculty for Electrotechnology and Computer Sciences, University of Zagreb in Croatia. He started his professional career as a System specialist and DBA for the Croatian company Informatika System. His most important project was the development and implementation of the enterprise, distributed point of sales solution, based on the Oracle technology. In 1999, Marin became the company CTO, where he played an active role in company development and technical orientation. After Informatika System, Marin worked as an IT Manager Assistant for the Austrian international retail company "Segro," on location in Graz (Austria) and Zagreb (Croatia). He was responsible for the company's technical infrastructure and operational support. Segro used IBM technology, OS/400 operating system and DB2 database. In 1998, Marin joined the international telecommunication company VIPNet GSM that was a part of greater concern, Mobilkom Austria& Western Wireless Int. USA. After one year, Marin took over the IT System Manager position, where he managed many multi-platform, telecommunication projects and was leading the IT system department. In 2001, Marin started to work in Germany as a senior system architect. He is currently working for German banks on different banking projects.

Latest Articles