PRIVILEGES and EFFECTIVE_PRIVILEGES

Displays the privileges, including inheritance, for users and groups on schemas, catalogs and data services. Both views show all direct (explicit) and inherited privileges based on the privileges of the user or the user’s group membership.

The PRIVILEGES view contains rows for users and groups that have any direct privileges 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 EFFECTIVE_PRIVILEGES view.

The EFFECTIVE_PRIVILEGES view contains rows for all users and groups that have any direct privileges set, or privileges inherited as a result of group membership. For example, if a user does not have any privileges set on any of the Federation Server objects, the user will still be in the result set if the user is a member of a group that has direct privileges.

Note: Both of these views can return very large result sets depending on the configuration of SAS Federation Server. Subsetting on DATA_SERVICE, CATALOG_NAME, and/or SCHEMA_NAME can reduce the size of the result set.

The table below lists the columns in the PRIVILEGES and EFFECTIVE_PRIVILEGES view:

Name	Type	Description
DATA_SERVICE	VARCHAR(256)	Specifies the name of the data service.
CATALOG_NAME	VARCHAR(256)	Specifies the name of the catalog.
SCHEMA_NAME	VARCHAR(256)	Specifies the name of the schema.
GRANTOR_ID	VARCHAR(256)	Specifies the AuthID of the user that granted or denied the privilege.
GRANTOR	VARCHAR(256)	Specifies the name of the user who is granted or denied the privilege.
GRANTOR_TYPE	CHAR(1)	Specifies the grantor type as U (User) or R (Role).
GRANTEE_ID	VARCHAR(256)	Specifies the AuthID of the user that is granted or denied the privilege.
GRANTEE	VARCHAR(256)	Specifies the name of the user who is granted or denied the privilege.
GRANTEE_TYPE	CHAR(1)	Specifies the grantee type as U (User) or G (Group).
PRIVILEGE_NAME	VARCHAR(20)	Specifies the privilege name as one of the following values: SELECT UPDATE EXECUTE INSERT REFERENCES
PRIVILEGE_TYPE	VARCHAR(5)	Specifies the privilege type as GRANT or DENY.
GRANTABLE	CHAR(1)	Specifies if the privilege can be granted. The only valid value is or N (No).
INHERITED	CHAR(1)	Indicates whether the privilege is inherited as either Y or N.
SOURCE_OBJECT_LEVEL	INTEGER	Specifies the object level where the privilege is inherited, as one of the following values: 0 - server 1 - data service 2 - catalog 3 - schema
SOURCE_GRANTEE_ID	VARCHAR(256)	Specifies the AuthID of a group or user from which the privilege is derived.
SOURCE_GRANTEE	VARCHAR(256)	Specifies the name of the group or user from which the privilege is derived.
SOURCE_GRANTEE_TYPE	CHAR(1)	Specifies the source_grantee type as U (User) or G (Group).

Last updated: March 6, 2018