SQLColumnPrivileges - Get privileges associated with the columns of a table

SQLColumnPrivileges() 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 with the same functions that are used to process a result set generated from a query.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLColumnPrivilegesW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support forDB2 for i CLI.

Syntax

SQLRETURN SQLColumnPrivileges  (
                SQLHSTMT            StatementHandle,
                SQLCHAR             *CatalogName,
                SQLSMALLINT         NameLength1,
                SQLCHAR             *SchemaName,
                SQLSMALLINT         NameLength2,
                SQLCHAR             *TableName
                SQLSMALLINT         NameLength3,
                SQLCHAR             *ColumnName,
                SQLSMALLINT         NameLength4);

Function arguments

Table 1. SQLColumnPrivileges arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLCHAR *	CatalogName	Input	Catalog qualifier of a 3 part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	Input	Length of CatalogName. This must be set to 0.
SQLCHAR *	SchemaName	Input	Schema qualifier of table name.
SQLSMALLINT	NameLength2	Input	Length of SchemaName.
SQLCHAR *	TableName	Input	Table Name.
SQLSMALLINT	NameLength3	Input	Length of TableName.
SQLCHAR *	ColumnName	Input	Buffer that can contain a pattern-value to qualify the result set by column name.
SQLSMALLINT	NameLength4	Input	Length of ColumnName.

Usage

The results are returned as a standard result set containing the columns listed in Table 2. 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 returned in the 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, they should be used sparingly, and the results saved rather than repeating the calls.

Start of change 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. Because DB2 for i names are always 128 characters or less in length, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN. The SQL_MAX_CATALOG_NAME_LEN value determines the actual length of the TABLE_CAT supported by the connected Database Management System (DBMS). The SQL_MAX_SCHEMA_NAME_LEN value determines the actual length of the TABLE_SCHEM supported by the connected DBMS. The SQL_MAX_TABLE_NAME_LEN value determines the actual length of the TABLE_NAME supported by the connected DBMS. The SQL_MAX_COLUMN_NAME_LEN value determines the actual length of the COLUMN_NAME supported by the connected DBMS. End of change

Note that the ColumnName argument accepts a search pattern.

Table 2. Columns returned by SQLColumnPrivileges
Column number/name	Data type	Description
1 TABLE_CAT	VARCHAR(128)	This is always NULL.
2 TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
3 TABLE_NAME	VARCHAR(128) not NULL	Name of the table or view.
4 COLUMN_NAME	VARCHAR(128) not NULL	Name of the column of the specified table or view.
5 GRANTOR	VARCHAR(128)	Authorization ID of the user who granted the privilege.
6 GRANTEE	VARCHAR(128)	Authorization ID of the user to whom the privilege is granted.
7 PRIVILEGE	VARCHAR(128)	The column privilege. This can be: INSERT REFERENCES SELECT UPDATE
8 IS_GRANTABLE	VARCHAR(3)	This indicates whether the grantee is permitted to grant the privilege to other users. Either YES or NO.

Note: The column names used by DB2 for i 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, then 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 3. SQLColumnPrivileges SQLSTATEs
SQLSTATE	Description	Explanation
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	String or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal to SQL_NTS.
HY010	Function sequence error	There is an open cursor for this statement handle, or there is no connection for this statement handle.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.

Restrictions

None.

Example

/* From the CLI sample TBINFO.C */
/* ... */
	
    /* call SQLColumnPrivileges */
    printf("\n Call SQLColumnPrivileges for:\n");
    printf(" tbSchema = %s\n", tbSchema);
    printf(" tbName = %s\n", tbName);
    sqlrc = SQLColumnPrivileges( hstmt, NULL, 0,
                                 tbSchema, SQL_NTS,
                                 tbName, SQL_NTS,
                                 colNamePattern, SQL_NTS);