DB2 security, Part 7: Object ownership management for DB2 9

Just like managing authorities and privileges in a database, managing database object ownership can be problematic if the company has a high turnover. This article introduces a new IBM® DB2® 9 feature that lets you transfer ownership, and an orphan ID concept to help you to cope with the object ownership management nightmare.

Share:

Kevin Yeung-Kuen See (see@ca.ibm.com), Software Developer, IBM

Kevin See photoKevin See, CISSP, has been a Software Developer at the IBM Toronto Laboratory for the past decade. His experiences include working in the DB2 Security Development team and the DB2 SQL and Catalog Development team. He is an IBM Certified Solutions Developer for XML and Related Technologies and a DB2 Certified Solutions Expert (DBA for OS390, DBA for Linux/UNIX/Windows, Advanced DBA for Linux/UNIX/Windows and DB2 Family Application Development). He is also an ISC2 Certified Information Systems Security Professional. He has written two IBM Developer Works security articles and is co-authoring an upcoming book about DB2 security.



24 August 2006

Introduction

Transfer ownership is a new DB2 9 feature where the ownership of a database object can transfer to another authorization ID (authid). There are two general usages for this feature. The first usage is when the owner of an SQL object leaves a company, the ownership of the object can transfer to another authid that has sufficient privileges to keep the object existence by issuing the transfer ownership SQL statement. It is also embedded in the new DB2 9 built-in function sysproc.admin_copy_schema. This new built-in function provides the ability to copy an arbitrary source schema (which may be different from the current schema) to an arbitrary target schema with or without data, and an option to transfer ownership of all new objects. If the option not specified, the owner of the target objects is the connected user. If a new owner is specified, ownership should be transferred to the new owner.

In order to transfer the ownership of an object to the new owner, the new owner must possess sufficient privileges on the underlying object. This article introduces the concept of transferring ownership and the orphan ID approach. It explains how the privilege of the underlying objects are checked so that the readers can understand what privileges need to be granted to the new owner before the new owner can accept the ownership of an SQL object.

Object ownership consideration

Depending on the individual organization, an employee or end user can move around frequently, which creates a nightmare of authority and privilege management. Furthermore, the departing end user might be an owner for a particular SQL object. Revoking privileges or authority from this user might cause those SQL objects to be invalid or dropped, which might not be a desirable behavior. There are two approaches where you can minimize the impact of such changes.

Orphan authorization ID approach

The orphan authorization ID approach is one way to handle the object ownership issue, by attacking the issue at the beginning before the object is created. The steps are as follows:

  1. Create a DB2 authorization ID that cannot be used for a connection to the database. That is, it does not have access to the CONNECT privilege directly or indirectly (for example, PUBLIC), or which is locked in the authentication mechanism (for example, an invalid or undefined user ID). You can either not define it on the operating system, Lightweight Directory Access Protocol (LDAP) server, or Kerberos if you are using one of the IBM shipped security plug-ins, or code the blocking logic in your customized security plug-in. Alternatively, just select an authorization ID with lower case characters since no user can be mapped to such an authorization ID because the authentication process always uppercases the authorization ID returned by the security plug-in.
  2. Grant the required privileges directly to this authorization ID so that it can create and own SQL objects.
  3. Grant the SETSESSIONUSER privilege to the user who will be issuing the bind command or create statements.

    Note: Only SECADM can grant this privilege, DBADM cannot grant this privilege.

  4. Use the SET SESSION AUTHORIZATION statement to have users switch to become the orphan authorization ID within DB2. Also, issue the required statements to create the objects desired, or bind the package and grant the ability to access and use those objects to other users or groups.
  5. Revoke the SETSESSIONUSER privilege from the user.

Transfer ownership

When an authorized user creates an SQL object, such as a view, the authorization ID of the user is marked as the definer of the object. An owner of an SQL object holds the ownership of the object and has the responsibility of maintaining the object.

Prior to DB2 9, most of the objects have only definer in the corresponding system catalog table or view. As of DB2 9, a new owner field was added in the SYSCAT views for all SQL objects. The definer and owner are initially set to the same user authorization ID that defines the objects. The definer has the ownership of the object until the object's ownership is transferred to another user authorization ID using the transfer ownership SQL statement.

