X_COLUMN_PRIVILEGES/X_EFFECTIVE_COLUMN_PRIVILEGES

The X_COLUMN_PRIVILEGES and the X_EFFECTIVE_COLUMN_PRIVILEGES views contain both the privileges for users and groups on all objects2, and indicate inheritance. They show all direct (explicit) and inherited privileges based on the privileges of the user or group or its group membership. Unlike most other views, the views do not strictly derive the information from system tables. It will merge metadata from the physical data sources with metadata in system tables to produce a complete result set for all objects.
The X_COLUMN_PRIVILEGES result set contains rows for users and groups that have any privilege directly set. If a user or group does not have any direct privilege, it will not be shown in this view. It is a condensed view of the X_EFFECTIVE_COLUMN_PRIVILEGES view.
The X_EFFECTIVE_COLUMN_PRIVILEGES result set contains rows for all users and groups that have any privilege directly set or a privilege can be derived from its group membership. For example, even if a user does not have any privileges directly set, records for this user will be in the result set if any of the groups in its group hierarchy has a privilege directly set.
Note: Both of these views can return very large result sets depending on the configuration of Federation Server. Subsetting on DATA_SERVICE, CATALOG_NAME, and/or SCHEMA_NAME can reduce the size of the result set.
Name
Type
Description
DATA_SERVICE
VARCHAR(256)
Specifies the data service name.
CATALOG_NAME
VARCHAR(256)
Specifies the catalog name.
COLUMN_NAME
VARCHAR(256)
Specifies the column name.
SCHEMA_NAME
VARCHAR(256)
Specifies the schema name.
GRANTOR_ID
VARCHAR(256)
Specifies the AuthID of the grantor.
GRANTOR
VARCHAR(256)
Specifies the name of the grantor. This field could be NULL if the user no longer exists.
GRANTOR_TYPE
CHAR(1)
Specifies the grantor type as R (Role) or U (User).
GRANTEE_ID
VARCHAR(256)
Specifies the AuthID of the grantee.
GRANTEE
VARCHAR(256)
Specifies the name of the grantee.
GRANTEE_TYPE
CHAR(1)
Specifies the grantee type as U (User) or G (Group).
PRIVILEGE_NAME
VARCHAR(256)
Name of privilege as reflected in the following list:
SELECT
UPDATE
INSERT
DELETE
EXECUTE
REFERENCES
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE VIEW
ALTER VIEW
DROP VIEW
PRIVILEGE_TYPE
VARCHAR(256)
Specifies the privilege type as GRANT or DENY.
GRANTABLE
CHAR(1)
Specifies if the privilege is grantable. The only valid value is N (No).
INHERITED
CHAR(1)
Specifies if the privilege is inherited with Y (Yes) or N (No).
SOURCE_OBJECT_LEVEL
INTEGER
Specifies the object level where the privilege is inherited from:
0 – server
1 – data service
2 – catalog
3 – schema
4 – object
5 - column
SOURCE_GRANTEE_ID
VARCHAR(256)
AuthID of group or user the privilege is derived from.
SOURCE_GRANTEE
VARCHAR(256)
Specifies the group or user name the privilege is derived from.
SOURCE_GRANTEE_TYPE
CHAR(1)
Specifies the grantee as U (User) or G (Group).
OBJECT_NAME
VARCHAR(256)
Specifies the name of the object.
2 Current list of objects includes:
  • table server
  • data services
  • catalogs
  • schemas
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: March 6, 2018