GRANT and DENY Statements

Enables you to give privileges to a specific user or all users to perform actions on objects. When submitting a grant, revoke, or deny DDL, surround all identifiers in quotation marks, including table and column names.

Note: CREATE DSN and ADMINISTER privileges cannot be granted or denied for the PUBLIC and USERS groups.

Syntax

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

               ALL [ PRIVILEGES ] }

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

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

    [ AS ADMINISTRATOR ]

“objectpriv”

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

SELECT
INSERT
UPDATE
DELETE
REFERENCES

“containerpriv”

Specifies the name of a container-level privilege to grant or deny, 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

“schemaname”

Specifies the name of the schema.

“catalogname”

Specifies the name of the catalog.

“servicename”

Specifies the name of the data service.

“dsnname”

Specifies the name of the DSN.

“authid”

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

AS ADMINISTRATOR

Grants privileges using the ADMINISTRATOR role. Without this, the privilege is granted as the individual user.

Note: If the user is a system user, then privileges are assigned with SYSTEM as the grantor.

Examples:

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

GRANT CONNECT ON SERVER TO "user1"

DENY CONNECT ON DSN "SQLSRVDSN1" TO "user1"

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

GRANT ADMINISTER ON SERVER TO "user1"

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