Chapter 11: Accessing Catalog Information with CSPs

sp_table_privileges

Description

Returns privilege information for one or more database objects.

Syntax

sp_table_privileges table_name [, table_owner]
 [, table_qualifier]

Parameters

table_name: is the name of the table. Use the wildcard character to request information about more than one table. Aliases are not supported.

table_owner: is the owner of the database object about which column privilege information is requested. Use the wildcard character to request information about tables owned by more than one user. If you do not specify this parameter, sp_table_privileges looks first for a table owned by the current user and then for a table owned by the database owner.

table_qualifier: is ignored. Leave blank or set to NULL.

Usage

The DB2 access service selects information from the SYSTABAUTH system catalog table.
This function corresponds to the ODBC function SQLTablePrivileges.

Results

sp_table privileges returns a list of one or more database objects with privilege information about each. Results are ordered by the following columns:

TABLE_OWNER
TABLE_NAME
PRIVILEGE

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table shows the result set.

Table 11-16: Result set for *sp_table_privileges*
Column name	Datatype	Notes
TABLE_QUALIFIER	varchar (128)	Always NULL.
TABLE_OWNER	varchar (128)	Table owner identifier (authorization ID).
TABLE_NAME	varchar (128) NOT NULL	Name of the database object about which privilege information is returned.
GRANTOR	varchar (128)	Identifies the user who granted this privilege; NULL if not applicable to the target database.
GRANTEE	varchar (128) NOT NULL	Identifies the user to whom this privilege was granted.
PRIVILEGE	varchar (128) NOT NULL	Identifies the privilege granted to the grantee on this object as one of the following values: SELECT if the grantee is authorized to select rows in the associated object. INSERT if the grantee is authorized to insert rows into the associated object. UPDATE if the grantee is authorized to update rows in the associated object. REFERENCES if the grantee is authorized to refer to one or more columns of the table within a constraint (for example: unique, referential, or table check constraint).
IS_GRANTABLE	varchar (3)	Indicates whether the grantee is authorized to grant privilege on this object to others users with one of the following values: YES if the grantee can grant this privilege to others. NO if the grantee cannot grant this privilege to others. NULL if it is unknown or not applicable to the target database.

View this book as PDF