BUFFERPOOL, SERVER, TRANSFORM, USER MAPPING, and WRAPPER objects cannot be transferred to a new owner because the system catalog table does not record the definer or owner of these objects. Objects owned by the system (such as, SYSIBM), including any routines that have the schema of SYSIBM, SYSFUN, or SYSPROC, are restricted from being transferred. An attempt to transfer system owned objects causes an sqlcode -607 to be returned to the user. There are the following six additional restrictions related to the transfer of ownership where an attempt to transfer results in sqlcode -20344 with the reason code:

  1. Subtables in a table hierarchy cannot be transferred. You need to transfer the whole table hierarchy.
  2. Subviews in a view hierarchy cannot be transferred. You need to transfer the whole view hierarchy.
  3. Indexes defined on a declared global temporary table cannot be transferred.
  4. Methods and functions that are generated by the system during create type cannot be transferred. The system generated methods and functions can only be transferred as part of the transfer of ownership of the data type. The new owner of the data type inherits the ownership of the system generated methods and functions for that data type.
  5. A package associated with a persistent stored module (PSM) (SQL) stored procedure cannot be transferred. It is transferred to the new owner of the PSM stored procedure implicitly when the PSM stored procedure is transferred.
  6. An active event monitor cannot be transferred. You need to disable the event monitor before attempting the transfer.

Security administrators (SECADMs), or the original definer or owner of the object, can perform a transfer of ownership of the given object. However, the security administrator cannot transfer an object to himself or herself, otherwise sqlcode -20379 is returned. If security administrators want to transfer ownership to themselves, they need to transfer to someone else and have that person transfer to them in a second sequence. But this temporary owner does not have to be a SECADM. The other way is to have someone else, another SECADM, transfer the object to the first SECADM. This approach removes the intermediary owner.

In order to transfer the ownership of an object to a new owner, the new owner must possess sufficient privileges on the underlying object. SQL objects, such as aliases, constraints (including primary key, foreign key, and check), database partition groups, event monitors, function mapping, nicknames, schemas (implicit and explicit created), tablespaces, data types (structured type and distinct type), and type mapping, do not reference other SQL objects. Therefore, there is no underlying object. As a result, the new owner does not require any privileges to accept the ownership of these objects.

After the object is transferred to the new owner, the schema of the object remains the same. The original owner or definer still has his or her existing privileges on the transferred objects preserved. For example, if the user zurbie created a view ZURBIE.V1, and a SECADM transferred the ownership of the view ZURBIE.V1 to user einstein. Both user zurbie and einstein have the same set of privileges granted on ZURBIE.V1, but Einstein is the owner. Note the schema for V1 remains as ZURBIE. If the old owner had a privilege revoked on the object, and then it was granted back, this privilege would not be transferred. Say a DBADM revoked control on ZURBIE.V1 from zurbie and then it was granted back to zurbie just before the transfer. The new owner, einstein, does not possess the control privilege on the view ZURBIE.V1. The set of privileges that was implicitly granted to the definer or owner (which has the GRANTEE of SYSIBM) is duplicated for the new user.

System generated routines created as part of the create type (structured) transfer the ownership to the new owner of the type if a transfer of ownership of the dependent type is performed.

The following table shows you where you can find all the underlying objects and the required privileges.

DATABASE OBJECTSCATALOG VIEWS
CONSTRAINTSYSCAT.CONSTDEP
FUNCTIONSYSCAT.ROUTINEDEP and SYSCAT.ROUTINES (For a sourced function)
INDEXSYSCAT.INDEXDEP
INDEX EXTENSIONSYSCAT.INDEXEXTENSIONDEP
METHODSYSCAT.ROUTINEDEP
PACKAGESYSCAT.PACKAGEDEP
PROCEDURESYSCAT.ROUTINEDEP
TABLESYSCAT.TABDEP
TRIGGERSYSCAT.TRIGDEP
VIEWSYSCAT.VIEWDEP
XSROBJECTSYSCAT.XSROBJECTDEP

The importance of the value recorded in the tabauth field in the dependency catalog view

