SQLTablePrivileges function (CLI) - Get privileges associated with a table

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

Specification:

  • CLI 2.1
  • ODBC 1.0
Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLTablePrivilegesW(). For details about ANSI to Unicode function mappings, seeUnicode functions (CLI).

Syntax

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

Function arguments

Table 1. SQLTablePrivileges arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input The statement handle.
SQLCHAR * CatalogName Input The catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and PKCatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that supports 3-part naming.
SQLSMALLINT NameLength1 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR * SchemaName Input A buffer that can contain a pattern value to qualify the result set by schema name.
SQLSMALLINT NameLength2 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR * TableName Input A buffer that can contain a pattern value to qualify the result set by table name.
SQLSMALLINT NameLength3 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store TableName, or SQL_NTS if TableName is null-terminated.

Note that the SchemaName and TableName input arguments accept search patterns.

Usage

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

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

In many cases, calls to the SQLTablePrivileges() function map to a complex and thus expensive query against the system catalog, so you should use the calls sparingly, and save the results rather than repeating the calls.

Sometimes, an application calls the function and no attempt is made to restrict the result set that is returned. For some data sources that contain a large quantity of tables, views, and aliases for example, this scenario maps to an extremely large result set and very long retrieval times. In order to help reduce the long retrieval times, you can specify the SchemaList configuration keyword in the CLI initialization file to help restrict the result set when the application has supplied a null pointer for the SchemaName. If the application specifies a SchemaName string, the SchemaList keyword is still used to restrict the output. Therefore, if the schema name that is supplied is not in the SchemaList string, the result is an empty result set.

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 the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

You can specify *ALL or *USRLIBL as values in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. If you specify *ALL, CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. For IBM® Db2® for IBM i servers, if you specify *USRLIBL, CLI searches on the current libraries of the server job. For other Db2 servers, *USRLIBL does not have a special meaning and CLI searches using *USRLIBL as a pattern. Alternatively, you can set the SchemaFilter IBM Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL or *USRLIBL.

Although new columns might 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))
The name of the catalog table. The value is NULL if this table does not have catalogs.
Column 2 TABLE_SCHEM (VARCHAR(128))
The name of the schema that contains TABLE_NAME.
Column 3 TABLE_NAME (VARCHAR(128) not NULL)
The name of the table.
Column 4 GRANTOR (VARCHAR(128))
The authorization ID of the user who granted the privilege.
Column 5 GRANTEE (VARCHAR(128))
The authorization ID of the user to whom the privilege is granted.
Column 6 PRIVILEGE (VARCHAR(128))
The table privilege. This privilege can be one of the listed strings:
  • ALTER
  • CONTROL
  • DELETE
  • INDEX
  • INSERT
  • REFERENCES
  • SELECT
  • UPDATE
Column 7 IS_GRANTABLE (VARCHAR(3))
Indicates whether the grantee is permitted to grant the privilege to other users.

The IS_GRANTABLE value can be "YES", "NO," or NULL.

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

Return codes

  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_STILL_EXECUTING
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
HY010 Function sequence error.

The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called during 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.

The function was called before a statement was prepared on the statement handle.
HY014 No more handles. Db2 CLI was unable to allocate a handle due to resource limitations.
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 value of one of the name-length arguments exceeded the maximum value that is supported for that data source. You can obtain the maximum supported value by calling the SQLGetInfo() function.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* get privileges associated with a table */
  cliRC = SQLTablePrivileges(hstmt,
                             NULL,
                             0,
                             tbSchemaPattern,
                             SQL_NTS,
                             tbNamePattern,
                             SQL_NTS);