Preventing Public Access to the DB2 Database - Page 2

Database Privileges and User Schema

A database privilege is a user or a group right to create or access the database resource. Privileges are granted on the database level and they are under the control of the DBADM and the SYSADM authority.

A list of privileges, grouped on the database object level:

PRIVILEDGE GROUP	PRIVILEDGES
DATABASE	Createtab,Load,Bindadd,Connect,Implict_Schema,Create_Not_Fenced
TABLE SPACES	Use
SCHEMA	Createin,Alterin,Dropin
TABLES	Control,Alter,Delete,Indeks,Insert,References,Select,Update
VIEWS	Control,All,Alter,Delete,Indeks,Insert,References,Select,Update
INDEXES	Control
PACKAGES	Control,Bind,Execute

A list of privileges, grouped on the security level:

PRIVILEDGE GROUP	PRIVILEDGES
OWNERSHIP	control privilege gives full control over database object
INDUVIDUAL	Select/delete/insert/update privilege allows user to performs special actions on database objects
IMPLICIT	granted to the user or group when the user got some higher level privilege
EXPLICIT	granted directly to the user or group

An ownership privilege (also called CONTROL privilege) gives full privileges for a specific object. Individual privileges may be granted to allow the user to carry out specific functions on specific objects. The users with CONTROL privilege, or administrative authority (SYSADM or DBADM) can grant and revoke the individual privileges. For the privilege granted with GRANT OPTION, the user can further grant privilege to another user. Restriction exists only for the CONTROL privilege for database authorities, package privileges, index privileges, or control privilege on the table or view that cannot be further granted. As the granting procedure is easy to understand and control, revoking (removing granted privilege) is a little bit tricky, since a person with the GRANT OPTION cannot revoke granted privilege without having direct CONTROL privilege on the object.

User schema is the center of user objects. A schema can be created explicitly using the create schema command or implicitly by the DB2 database manger. For example, a user can run the CREATE command, with an intention to create a new database object. The database manager will implicitly create the user schema for the connected user, with a requested object inside.

The owner of all schemas is a generic internal user, SYSIBM. The schema objects are tables, indexes, views, packages, user-define data types, functions, triggers, stored procedures and aliases. Minimal set of privileges, user or group needed to create the table:

1. CONNECT - database privilege to connect to the database
2. CREATEIN - schema privilege to create objects inside schema
3. CREATETAB - database privilege to create table inside the schema

The creator of the table has automatic CONTROL privilege over the table.

The minimal set of privileges, user or group needed to control the package:

1. CONNECT - database privilege to connect to the database

2. CREATEIN - schema privilege to create objects inside schema

3. BINDADD - database privilege to create package

4. BIND - object privilege to rebind existing package

The creator of the package has automatic CONTROL privilege on the package.

A Special Database Group Public

User and group definition in DB2 security are left to the control of the operating system or the external security facility. In addition, DB2 has one special PUBLIC group.

Listing of the user and group privileges, for an empty DB2 database:

db2 => select substr(GRANTOR,1,8) Grantor, substr(GRANTEE,1,8) Grantee, GRANTEETYPE T, \
db2 (cont.) => DBADMAUTH DBA, CREATETABAUTH CRTAB, BINDADDAUTH BIND, CONNECTAUTH CONN, \
db2 (cont.) => NOFENCEAUTH NOFNC,IMPLSCHEMAAUTH IMPLSCH, LOADAUTH LOAD from syscat.dbauth \
db2 (cont.) => where GRANTEE = 'PUBLIC'
 
GRANTOR  GRANTEE  T DBA CRTAB BIND CONN NOFNC IMPLSCH LOAD
-------- -------- - --- ----- ---- ---- ----- ------- ----
SYSIBM   PUBLIC   G N   Y     Y    Y    N     Y       N   
DB2INST1 PUBLIC   G N   Y     Y    Y    N     N       N 
SYSIBM   DB2INST1 U Y   Y     Y    Y    Y     Y       Y

The grantor gives a privilege and the grantee receives a privilege. In the example above, the instance owner (db2inst1) has granted DBADM authority (privilege) on a user level (DBADMAUTH=U), and has granted implicitly all the others database privileges.

The grantee PUBLIC holds Createtab, Bindadd, Connect,Implicit_Schema database privileges and Select privilege on the system catalog views. This privilege has been granted to PUBLIC during database creation.

Note that instance owner db2inst1 had granted to PUBLIC the same privilege set as user SYSIBM, only without Implict_Schema database privilege. The effective group rights for PUBLIC will be a combination of both, the PUBLIC group will hold Implicit_Schema database privilege.

The Implicit_schema database privilege provides that any user can create a schema implicitly by creating an object using the CREATE statement with a new, nonexistent schema name. SYSIBM becomes the owner of the implicitly created schema and PUBLIC is given the privilege to create objects in this schema. To remove any privilege from the PUBLIC, SYSIBM or DBADM, the privilege must be explicitly revoked.

The PUBLIC group has by default, the select right to 151 system views in the version 7.1.

The user SYSIBM granted implicit privileges, GRANTEETYPE of G to the PUBLIC group, meaning that PUBLIC will receive granted privileges and can grant to the others.

Directly granted (explicit) privileges, granted to the user are shown as GRANTEETYPE of U, meaning that the user received the privilege to the database object without the ability to grant to the others.

All users on the machine, by default belong to the PUBLIC group. Using that, any user can connect (Connect) to the database, create a table (Createtab), or execute the package bind (Bindadd) command.

Additionally, PUBLIC has implicitly granted SELECT right on the system catalog tables. Every member of the PUBLIC group has all privileges granted to the group.

The Public Group Research

For our test environment, we have a UNIX server, with Sun Solaris 2.7 operating system and DB2 UDB EEE database, version 7.1.

The following groups and users relevant to the DB2 database exist on the operating system:

$ cat /etc/passwd
db2fenc1:x:59555:103::/export/home/db2fenc1:/bin/ksh
db2inst1:x:59556:102::/export/home/db2inst1:/bin/ksh

$ cat /etc/group
db2fadm1::103:
db2iadm1::102:

User db2inst1 is the instance owner, and d2fenc1 is the default fenced DB2 user.

We are going to attempt to connect a nonexistent user "ARTIST" to the local ARTIST DB2 database, using a local UNIX console:

# db2 connect to artist user artist using artist

SQL1403N  The username and/or password supplied is incorrect.  SQLSTATE=08004

We cannot connect since user does not exist. Next, we will create the group db2user for regular users, and a new user with the name ARTIST and password xxx123.

   # groupadd db2user
   # useradd -g db2user -G db2user -d /home/artist artist
   # passwd artist 

The situation on the operating system side now looks like this:

$ cat /etc/group
db2fadm1::103:
db2iadm1::102:
db2user::104:

$ cat /etc/passwd
db2fenc1:x:59555:103::/home/db2fenc1:/bin/ksh
db2inst1:x:59556:102::/home/db2inst1:/bin/ksh
artist:x:40000:104::/home/artist:/bin/ksh

We are ready to connect to the Unix machine with the newly created user ARTIST for the first time.

# su - artist
Password:
$ id 
uid=40000(artist) gid=5555(db2user)groups=104(db2user) 

We will set the normal DB2 profile for user ARTIST, with a path to the DB2 commands and then try to connect to the DB2 database.

$ db2ilist
artist

$ 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 =>  LIST DATABASE DIRECTORY
 
 System Database Directory
 
 Number of entries in the directory = 1
 
Database 1 entry:
 
 Database alias                  = ARTIST
 Database name                   = ARTIST
 Local database directory        = /data/artist/node0/artist
 Database release level          = 9.00
 Comment                         = ARTIST test database 
 Directory entry type            = Indirect
 Catalog node number             = 0

Viewing a table definition for the schema ARTIST:

db2 => list tables for schema artist

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------
  0 record(s) selected.

The user ARTIST has no defined objects. Because the PUBLIC group is enabled, the user ARTIST will have all of the rights belonging to PUBLIC. He can connect, and select from the database catalog views:

db2 => select count(*) from syscat.tables

1
-----------
        151

  1 record(s) selected.

The Actual authorities and database level privileges for the user ARTIST are as follows:

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           = YES
 Indirect CONNECT authority           = YES
 Indirect CREATE_NOT_FENC authority   = NO
 Indirect IMPLICIT_SCHEMA authority   = YES
 Indirect LOAD authority              = NO

The user has the CREATETAB, BINDADD, CONNECT privileges and IMPLICT_SCHEMA authority.

db2 => list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- ------------------
  0 record(s) selected.

The user has no tables and the user schema does not exist on the system.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1

We are going to create a new schema for the user ARTIST:

db2 => create schema artist authorization xxx123
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 "CREATE
SCHEMA".  SQLSTATE=42502

User ARTIST cannot create a schema object, but can create a table. A newly created table will be created in his new implicitly created schema:

db2 => CREATE TABLE test (name varchar(10) NOT NULL,PRIMARY KEY(name))
DB20000I  The SQL command completed successfully.
db2 => list tables
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TEST                            ARTIST          T     2003-09-06-17.20.17.396000
  1 record(s) selected.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1
ARTIST     SYSIBM     ARTIST  > new schema implicitly created

The new schema implicitly created for the user ARTIST exists in the database.

We saw that the user could not explicitly create a schema, however, by creating a table he will succeed in implicitly creating a database schema. The user can also create a table in another, new schema. For example:

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

The operation is successful. We have succeeded to create implicitly a new database schema using PUBLIC grants.

db2 => select schemaname,owner, definer from syscat.schemata

SCHEMA     OWNER      DEFINER
---------- ---------- ----------
SYSIBM     SYSIBM     SYSIBM
SYSCAT     SYSIBM     SYSIBM
SYSFUN     SYSIBM     SYSIBM
SYSSTAT    SYSIBM     SYSIBM
NULLID     SYSIBM     DB2INST1
DB2INST1   SYSIBM     DB2INST1
VIP333     DB2INST1   DB2INST1
ARTIST     SYSIBM     ARTIST  > new schema explicitly created
NOONE      SYSIBM     ARTIST  > new schema implicitly created

The user NOONE does not exist on the operating system but we have succeeded in creating the additional schema with a new object in that schema.

The user ARTIST has the ability to change his current schema to the schema of another user, or list tables from another schema:

db2 => set schema vip333
DB20000I  The SQL command completed successfully.

db2 => list tables for schema vip333

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- ----------------------
PROTO                           VIP333           T     2001-05-08-21.33.31.886865
WELLA                           VIP333           T     2001-05-08-21.33.32.246662


db2 => select * from vip333.wella
SQL0551N  "ARTSIT" does not have the privilege to perform operation "SELECT" on
object "VIP333.WELLA".  SQLSTATE=42501

The user cannot look inside the other data, but he can select information from the catalog tables regarding other database users. Sometimes, just reading activity for a user from the catalog tables can be very valuable information. This is a situation we would like to avoid.

PUBLIC is just public. It was created with the intention of helping everyone, to provide as much database information as possible and to focus end users on business logic. It is like good buddy, keeping a door open for visitors. However, do we want visitors to walk in and out of a business critical, high cost database, showing only a badge given by system administrators? We cannot stop them, but we can limit their view.

» See All Articles by Columnist Marin Komadina