Generally speaking, you only need the minimum privilege set to maintain the existence of an object to become the new owner, as long as the underlying object does not include a table or view where the privilege is recorded in the tabauth field in the system catalog views. Therefore, the new owner never requires use privileges on the tablespace in order to accept the ownership of a table. For objects that have references to a table or view, the tabauth is the key to determining what privileges the new owner requires.

To interpret what privileges are required, you must first convert the decimal value in the tabauth field to the hexadecimal format. Then, map the hexadecimal value with the following list of privileges in hexadecimal format to find all the required privileges. The list can also be found in SQL.H, a shipped header file that can be found under the sqllib/include directory.

Listing 1. Extract from sql.h
#define SQL_TAB_CTL            0x1     /* Control Authority                   */
#define SQL_TAB_ALT            0x2     /* Alter Privilege                     */
#define SQL_TAB_DEL            0x4     /* Delete Privilege/Dependency         */
#define SQL_TAB_IDX            0x8     /* Index Privilege                     */
#define SQL_TAB_INS            0x10    /* Insert Privilege/Dependency         */
#define SQL_TAB_SEL            0x20    /* Select Privilege/Dependency         */
#define SQL_TAB_UPD            0x40    /* Update Privilege/Dependency         */
#define SQL_TAB_REF            0x80    /* Reference Privilege                 */

#define SQL_TAB_ALT_G          0x200   /* Alter Privilege Grantable           */
#define SQL_TAB_DEL_G          0x400   /* Delete Privilege Grantable          */
#define SQL_TAB_IDX_G          0x800   /* Index Privilege Grantable           */
#define SQL_TAB_INS_G          0x1000  /* Insert Privilege Grantable          */
#define SQL_TAB_SEL_G          0x2000  /* Select Privilege Grantable          */
#define SQL_TAB_UPD_G          0x4000  /* Update Privilege Grantable          */
#define SQL_TAB_REF_G          0x8000  /* References Privilege Grantable      */

Say the tabauth shows a decimal value of 29813. This is x7475 in hex format. According to Listing 1, it indicates that you require insert with the grant privilege, select with the grant privilege, update with the grant privilege, delete with the grant privilege, and the control privilege on the underlying table in order to accept the ownership of the object.

All three scenarios of ownership transfer below assume that the database is not created with the new restrictive option.

Example 1: Minimum privilege set
User issuing the SQL statementSQL StatementOutput
user2 Create function sourced1 (x integer) specific sourced1 returns integer source sysfun.abs (integer)

Grant execute on specific function user2.sourced1 to user zurbie with grant option

Grant execute on specific function user2.sourced1 to user boss
All SQL statements return sqlcode 0, indicating a successful operation.
zurbie Create table mytable (x integer, constraint check1 check (user2.sourced1(x) > 0)) The CREATE TABLE statement returns sqlcode 0, indicating a successful operation.
newton (System administrator, SYSADM) Grant secadm on database to user user3 The GRANT statement returns sqlcode 0 indicating successful operation.
User3 Select tabschema, tabname, constname, owner from syscat.checks where constname = 'CHECK1' and tabschema = 'ZURBIE' and tabname = 'MYTABLE' Owner is zurbie.
User3 Select * from syscat.constdep where constname = 'CHECK1' and tabschema = 'ZURBIE' and tabname = 'MYTABLE' 1 row:
BTYPE = "F", BSCHEMA= "USER2", BNAME="SOURCED1"
User3 Transfer ownership of constraint zurbie.mytable.check1 to user boss The TRANSFER OWNERSHIP statement returns sqlcode 0, indicating a successful operation.
User3 Select tabschema, tabname, constname, owner from syscat.checks where constname = 'CHECK1' and tabschema = 'ZURBIE' and tabname = 'MYTABLE' The owner has changed to BOSS, despite the fact that BOSS only has execute privileges instead of execute with the grant option. But execute privileges on USER2.SORUCED1 is the minimum required privilege to keep the check constraint valid.

Example 2: Tabauth is involved where the original definer is not a DBADM

