GRANT and REVOKE permissions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the effect of this GRANT command?

Consider the following SQL command executed in PostgreSQL:

GRANT SELECT, INSERT ON TABLE employees TO user_jane;

What permissions does user_jane have on the employees table after this command?

A user_jane can read, add, and delete rows from employees table.

B user_jane can only read data from employees table.

C user_jane can modify table structure of employees.

D user_jane can read and add new rows to employees table.

💡 Hint

SELECT allows reading data, INSERT allows adding new rows.

The GRANT command gives user_jane permission to SELECT (read) and INSERT (add new rows) on the employees table. It does not allow deleting or modifying the table structure.

❓ query_result

intermediate

What happens after this REVOKE command?

Assume user_john has SELECT and UPDATE permissions on the products table.

After running:

REVOKE UPDATE ON products FROM user_john;

Which of the following is true?

A user_john can still read data but cannot update products.

B user_john can no longer read data from products.

C user_john loses all permissions on products.

D user_john can update but cannot read products.

💡 Hint

REVOKE only removes specified permissions.

REVOKE UPDATE removes only the update permission. user_john still has SELECT permission to read data.

📝 Syntax

advanced

Which GRANT command is syntactically correct to give all privileges on a table?

You want to give all possible permissions on the table orders to user_mike.

Which of the following commands is correct in PostgreSQL?

A GRANT ALL ON orders TO user_mike;

B GRANT ALL RIGHTS ON orders TO user_mike;

C GRANT ALL PERMISSIONS ON orders TO user_mike;

D GRANT ALL PRIVILEGES ON orders TO user_mike;

💡 Hint

PostgreSQL uses 'ALL' without 'PRIVILEGES' keyword.

In PostgreSQL, the correct syntax to grant all privileges is 'GRANT ALL ON table TO user;'. The keyword 'PRIVILEGES' is not used.

🧠 Conceptual

advanced

What is the effect of granting permissions WITH GRANT OPTION?

In PostgreSQL, what does adding WITH GRANT OPTION to a GRANT command do?

A Limits the user to only read data.

B Automatically revokes permissions after a time.

C Allows the user to grant the same permissions to others.

D Prevents the user from revoking their own permissions.

💡 Hint

Think about permission delegation.

WITH GRANT OPTION lets the user pass on the granted permissions to other users.

🔧 Debug

expert

Why does this REVOKE command fail with an error?

Given the command:

REVOKE SELECT, INSERT FROM user_anna ON TABLE sales;

Why does PostgreSQL raise a syntax error?

A The order of clauses is incorrect; ON TABLE must come before permissions.

B The keyword TABLE is not allowed after ON in this context.

C User names cannot start with 'user_'.

D Permissions must be revoked one at a time.

💡 Hint

Check the correct syntax order for REVOKE.

In PostgreSQL, the correct syntax is REVOKE permissions ON table FROM user; The keyword TABLE is not used after ON.