Call Level Interface Guide and Reference

SQLTablePrivileges - Get Privileges Associated With A Table

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLTablePrivileges() returns a list of tables and associated privileges for each table. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set generated by a query.

Syntax

SQLRETURN SQLTablePrivileges (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      FAR  *CatalogName,      /*  szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      FAR  *SchemaName,       /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      FAR  *TableName,        /* szTableName */
                SQLSMALLINT       NameLength3);      /* cbTableName */

Function Arguments

Table 176. SQLTablePrivileges Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLCHAR * szTableQualifier Input Catalog qualifier of a 3 part table name. This must be a null pointer or a zero length string.
SQLSMALLINT cbTableQualifier Input Length of CatalogName. This must be set to 0.
SQLCHAR * SchemaName Input Buffer that may contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT NameLength2 Input Length of SchemaName.
SQLCHAR * TableName Input Buffer that may contain a pattern-value to qualify the result set by table name.
SQLSMALLINT NameLength3 Input Length of TableName.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLCHAR *	szTableQualifier	Input	Catalog qualifier of a 3 part table name. This must be a null pointer or a zero length string.
SQLSMALLINT	cbTableQualifier	Input	Length of `CatalogName`. This must be set to 0.
SQLCHAR *	SchemaName	Input	Buffer that may contain a `pattern-value` to qualify the result set by schema name.
SQLSMALLINT	NameLength2	Input	Length of `SchemaName`.
SQLCHAR *	TableName	Input	Buffer that may contain a `pattern-value` to qualify the result set by table name.
SQLSMALLINT	NameLength3	Input	Length of `TableName`.

Note that the SchemaName and TableName arguments accept search pattern. For more information about valid search patterns, refer to Input Arguments on Catalog Functions.

Usage

The results are returned as a standard result set containing the columns listed in the following table. The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and PRIVILEGE. If multiple privileges are associated with any given table, each privilege is returned as a separate row.

The granularity of each privilege reported here may or may not apply at the column level; for example, for some data sources, if a table can be updated, every column in that table can also be updated. For other data sources, the application must call SQLColumnPrivileges() to discover if the individual columns have the same table privileges.

Since calls to SQLTablePrivileges() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_OWNER_SCHEMA_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Columns Returned By SQLTablePrivileges

Column 1 TABLE_CAT (VARCHAR(128))

This is always null.

Column 2 TABLE_SCHEM (VARCHAR(128))

The name of the schema contain TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

The name of the table.

Column 4 GRANTOR (VARCHAR(128))

Authorization ID of the user who granted the privilege.

Column 5 GRANTEE (VARCHAR(128))

Authorization ID of the user to whom the privilege is granted.

Column 6 PRIVILEGE (VARCHAR(128))

The table privilege. This may be one of the following strings:

ALTER
CONTROL
INDEX
DELETE
INSERT
REFERENCES
SELECT
UPDATE

Column 7 IS_GRANTABLE (VARCHAR(3))

Indicates whether the grantee is permitted to grant the privilege to other users.

This can be "YES", "NO" or NULL.

Note: The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents and order are identical to those defined for the SQLProcedures() result set in ODBC.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 177. SQLTablePrivileges SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY009 Invalid argument value. TableName is null.
HY010 Function sequence error.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the SQLGetInfo() function.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY009	Invalid argument value.	`TableName` is null.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the `SQLGetInfo()` function.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

CLI Sample tbinfo.c

(The complete sample tbinfo.c is also available here .)

 
/* From the CLI sample TBINFO.C */
/* ... */
 
    /* call SQLTablePrivileges */ 
    printf("\n    Call SQLTablePrivileges for:\n");
    printf("        tbSchemaPattern = %s\n", tbSchemaPattern);    
    printf("        tbNamePattern = %s\n", tbNamePattern);    
    sqlrc = SQLTablePrivileges( hstmt, NULL, 0,
                                tbSchemaPattern, SQL_NTS,
                                tbNamePattern, SQL_NTS); 
    STMT_HANDLE_CHECK( hstmt, sqlrc);

References

SQLTables - Get Table Information

[ Top of Page | Previous Page | Next Page ]