Database Strategies: Using Informix XPS and DB2 Universal Database

Chapter 4. Instance and database operations 129

4.6.3 Security levels

Both XPS and DB2 apply security at different levels on different database

objects. You can have specific privileges on tables and other specific privileges

on the columns of that table. At database level, DB2 maintains security as

authorities. The authorities is applicable to the entire database rather than

privileges that apply to specific objects within the database.

As with XPS, GRANT and REVOKE SQL statements are used in DB2 to assign

authorities and privileges to a user. The following is a list of those authorities and

privileges, with their description:

򐂰 Default privileges

DB2 does not grant default privileges the way that XPS does. If you grant

database CONNECT privilege to a user, the user does not have permission

automatically to query any table in the database. All subsequent privileges

have to be configured manually. You will find that DB2 is more restrictive in

this area than XPS.

򐂰 Database authorities

The syntax to administer database authorities in DB2 (similar to database

level privileges in XPS) is slightly different than in XPS. The syntax to grant

database authorities is depicted in Figure 4-7.

Figure 4-7 Database authorities syntax

130 Database Strategies: Using Informix XPS and DB2 Universal Database

Example 4-18 illustrates the Grant and Revoke commands.

Example 4-18 Granting and revoking permissions

GRANT CONNECT ON DATABASE TO mark;

REVOKE CONNECT ON DATABASE FROM nora;

DB2 offers more specific privileges than XPS. Some of these additional

privileges are not covered here because XPS has nothing comparable, so they

are therefore not relevant to a discussion on transitioning.

Figure 4-8 provides an overview of the authorities and privileges and how to

relate.

Figure 4-8 Privileges overview

The following is a list of the applicable authorities and privileges and their

descriptions:

򐂰 BINDADD

Grants the authority to create packages. The creator of a package

automatically has the CONTROL privilege on that package and retains this

privilege even if the BINDADD authority is subsequently revoked.

򐂰 CONNECT

Grants the authority to access the database.

SYSADM

SYSCTRL

SYSMON

SYSMAINT

TABLESPACE

USE

DATABASE

DBADM

CONNECT

IMPLICIT_SCHEMA

BINDADD

CREATETAB

CREATE_NOT_FENCED

LOAD

SCHEMA

CREATEIN

ALTERIN

DROPIN

PACKAGE

CONTROL

EXECUTE

BIND

TABLE (VIEW)

CONTROL

ALL

ALTER

INSERT

UPDATE

SELECT

CONTROL

INDEX

REFERENCES

SEQUENCE

USE

ROUTINE

EXECUTE

Chapter 4. Instance and database operations 131

򐂰 CREATETAB

Grants the authority to create base tables. The creator of a base table

automatically has the CONTROL privilege on that table. The creator retains

this privilege even if the CREATETAB authority is subsequently revoked.

There is no explicit authority required for view creation. A view can be created

at any time if the authorization ID of the statement used to create the view has

either CONTROL or SELECT privilege on each base table of the view.

򐂰 CREATE_EXTERNAL_ROUTINE

Grants the authority to register external routines. Care must be taken that

routines so registered will not have adverse side effects. (For more

information, see the description of the THREADSAFE clause on the CREATE

or ALTER routine statements.) When an external routine has been registered,

it continues to exist, even if CREATE_EXTERNAL_ROUTINE is

subsequently revoked.

򐂰 CREATE_NOT_FENCED_ROUTINE

Grants the authority to register routines that execute in the database

manager’s process. Care must be taken that routines so registered will not

have adverse side effects. (For more information, see the description of the

FENCED clause on the CREATE or ALTER routine statements.) When a

routine has been registered as not fenced, it continues to run in this manner,

even if CREATE_NOT_FENCED_ROUTINE is subsequently revoked.

CREATE_EXTERNAL_ROUTINE is automatically granted to an

authorization-name that is granted CREATE_NOT_FENCED_ROUTINE

authority.

򐂰 DBADM

Grants the database administrator authority and all other database

authorities. A database administrator has all privileges against all objects in

the database and can grant these privileges to others.

򐂰 IMPLICIT_SCHEMA

Grants the authority to implicitly create a schema.

򐂰 LOAD

Grants the authority to load in this database. This authority gives a user the

right to use the LOAD utility in this database. SYSADM and DBADM also

have this authority by default. However, if a user only has LOAD authority (not

SYSADM or DBADM), the user is also required to have table-level privileges.

Note: All other database authorities are granted to an authorization-name

implicitly and automatically that is granted DBADM authority.