GRANT and REVOKE permissions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant SELECT permission on the table 'employees' to user 'john'.

PostgreSQL

GRANT [1] ON employees TO john;

Drag options to blanks, or click blank then click option'

AUPDATE

BINSERT

CSELECT

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT instead of SELECT will not allow reading data.

Forgetting to specify the permission causes syntax errors.

The SELECT permission allows the user to read data from the table.

2fill in blank

medium

Complete the code to revoke UPDATE permission on the table 'products' from user 'alice'.

PostgreSQL

REVOKE [1] ON products FROM alice;

Drag options to blanks, or click blank then click option'

AUPDATE

BINSERT

CDELETE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking SELECT instead of UPDATE does not prevent data changes.

Using incorrect syntax for REVOKE causes errors.

The UPDATE permission allows modifying existing rows. Revoking it stops the user from changing data.

3fill in blank

hard

Fix the error in the code to grant DELETE permission on 'orders' to user 'mike'.

PostgreSQL

GRANT [1] orders TO mike;

Drag options to blanks, or click blank then click option'

ADELETE ON

BDELETE

CON DELETE

DDELETE FROM

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the ON keyword causes syntax errors.

Using DELETE FROM is incorrect in GRANT statements.

The correct syntax is GRANT DELETE ON orders TO mike;. The keyword ON must come after the permission.

4fill in blank

hard

Fill both blanks to grant INSERT and UPDATE permissions on 'customers' to user 'emma'.

PostgreSQL

GRANT [1], [2] ON customers TO emma;

Drag options to blanks, or click blank then click option'

AINSERT

BSELECT

CUPDATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Granting SELECT instead of INSERT does not allow adding data.

Forgetting to separate permissions with a comma causes errors.

To allow adding and modifying data, grant INSERT and UPDATE permissions.

5fill in blank

hard

Fill all three blanks to revoke SELECT permission on 'sales' from user 'dave' and grant him INSERT permission.

PostgreSQL

REVOKE [1] ON sales FROM dave; GRANT [2] ON sales TO [3];

Drag options to blanks, or click blank then click option'

ASELECT

BINSERT

Cdave

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the order of REVOKE and GRANT.

Using wrong user name or permission names.

This code first removes SELECT permission, then gives INSERT permission to user dave.

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