X_OBJECT_PRIVILEGES/X_EFFECTIVE_OBJECT_PRIVILEGES View

The X_OBJECT_PRIVILEGES and the X_EFFECTIVE_OBJECT_PRIVILEGES views contain both the privileges for users and groups on all objects2 and indicates inheritance. They show all direct (explicit) and inherited privileges based on the privileges of the user and 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_OBJECT_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_OBJECT_PRIVILEGES view.
The X_EFFECTIVE_OBJECT_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 instance, 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.
If a privilege is not explicitly listed in the result sets, it is DENIED by default.
Name
Type
Description
DATA_SERVICE
VARCHAR(256)
Specifies the data service name.
CATALOG_NAME
VARCHAR(256)
Specifies the catalog 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 U (User) or R (Role).
GRANTEE_ID
VARCHAR(256)
Specifies the AuthID of the grantee.
GRANTEE
VARCHAR(256
Specifies the grantee name.
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
CREATE CACHE
ALTER CACHE
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 or N.
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:
  • server
  • data services
  • catalogs
  • schemas
Copyright © SAS Institute Inc. All rights reserved.