SQL Reference

DENY

DENY is a command in SQL Server that is used to deny permissions to a certain principal (users, roles, or logins). It is primarily used to refuse specific actions on a database object or an entire database. A DENY command overrules all GRANT and INHERIT permissions. This command aids in the management of database security and access control.

Example

The following SQL code demonstrates the usage of the DENY command in SQL Server:

DENY SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.Person TO user1;

Output

The SQL Server Management Studio will not display any output for the above command as this command is only executed internally to change permissions. The output can be confirmed by attempting to execute any select, insert, update, or delete operations with the ‘user1’ user.

Explanation

The DENY command is used to deny permissions to a user in SQL Server. It revokes specified permissions to a certain database object from a user or role. In the above example, DENY is used to revoke ‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’ permissions on the Person table in the dbo schema from ‘user1’. After this command is executed and committed, ‘user1’ will not be able to select, insert, update, or delete any data in the Person table.

Example

CREATE USER TestUser IDENTIFIED BY Test123;
CREATE TABLE TestTable (TestColumn VARCHAR2(30));

DENY SELECT ON TestTable TO TestUser;

Output

User created.
Table created.
User does not have the privilege SELECT.

Explanation

In the given example, we first create a new user ‘TestUser’ with password ‘Test123’. We then create a new table ‘TestTable’. Finally, we attempt to deny ‘TestUser’ the SELECT privilege on ‘TestFrame’. The output indicates that the user does not have the SELECT privilege. However, it is important to note that in Oracle, the ‘DENY’ statement does not exist. Instead, Oracle uses a REVOKE command to remove certain permissions.