When a SQL object is created, the required privilege for the underlying table or view is recorded into the tabauth field that contains an internal code indicating the required privilege to DB2. Transferring ownership relies on this tabauth field despite the fact that it might not be the minimum privilege, as shown in Example 1. The new owner must have all the privileges encoded in the tabauth field of the dependency catalog for that object.

User issuing the SQL statementSQL StatementOutput
user2 create table user2.table_regular (i1 int not null)

Grant select on table user2.table_regular to user zurbie with grant option

Grant select on table user2.table_regular to user boss with grant option

Grant select on table user2.table_regular to user einstein

All SQL statements return sqlcode 0, indicating a successful operation.
Zurbie create view view_regular as (select * from user2.table_regular) The CREATE VIEW statement returns sqlcode 0, indicating a successful operation.
newton (System administrator, SYSADM) Grant secadm on database to user user3 The GRANT statement returns sqlcode 0, indicating a successful operation.
User3 Select viewschema, viewname, owner from syscat.views where viewschema = 'ZURBIE' and viewname = 'VIEW_REGULAR' Owner is zurbie.
User3 Select * from syscat.viewdep where viewschema = 'ZURBIE' and viewname = 'VIEW_REGULAR' 1 row:
BTYPE = "T", BSCHEMA= "USER2", BNAME="TABLE_REGULAR", DEFINER = "ZURBIE", TABAUTH = "8224", which is hex value 2020, which internally means select privilege with the "with grant option."
User3 Transfer ownership of view zurbie.view_regular to user einstein The TRANSFER OWNERSHIP statement failed with sqlcode -20342, indicating it also required the with grant option on the SELECT privilege on the underlying table user2.table_regular.
User3 Select viewschema, viewname, owner from syscat.views where viewschema = 'ZURBIE' and viewname = 'VIEW_REGULAR' Owner remains as zurbie.
User3 Transfer ownership of view zurbie.view_regular to user boss The TRANSFER OWNERSHIP statement returns sqlcode 0, indicating a successful operation.
User3 Select viewschema, viewname, owner from syscat.views where viewschema = 'ZURBIE' and viewname = 'VIEW_REGULAR' The owner has changed to BOSS because BOSS has the SELECT with grant option, despite the fact that just having SELECT should be sufficient for maintaining the validity of the view.

Example 3: Tabauth is involved where the original definer is a DBADM or SYSADM

When a SYSADM or DBADM creates an SQL object that references a table or a view object, the tabauth field is set to 0 to indicate that it was created by SYSADM or DBADM. But in the context of transferring ownership, there is no way to determine what the required privileges are from the value 0. Therefore, no privilege is required to accept the ownership of a view that is created originally by DBADM or SYSADM.

User issuing the SQL statementSQL StatementOutput
user2create table user2.table_regular (i1 int not null) All SQL statements return sqlcode 0, indicating a successful operation.
newton (System administrator, SYSADM)create view view_regular as (select * from user2.table_regular) The CREATE VIEW statement returns sqlcode 0, indicating a successful operation.
newton (System administrator, SYSADM)Grant secadm on database to user user3 The GRANT statement returns sqlcode 0, indicating a successful operation.
User3Select viewschema, viewname, owner from syscat.views where viewschema = 'NEWTON' and viewname = 'VIEW_REGULAR' Owner is newton.
User3 Select * from syscat.viewdep where viewschema = 'NEWTON' and viewname = 'VIEW_REGULAR' 1 row:
BTYPE = "T", BSCHEMA= "USER2", BNAME="TABLE_REGULAR", DEFINER = "ZURBIE", TABAUTH = "0"
User3Transfer ownership of view zurbie.view_regular to user boss The TRANSFER OWNERSHIP statement returns sqlcode 0, indicating a successful operation.
User3Select viewschema, viewname, owner from syscat.views where viewschema = 'NEWTON' and viewname = 'VIEW_REGULAR' The owner has changed to BOSS, despite the fact that BOSS does not originally have any privileges on USER2.TABLE_REGULAR.

Conclusion

After reading this article, you should have a better understanding of how you can manage the ownership of a SQL object inside the DB2 database.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=155704
ArticleTitle=DB2 security, Part 7: Object ownership management for DB2 9
publish-date=08242006