Blog
DB2utor

Categories

August 16, 2010

Object Creator, Schema, Qualifier and Owner

Recently a user identifying himself as new to DB2 for z/OS posted this question on the DB2-L list server:

"What is the difference (if any) between DB2 creator, schema qualifier and owner?"

When an object is created, an authorization ID is assigned as the owner of the object and, in most cases, this value is used as the schema qualifier for an object. One factor in determining the authorization ID depending is if the statement is executed using static or dynamic bind rules. I always use dynamic SQL and the SET CURRENT SCHEMA and SET CURRENT SQLID statements to control the object's schema qualifier and owner. For a detailed guide to setting these values in static and dynamic programs, see the DB2 9 for z/OS SQL Reference Guide.

The object owner implicitly inherits privileges to reference, maintain and grant privileges to the object in any SQL statement. Another term used is object creator. This is really the same as object owner.

Here's an example using dynamic SQL. SET CURRENT SCHEMA specifies the object qualifier and SET CURRENT SQLID specifies the object createdby and owner.

Example: Create table T1 using SCHEMA TROY and SQLID USER1

SET CURRENT SCHEMA = 'TROY';
 SET CURRENT SQLID = 'USER1';   
CREATE TABLE T1 (COL1 CHAR(10)); 
COMMIT;

Now query the catalog for table T1 has a schema name of "TROY," known as CREATOR on the catalog table systables and the OWNER authorized to manage this object.

SELECT SUBSTR(CREATOR,1,8) AS SCHEMA  
     , SUBSTR(NAME,1,18) AS NAME         
     , SUBSTR(CREATEDBY,1,8) AS CREATEDBY
     , SUBSTR(OWNER,1,8) AS OWNER        
FROM SYSIBM.SYSTABLES                    
WHERE CREATOR = 'TROY'                   
  AND NAME = 'T1'#                       

SCHEMA    NAME      CREATEDBY  OWNER 
------------+-----------+----------------+------------
TROY          T1             USER1             USER1

In summary, an object creator or qualifier is synonymous to schema. The OWNER is the authorization ID set by the CURRENT SQLID special register, which is authorized to maintain and grant privileges on the given object.