User and Object Privileges

GRANT and DENY

Use GRANT or DENY to specify what actions can be processed on server objects by a specific user or group. Table privileges are the default modifier unless another modifier is specified, for example SCHEMA, CATALOG, DATA SERVICE, or DSN. When submitting a GRANT, DENY, or REVOKE request, objects must be fully qualified. In the example that follows, TABLE1 is fully qualified with the catalog and schema objects:

GRANT SELECT ON CAT1.SCHEMA1.TABLE1 TO BOB

You can also add an optional TABLE qualifier, for example:

GRANT SELECT ON TABLE CAT1.SCHEMA1.TABLE1 TO BOB

Merely specifying

GRANT
                        SELECT ON TABLE1 to BOB

produces an error due to missing because the table object is not fully qualified.

Fully qualified catalog names are one-part names:

GRANT SELECT ON CATALOG CAT1 TO BOB

Fully qualified data service and DSN names are also one-part names. Fully qualified schema names are two-part names:

GRANT SELECT ON SCHEMA CAT1.SCHEMA1 TO BOB

Note: You cannot GRANT or DENY the CREATE DSN and ADMINISTER privileges for the PUBLIC and SASUSERS groups.

GRANT | DENY { {"objectpriv" |"containerpriv" |
"serverpriv" [,...] } |

               ALL [ PRIVILEGES ] }

   [ ON { SCHEMA "schemaname" | CATALOG "catalogname" |
 [DATA] SERVICE "servicename" | DSN "dsnname" | SERVER } ]

   TO { "authid" | PUBLIC | SASUSERS } [, ...]

WHERE column-name = 'operator-value'

    [ AS ADMINISTRATOR ]

“objectpriv”

Specifies the name of an object-level privilege as one of the following values:

SELECT
EXECUTE
INSERT
UPDATE
DELETE
REFERENCES

“containerpriv”

Specifies the name of a container-level privilege as one of the following values:

CREATE VIEW
ALTER VIEW
DROP VIEW
CREATE TABLE
ALTER TABLE
DROP TABLE

“serverpriv”

Specifies the name of the server-level privilege to grant or deny, as one of the following values:

ADMINISTER
TRACE
CREATE DSN
CONNECT

SCHEMA “schemaname”

Specifies the name of the schema.

CATALOG “catalogname”

Specifies the name of the catalog.

[DATA] SERVICE “servicename”

Specifies the name of the data service.

DSN “dsnname”

Specifies the name of the DSN.

“authid”

Specifies the user or group name for which the privileges are granted or denied.

WHERE table-column-name = ‘operator-value’

Used only with GRANT for row-level security, the WHERE clause extracts only those records that fulfill a specified criteria.

[ AS ADMINISTRATOR ]

Grants privileges using the ADMINISTRATOR role. If this option is not used, the privilege is granted as the individual user. If the user is a system user, privileges are assigned with SYSTEM as the grantor.

Here are examples of the GRANT and DENY statements:

GRANT INSERT ON SCHEMA "BASE_CATALOG1"."schema1_BASE" TO "user1"

GRANT CONNECT ON SERVER TO "user1"

GRANT SELECT ON TABLE CATALOG.SCHEMA.T1 TO SALES WHERE SALES_REGION = 'NORTHEAST'

GRANT CREATE DSN ON DATA SERVICE "SQLSRV1" TO "user1"

GRANT ADMINISTER ON SERVER TO "user1"

DENY CONNECT ON DSN "SQLSRVDSN1" TO "user1"

GRANT TRACE to "user1"

DENY ALL ON SCHEMA "BASE_CATALOG1"."schema1_BASE" TO "user1"

REVOKE

Use REVOKE to remove explicitly granted or denied privileges from the specified object.

REVOKE { { "objectpriv" | "containerpriv" |
"serverpriv" [,...] } |

ALL [ PRIVILEGES ] }

[ ON { SCHEMA "schemaname" | CATALOG "catalogname" | 
     [DATA] SERVICE "servicename" | DSN "dsnname" | SERVER} ]

FROM { "authid" | PUBLIC | SASUSERS } [, ...]

“objectpriv”

Specifies the name of an object-level privilege as one of the following values:

SELECT
INSERT
EXECUTE
UPDATE
DELETE
REFERENCES

“containerpriv”

Specifies the name of a container-level privilege as one of the following values:

CREATE VIEW
ALTER VIEW
DROP VIEW
CREATE TABLE
ALTER TABLE
DROP TABLE

“serverpriv”

Specifies the name of the server-level privilege to grant or deny, as one of the following values:

ADMINISTER
TRACE
CREATE DSN
CONNECT

ALL [PRIVILEGES]

Specifies that (all) object, container, and server privileges be removed from the specified object.

SCHEMA “schemaname”

Specifies the name of the schema.

CATALOG “catalogname”

Specifies the name of the catalog.

[DATA] SERVICE “servicename”

Specifies the name of the data service.

DSN “dsnname”

Specifies the name of the DSN.

“authid”

Specifies the user or group name for which the privileges are granted or denied.

[ AS ADMINISTRATOR ]

Here are examples of the REVOKE statement:

REVOKE ALL ON SCHEMA "BASE_CATALOG1"."schema1_BASE" FROM "user1"

REVOKE INSERT ON DATA SERVICE BASE FROM "USER1"

REVOKE all on server from "user1"

DROP AUTHID

Description

Use the DROP AUTHID statement to drop or transfer a user ID or group ID. User and group IDs are shown in the AUTHORIZATION IDENTIFIERS view.

Parameters

DROP { AUTHID | AUTHORIZATION [IDENTIFIER] } "ID" [TRANSFER TO
name] [CASCADE|RESTRICT] [FORCE]

AUTHORIZATION IDENTIFIER “ID”

Specifies the authorization identity to drop. The ID must be surrounded in double quotation marks. This is the user ID or the group ID displayed in the AUTHORIZATION_IDENTIFIERS information view.

TRANSFER TO name

Specifies the authorization identity that will receive object ownership from the dropped identity. This user name is created using the SAS Metadata Server.

Drop disposition

Used to specify additional options.

CASCADE

Specifies the entities are dropped unconditionally. All records that reference the entity are removed. This option is invalid if the TRANSFER TO option is specified.

RESTRICT

Specifies that the drop fails if the entity is the grantor of any privilege. The drop also fails if the entity is the owner of a DSN or schema. This option is ignored if the TRANSFER TO option is specified.

FORCE

Specifies the optional FORCE keyword that will suppress error messages when the user does not exist.

Examples

Here are examples of the DROP AUTHID statement:

drop authid "5E563F78B0D70854086FB3D8441EF9AA" transfer to user2

drop authid "F135005B80DED494E996F70DCC53790D" cascade

drop authid "B8A105927F25B1A47AE8198D1E3C4B86" transfer to user2 force

Last updated: March 6, 2018