`SQLColumnPrivileges()` - Get column privileges

SQLColumnPrivileges() returns a list of columns and associated privileges for the specified table. The information is returned in an SQL result set. You can retrieve the result set by using the same functions that you use to process a result set that a query generates.

ODBC specifications for `SQLColumnPrivileges()`

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

Syntax

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

Function arguments

Table 2 lists the data type, use, and description for each argument in this function.

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

Usage

The results are returned as a standard result set that contains the columns listed in Table 3. 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. Typically, you 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_SCHEM, TABLE_NAME, and COLUMN_NAME columns of the SQLColumns() result set as input arguments to this function.

Because calls to SQLColumnPrivileges() frequently result in a complex and thus expensive query to the catalog, used these calls sparingly, and save the results rather than repeat the calls.

The VARCHAR columns of the catalog functions result set are declared with a maximum length attribute of 128 bytes (which is consistent with ANSI/ISO SQL standard of 1992 limits). Because Db2 names are shorter than 128 characters, the application can choose to always set aside 128 characters (plus the nul-terminator) for the output buffer. You can alternatively 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

Note that the szColumnName argument accepts a search pattern.

Although new columns might be added and the names of the existing columns might change in future releases, the position of the current columns will remain unchanged. The following table lists the columns in the result set that SQLColumnPrivileges() currently returns.

Table 3. Columns returned by `SQLColumnPrivileges()`
Column number	Column name	Data type	Description
1	TABLE_CAT	VARCHAR(128)	Always null.
2	TABLE_SCHEM	VARCHAR(128)	Indicates the name of the schema that contains TABLE_NAME.
3	TABLE_NAME	VARCHAR(128) not NULL	Indicates the name of the table or view.
4	COLUMN_NAME	VARCHAR(128) not NULL	Indicates the name of the column of the specified table or view.
5	GRANTOR	VARCHAR(128)	Indicates the authorization ID of the user who granted the privilege.
6	GRANTEE	VARCHAR(128)	Indicates the authorization ID of the user to whom the privilege is granted.
7	PRIVILEGE	VARCHAR(128)	Indicates the column privilege. This can be: ALTER CONTROL DELETE INDEX INSERT REFERENCES SELECT UPDATE Supported privileges are based on the data source to which you are connected. Most IBM® relational database management systems do not offer column-level privileges at the column level. Db2 for z/OS® and Db2 server for VSE and VM support the UPDATE column privilege; each updatable column receives one row in this result set. For all other privileges for Db2 for z/OS and Db2 server for VSE and VM, and for all privileges for other IBM relational database management systems, if a privilege has been granted at the table level, a row is present in this result set.
8	IS_GRANTABLE	VARCHAR(3)	Indicates whether the grantee is permitted to grant the privilege to other users. Either "YES" or "NO".

The column names that Db2 ODBC uses follow the X/Open CLI CAE specification style. The column types, contents, and order are identical to those that are defined for the SQLColumnPrivileges() result set in ODBC.

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

Return codes

After you call SQLColumnPrivileges(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 4 lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLColumnPrivileges()` 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 open 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.
HY009	Invalid use of a null pointer.	The szTableName argument is null.
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments is less than 0 and not equal to SQL_NTS.
HYC00	Driver not capable.	Db2 ODBC does not support "catalog" as a qualifier for table name.

Example

The following example shows an application that prints a list of column privileges for a table.

Figure 1. An application that retrieves user privileges on table columns

/* ... */
SQLRETURN
list_column_privileges(SQLHDBC hdbc, SQLCHAR *schema, SQLCHAR *tablename )
{
/* ... */
    rc = SQLColumnPrivileges(hstmt, NULL, 0, schema, SQL_NTS,
                             tablename, SQL_NTS, columnname.s, SQL_NTS);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) columnname.s, 129,
                    &columnname.ind);
    rc = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER) grantor.s, 129,
                    &grantor.ind);
    rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) grantee.s, 129,
                    &grantee.ind);
    rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) privilege.s, 129,
                    &privilege.ind);
     rc = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER) is_grantable.s, 4,
                    &is_grantable.ind);
    printf("Column Privileges for %s.%s\n", schema, tablename);
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        sprintf(cur_name, " Column: %s\n", columnname.s);
        if (strcmp(cur_name, pre_name) != 0) {
            printf("\n%s\n", cur_name);
            printf("   Grantor         Grantee         Privilege  Grantable\n");
            printf("   --------------- --------------- ---------- ---\n");
        }
        strcpy(pre_name, cur_name);
        printf(" %-15s", grantor.s);
        printf(" %-15s", grantee.s);
        printf(" %-10s", privilege.s);
        printf(" %-3s\n", is_grantable.s);
    }                           /* endwhile */
/* ... */

SQLColumnPrivileges() - Get column privileges

ODBC specifications for SQLColumnPrivileges()