SQLTablePrivileges - Get privileges associated with a table
SQLTablePrivileges()
returns a list of
tables and associated privileges for each 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 by a query.
SQLTablePrivilegesW()
. Refer
to Unicode in Db2 for i CLI for more information
about Unicode support for DB2® CLI.Syntax
SQLRETURN SQLTablePrivileges (SQLHSTMT StatementHandle,
SQLCHAR *CatalogName,
SQLSMALLINT NameLength1,
SQLCHAR *SchemaName,
SQLSMALLINT NameLength2,
SQLCHAR *TableName,
SQLSMALLINT NameLength3);
Function arguments
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | StatementHandle | Input | Statement handle. |
SQLCHAR * | szTableQualifier | Input | Catalog qualifier of a 3 part table name. This must be a null pointer or a zero length string. |
SQLSMALLINT | cbTableQualifier | Input | Length of CatalogName. This must be set to 0. |
SQLCHAR * | SchemaName | Input | Buffer that might contain a pattern-value to qualify the result set by schema name. |
SQLSMALLINT | NameLength2 | Input | Length of SchemaName. |
SQLCHAR * | TableName | Input | Buffer that might contain a pattern-value to qualify the result set by table name. |
SQLSMALLINT | NameLength3 | Input | Length of TableName. |
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.
The granularity of each privilege
reported here might or might not apply at the column level; for example,
for some data sources, if a table can be updated, every column in
that table can also be updated. For other data sources, the application
must call SQLColumnPrivileges()
to discover if the
individual columns have the same table privileges.
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 calls.
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 names are
always 128 characters or less , the application may 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 DBMS.
The SQL_MAX_SCHEMA_NAME_LEN value determines the actual length of
the TABLE_SCHEM supported by the connected Database Management System
(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.
Although new columns can be added and the names of the existing columns changed in future releases, the position of the current columns does not change.
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 | 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:
|
7 IS_GRANTABLE | VARCHAR(3) | This indicates whether the grantee is permitted to grant the privilege to other users. This can be "YES", "NO" or "NULL". |
SQLProcedures()
result
set in ODBC.Return codes
- SQL_SUCCESS
- SQL_SUCCESS_WITH_INFO
- SQL_ERROR
- SQL_INVALID_HANDLE
Diagnostics
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 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 SQLTablePrivileges */
printf("\n Call SQLTablePrivileges for:\n");
printf(" tbSchemaPattern = %s\n", tbSchemaPattern);
printf(" tbNamePattern = %s\n", tbNamePattern);
sqlrc = SQLTablePrivileges( hstmt, NULL, 0,
tbSchemaPattern, SQL_NTS,
tbNamePattern, SQL_NTS);
STMT_HANDLE_CHECK( hstmt, sqlrc);