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.

Practice

1. What does the GRANT command do in PostgreSQL?

easy

A. It gives specific permissions to a user or role.

B. It deletes a user from the database.

C. It creates a new database.

D. It backs up the database.

Solution

1. Step 1: Understand the purpose of GRANT

   The GRANT command is used to give permissions like SELECT, INSERT, or UPDATE to users or roles.

2. Step 2: Compare with other options

   Deleting users, creating databases, or backing up are done by other commands, not GRANT.

3. Final Answer:

   It gives specific permissions to a user or role. -> Option A

4. Quick Check:

   GRANT = give permissions [OK]

Hint: GRANT means give rights; REVOKE means take away [OK]

Common Mistakes:

• Confusing GRANT with user creation
• Thinking GRANT deletes data
• Mixing GRANT with backup commands

2. Which of the following is the correct syntax to grant SELECT permission on table employees to user john?

easy

A. GRANT ON employees SELECT TO john;

B. GRANT john ON employees SELECT;

C. GRANT SELECT TO john ON employees;

D. GRANT SELECT ON employees TO john;

Solution

1. Step 1: Recall correct GRANT syntax

   The correct syntax is: GRANT permission ON object TO user;

2. Step 2: Match syntax with options

   GRANT SELECT ON employees TO john; matches the correct order: GRANT SELECT ON employees TO john;

3. Final Answer:

   GRANT SELECT ON employees TO john; -> Option D

4. Quick Check:

   GRANT + permission + ON + object + TO + user [OK]

Hint: GRANT permission ON object TO user; is the pattern [OK]

Common Mistakes:

• Mixing order of keywords
• Placing user before permission
• Omitting ON keyword

3. Given the commands:
GRANT SELECT ON customers TO alice;
REVOKE SELECT ON customers FROM alice;
What permissions does user alice have on table customers after these commands?

medium

A. Alice can SELECT from customers.

B. Alice can INSERT into customers.

C. Alice cannot SELECT from customers.

D. Alice has all permissions on customers.

Solution

1. Step 1: Analyze the GRANT command

   Alice is given SELECT permission on customers table.

2. Step 2: Analyze the REVOKE command

   The SELECT permission is then revoked from Alice, removing her ability to SELECT.

3. Final Answer:

   Alice cannot SELECT from customers. -> Option C

4. Quick Check:

   REVOKE removes permission given by GRANT [OK]

Hint: REVOKE removes permissions previously granted [OK]

Common Mistakes:

• Assuming REVOKE adds permissions
• Confusing SELECT with INSERT
• Ignoring the order of commands

4. Identify the error in the following command:
REVOKE ALL PRIVILEGES employees FROM bob;

medium

A. The order of keywords is incorrect.

B. The user name should be before the table name.

C. REVOKE cannot be used with ALL PRIVILEGES.

D. The command is correct.

Solution

1. Step 1: Check correct REVOKE syntax

   The correct syntax is: REVOKE privileges ON object FROM user;

2. Step 2: Compare with given command

   The command uses: REVOKE ALL PRIVILEGES employees FROM bob; missing ON keyword and wrong order.

3. Final Answer:

   The order of keywords is incorrect. -> Option A

4. Quick Check:

   REVOKE + privileges + ON + object + FROM + user [OK]

Hint: REVOKE syntax requires ON before object [OK]

Common Mistakes:

• Omitting ON keyword
• Swapping user and object positions
• Using ALL PRIVILEGES incorrectly

5. You want to allow user carol to insert and update data on table orders, but not delete. Which commands correctly grant these permissions?

hard

A. GRANT DELETE ON orders TO carol;

B. GRANT INSERT, UPDATE ON orders TO carol;

C. GRANT ALL PRIVILEGES ON orders TO carol;

D. GRANT SELECT, DELETE ON orders TO carol;

Solution

1. Step 1: Identify required permissions

   Carol needs INSERT and UPDATE permissions only, no DELETE.

2. Step 2: Match commands with required permissions

   GRANT INSERT, UPDATE ON orders TO carol; grants INSERT and UPDATE correctly. Options B and D grant DELETE, which is not wanted. GRANT ALL PRIVILEGES ON orders TO carol; grants all permissions, including DELETE.

3. Final Answer:

   GRANT INSERT, UPDATE ON orders TO carol; -> Option B

4. Quick Check:

   Grant only needed permissions, avoid ALL PRIVILEGES if not required [OK]

Hint: Grant only needed permissions, avoid ALL PRIVILEGES if unsure [OK]

Common Mistakes:

• Granting DELETE when not needed
• Using ALL PRIVILEGES carelessly
• Confusing SELECT with UPDATE