Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Oct 9, 2003

Preventing Public Access to the DB2 Database - Part 2

By Marin Komadina

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.



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.