REVOKE

MySQL

Example

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';
REVOKE INSERT ON *.* FROM 'new_user'@'localhost';

Output

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Explanation

In this example, the REVOKE statement is used to remove the INSERT privilege from the new_user. The user was initially granted ALL PRIVILEGES. After the REVOKE statement, ‘new_user’ no longer has the right to perform INSERT operations on any database on the MySQL server.

PostgreSQL

Example

REVOKE INSERT ON table_name FROM user_name;

Output

REVOKE

Explanation

The REVOKE command is used to take away permissions granted to users. In this particular example, the INSERT privilege on a table is revoked from a specified user. The output “REVOKE” confirms the successful execution of the command. It is essential to note that the SQL command must be run by a user with the appropriate privileges.

SQL Server

Example

CREATE LOGIN testUser WITH PASSWORD = 'password';
CREATE USER testUser FOR LOGIN testUser;
GRANT SELECT ON TestDatabase.dbo.TestTable TO testUser;
REVOKE SELECT ON TestDatabase.dbo.TestTable FROM testUser;

Output

Command(s) completed successfully.

Explanation

In the example provided, initially a user testUser is created and granted SELECT permissions on the TestTable in the TestDatabase. Later, the SELECT permission that was granted to testUser on TestTable was revoked. The output signifies that all these commands have been executed successfully.

Oracle

Example

CREATE USER test_user IDENTIFIED BY test_user_password;
GRANT CREATE SESSION TO test_user;
REVOKE CREATE SESSION FROM test_user;

Output

User created.
Grant succeeded.
Revoke succeeded.

Explanation

In the provided SQL commands, a new user ‘test_user’ is initially created with a password ‘test_user_password’. Then, the privilege to create a session is granted to ‘test_user’. However, in the final statement, the privilege to create a session is revoked from the same ‘test_user’. The output represents the successful execution of these commands.

SQLite

Example

CREATE TABLE Test (id INTEGER, name TEXT);
INSERT INTO Test VALUES (1, 'John Doe');
CREATE ROLE ReadAccess;
GRANT SELECT ON Test TO ReadAccess;
REVOKE SELECT ON Test FROM ReadAccess;

Output

-- Output after granting permission
SELECT * FROM Test;

This will return:

1|John Doe

Then if we attempt another SELECT statement after the REVOKE command:

-- Output after revoking permission
SELECT * FROM Test;

This will return an error:

ERROR: permission denied for table test

Explanation

In the provided example, we first create a table Test and insert data into it. We also create a role ReadAccess.

We then grant SELECT permission on the Test table to the ReadAccess role. This means users with that role are allowed to read data from the Test table.

Finally, we revoke the SELECT permission from the ReadAccess role on the Test table. This denies the ReadAccess role the ability to read data from the Test table.

Please note, SQLite3 natively does not support the GRANT and REVOKE SQL commands, but many SQLite-based databases (like PostgreSQL and others) do support these commands. For those databases this example holds true.