Native DB2 Security › What Are DB2 Authorities and Privileges? › Explicit Assignment

Explicit Assignment

You can explicity grant privileges to users with the SQL GRANT statement.

The REVOKE statement lets you remove privileges that have been specifically granted.

Using the WITH GRANT OPTION clause, which is discussed later in this chapter, the GRANT statement allows an ID to pass on the granted privilege to others. If the privilege is revoked from the ID, its deletion can cascade to others, with side effects that are not immediately obvious. For example, when a privilege is revoked from authorization ID Ted, it is also revoked from any ID to which Ted granted it, unless that ID also has the privilege from some other source.

Note: You can use the GRANT or REVOKE statement only if authorization checking was enabled when DB2 was installed.

You can grant and revoke privileges to and from a single ID, or you can name several IDs on one statement. You can also grant privileges to the ID called PUBLIC, which makes the privileges available to all IDs. In CA Top Secret Option for DB2 nomenclature, PUBLIC is analogous to the ALL Record.

PUBLIC is a special identifier used by DB2 internally and it should not be used as a primary or secondary authorization ID. When a privilege is revoked from PUBLIC, authorization IDs that were specifically granted that privilege will still retain the privilege.

You can grant a specific privilege on one object in a single statement, a list of privileges, or privileges over a list of objects.

GRANT Statement Syntax

The GRANT statement has the following syntax:

GRANT privilege [ON object] {TO authorization‑ID} [WITH GRANT OPTION]
                                 {   PUBLIC          }

where:

GRANT

The SQL statement that grants privileges to users.

Note: The clause USE OF is added to the GRANT statement for granting privileges to Buffer Pools, Storage Groups, and Table Spaces.

privilege

Names one or more privileges for the named objects or authorities.

ON object

Used with particular objects, such as: Database, Plan, and Table.

TO

Indicates to whom the privileges are granted.

authorization‑ID

Lists one or more authorization IDS. You cannot use the ID of the GRANT statement itself. In other words, you cannot grant privileges to yourself.

PUBLIC

Grants the privileges to all users.

WITH GRANT OPTION

Allows the named users to grant the privileges to others. Granting an administrative authority with this option allows the user to specifically grant any privilege belonging to that authority. PUBLIC and WITH GRANT OPTION are mutually exclusive terms. If you use both terms, the privileges are granted to PUBLIC without GRANT authority.

Examples

The following examples illustrate some of the variances that can exist within the syntax for the GRANT statement.

• Grant the SYSADM privilege to user Mike

  GRANT SYSADM TO MIKE
  

  The SYSADM privilege automatically gives user Mike all privileges and the ability to grant them. Using the WITH GRANT OPTION in this cases is valid but unnecessary.

• Grant UPDATE privileges on columns EMPNO and DEPT on table DSN1000.EMP to all users.

  GRANT UPDATE (EMPNO, DEPT) ON TABLE DSN1000.EMP TO PUBLIC
  

• Grant the authority to use buffer pools BP0 and BP1 to users Jim and Dan.

  GRANT USE OF BUFFERPOOL BP0,BP1 TO JIM,DAN
  

Implicit Assignment

With the exception of plans and packages, you create DB2 objects by SQL CREATE statements in which you give the object a name. Establishing ownership of objects can be accomplished with qualified or unqualified names. For more information about creating objects, see the IBM DB2 Administration Guide.

Ownership of an object carries certain privileges implicitly. An ID that owns an object has all privileges for the object, and can grant them to others.

As long as an object exists, there is no way to change the owner or revoke the privileges implicit in ownership. The only thing to be done is to drop the object. You can then recreate it with a new owner.

The implications of dropping the object and recreating a new owner include the following:

• All privileges are dropped.
• All data contained within the object is lost.
• Any views associated with the object are dropped.
• Application plans are invalidated.
• Indexes are dropped.
• Authorization information is updated.

However, you can share the privileges of ownership. This can be accomplished by making the owning ID a secondary ID to which several primary authorization IDs are connected. The list of primary IDs connected to the secondary ID can be changed without having to drop and recreate the object.

Packages and Plans

DB2 uses application packages and plans to communicate requests to the database manager.

The BIND and REBIND commands create and change application plans and packages. Before you create a plan or package, the owner of the plan or package must possess the privileges needed to use all SQL statements in the program. Used alone, the BIND command gives your primary ID ownership of the newly created object. However, you can also use the OWNER option on the BIND or REBIND command to specify one of your secondary IDs as the owner. Users with system DBADM, SYSCTRL, or SYSADM authority can specify any authorization ID as owner of the plan or package.

DB2 lets you change ownership of a plan or package using the OWNER option on the REBIND command. Although the former owner no longer owns the plan or package, he retains the BIND and EXECUTE privileges with the WITH GRANT OPTION on the rebound object. Not even someone with SYSADM authority can revoke these privileges. The only way to change the implicit privileges of an owner is to drop the object.

Use of Views

In native DB2, you can also use views to restrict a user’s access to a table. A view is a subset of selected columns and rows from a table. Users with the SELECT privilege on a view can see only the information that you define. You can also restrict a user's ability to update only named columns in a table.