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.