SQLColumnPrivileges function (CLI) - Get privileges associated with the columns of a table

Returns a list of columns and associated privileges for the specified 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 from a query.

Specification:

CLI 2.1
ODBC 1.0

SQLColumnPrivileges() returns a list of columns and associated privileges for the specified 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 from a query.

Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLColumnPrivilegesW(). See Unicode functions (CLI) for information about ANSI to Unicode function mappings.

Syntax

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

Function arguments

Table 1. SQLColumnPrivileges 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 CatalogName 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 support 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	The schema qualifier of the table 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	The 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.
SQLCHAR *	ColumnName	Input	A buffer that might contain a pattern value to qualify the result set by column name.
SQLSMALLINT	NameLength4	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ColumnName, or SQL_NTS if ColumnName is null-terminated.

Usage

The results are returned as a standard result set that contains the columns listed in Columns Returned by SQLColumnPrivileges. The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, and PRIVILEGE. If multiple privileges are associated with any given column, each privilege is returned as a separate row. A typical application might want to call this function after a call to SQLColumns() to determine column privilege information. The application should use the character strings that are returned in the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME columns of the SQLColumns() result set as input arguments to this function.

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

The ColumnName input argument accepts a search pattern, however, all other input arguments do not.

Sometimes, an application calls the function and no attempt is made to restrict the result set that is returned. In order to help reduce the long retrieval times, you can specify the configuration keyword SchemaList in the CLI initialization file to help restrict the result set when the application has supplied a null pointer for 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.

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 SQLColumnPrivileges

Column 1 TABLE_CAT (VARCHAR(128) Data type)

Name of the catalog. The value is NULL if this table does not have catalogs.

Column 2 TABLE_SCHEM (VARCHAR(128))

Name of the schema containing TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

Name of the table or view.

Column 4 COLUMN_NAME (VARCHAR(128) not NULL)

Name of the column of the specified table or view.

Column 5 GRANTOR (VARCHAR(128))

Authorization ID of the user who granted the privilege.

Column 6 GRANTEE (VARCHAR(128))

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

Column 7 PRIVILEGE (VARCHAR(128))

The column privilege. This can be:

INSERT
REFERENCES
SELECT
UPDATE

Note: Some IBM RDBMSs do not offer column level privileges at the column level. Db2, z/OS, and Db2 Server for VM and VSE support the UPDATE column privilege; there is one row in this result set for each updateable column. For all other privileges for Db2, z/OS, and Db2 Server for VM and VSE, and for all privileges for other IBM RDBMSs, if a privilege has been granted at the table level, a row is present in this result set.

Column 8 IS_GRANTABLE (VARCHAR(3) Data type)

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

Either YES or NO.

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 defined for the SQLColumnPrivileges() result set in ODBC.

If there is more than one privilege associated with a column, each privilege is returned as a separate row in the result set.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLColumnPrivileges SQLSTATEs
SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40001	Serialization failure	The transaction was rolled back due to a resource deadlock with another transaction.
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.
HY009	Invalid argument value.	TableName is NULL.
HY010	Function sequence error	An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. `SQLExecute()`, `SQLExecDirect()`, or `SQLSetPos()` was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.
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.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  cliRC = SQLColumnPrivileges(hstmt,
                              NULL,
                              0,
                              tbSchema,
                              SQL_NTS,
                              tbName,
                              SQL_NTS,
                              colNamePattern,
                              SQL_NTS);