SQLTablePrivileges() - Get table privileges

SQLTablePrivileges() returns a list of tables and associated privileges for each table. The information is returned in an SQL result set. You can retrieve this result set with the same functions that you use to process a result set that is generated by a query.

ODBC specifications for SQLTablePrivileges()

Table 1. SQLTablePrivileges() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
1.0 No No

Syntax

SQLRETURN SQLTablePrivileges (SQLHSTMT          hstmt,
                              SQLCHAR     FAR   *szCatalogName,
                              SQLSMALLINT       cbCatalogName,
                              SQLCHAR     FAR   *szSchemaName,
                              SQLSMALLINT       cbSchemaName,
                              SQLCHAR     FAR   *szTableName,
                              SQLSMALLINT       cbTableName);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLTablePrivileges() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Statement handle.
SQLCHAR * szTableQualifier input Catalog qualifier of a three-part table name. This must be a null pointer or a zero length string.
SQLSMALLINT cbTableQualifier input The length, in bytes, of szCatalogName. This must be set to 0.
SQLCHAR * szSchemaName input Buffer that can contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT cbSchemaName input The length, in bytes, of szSchemaName.
SQLCHAR * szTableName input Buffer that can contain a pattern-value to qualify the result set by table name.
SQLSMALLINT cbTableName input The length, in bytes, of szTableName.

The szSchemaName and szTableName arguments accept search patterns.

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.

Because 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 are declared with a maximum length attribute of 128 bytes to be consistent with ANSI/ISO SQL standard of 1992 limits. Because Db2 names are less than 128 bytes, the application can choose to always set aside 128 bytes (plus the nul-terminator) for the output buffer. Alternatively, you can call SQLGetInfo() with the InfoType argument set to each of the following values:
  • SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
  • SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
  • SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
  • SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns remains unchanged. The following table lists the columns in the result set SQLTablePrivileges() currently returns.

Table 3. Columns returned by SQLTablePrivileges()
Column number Column name Data type Description
1 TABLE_CAT VARCHAR(128) The is always null.
2 TABLE_SCHEM VARCHAR(128) The name of the schema contain TABLE_NAME.
3 TABLE_NAME VARCHAR(128) NOT NULL The name of the table.
4 GRANTOR VARCHAR(128) Authorization ID of the user who granted the privilege.
5 GRANTEE VARCHAR(128) Authorization ID of the user to whom the privilege is granted.
6 PRIVILEGE VARCHAR(128) The table privilege. This can be one of the following strings:
  • ALTER
  • CONTROL
  • DELETE
  • INDEX
  • INSERT
  • REFERENCES
  • SELECT
  • UPDATE
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.

The column names used by Db2 ODBC 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

After you call SQLTablePrivileges(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. SQLTablePrivileges() SQLSTATEs
SQLSTATE Description Explanation
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
24000 Invalid cursor state. A cursor is opened on the statement handle.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010 Function sequence error. The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the SQLParamData() or SQLPutData() functions.)
HY014 No more handles. Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090 Invalid string or buffer length. This SQLSTATE is returned for one or more of the following reasons:
  • The value of one of the name length arguments is less than 0, but not equal to SQL_NTS.
  • The value 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 ODBC does not support catalog as a qualifier for table name.

Example

The following example shows an application that uses SQLTablePrivileges() to generate a result set of privileges on tables.
Figure 1. An application that generates a result set containing privileges on tables 
/* ... */
SQLRETURN
list_table_privileges(SQLHDBC hdbc, SQLCHAR *schema,
                       SQLCHAR *tablename )
{
    SQLHSTMT        hstmt;
    SQLRETURN       rc;
    struct { SQLINTEGER ind;   /* Length & Indicator variable */
             SQLCHAR  s[129];  /* String variable */
           } grantor, grantee, privilege;
    struct { SQLINTEGER  ind;
             SQLCHAR     s[4];
            }is_grantable;
    SQLCHAR         cur_name[512] = "";  /* Used when printing the */
    SQLCHAR         pre_name[512] = "";  /* Result set */
    /* Allocate a statement handle to reference the result set */
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    /* Create table privileges result set */
    rc = SQLTablePrivileges(hstmt, NULL, 0, schema, SQL_NTS,
                             tablename, SQL_NTS);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) grantor.s, 129,
                    &grantor.ind);
/* Continue Binding, then fetch and display result set */
/* ... */