DENY Statement , REVOKE Statement

The DENY statement prevents users from performing actions. This means that the statement removes existing permissions from user accounts or prevents users from gaining permissions through their group/role membership that might be granted in the future. This statement has the following syntax:

DENY {ALL [PRIVILEGES] } | permission_list
[ON [class::] securable] TO principal_list
[CASCADE] [ AS principal ]

All options of the DENY statement have the same logical meaning as the options with the same name in the GRANT statement. DENY has an additional option, CASCADE, which specifies that permissions will be denied to user A and any other users to whom user A passed this permission. (If the CASCADE option is not specified in the DENY statement, and the corresponding object permission was granted with the WITH GRANT OPTION, an error is returned.)

The DENY statement prevents the user, group, or role from gaining access to the permission granted through their group or role membership. This means that if a user belongs to a group (or role) and the granted permission for the group is denied to the user, this user will be the only one of the group who cannot use this permission. On the other hand, if a permission is denied for a whole group, all members of the group will be denied the permission.

Note – You can think of the GRANT statement as a “positive” and the DENY statement as a “negative” user authorization. Usually, the DENY statement is used to deny already existing permissions for groups (roles) to a few members of the group.

Examples 12.18 and 12.19 show the use of the DENY statement.

Example 12.18

USE sample;
DENY CREATE TABLE, CREATE PROCEDURE
	TO peter;Code language: PHP (php)

The DENY statement in Example 12.18 denies two previously granted statement permissions to the user peter.

Example 12.19

USE sample;
GRANT SELECT ON project
	TO PUBLIC;
DENY SELECT ON project
	TO peter, mary;Code language: PHP (php)

Example 12.19 shows the negative authorization of some users of the sample database. First, the retrieval of all rows of the project table is granted to all users of the sample database. After that, this permission is denied to two users: peter and mary.

Note – Permissions denied at a higher scope of the Database Engine security model override granted permissions at a lower scope. For instance, if SELECT permission is denied on the level of the sample database, and SELECT is granted on the employee table, the result is that SELECT is denied to the employee table as well as all other tables.

Related Lessons: