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 request, surround all identifiers in double quotation marks, including table and column names.

Note: You cannot grant(deny) CREATE DSN and ADMINISTER privileges for the PUBLIC and USERS groups.

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 as one of the following values:

SELECT
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.

[ 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.

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"