Schema-level access control in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant usage on a schema to a user.

PostgreSQL

GRANT [1] ON SCHEMA sales TO alice;

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

AUPDATE

BSELECT

CINSERT

DUSAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using SELECT instead of USAGE on schema.

Trying to grant data privileges on schema level.

The USAGE privilege allows a user to access objects in the schema.

2fill in blank

medium

Complete the code to revoke schema usage from a role.

PostgreSQL

REVOKE [1] ON SCHEMA marketing FROM marketing_team;

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

ASELECT

BUSAGE

CALL

DCREATE

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking SELECT instead of USAGE on schema.

Using ALL which affects other privileges too.

Revoking USAGE removes the ability to access the schema.

3fill in blank

hard

Fix the error in the code to grant create privilege on a schema.

PostgreSQL

GRANT [1] ON SCHEMA hr TO bob;

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

AINSERT

BUSAGE

CCREATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using USAGE instead of CREATE to allow object creation.

Confusing data privileges like INSERT with schema privileges.

The CREATE privilege allows creating new objects in the schema.

4fill in blank

hard

Fill both blanks to grant all privileges on a schema to a user.

PostgreSQL

GRANT [1] ON SCHEMA [2] TO charlie;

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

AALL PRIVILEGES

Bsales

Cmarketing

DUSAGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using USAGE instead of ALL PRIVILEGES for full rights.

Mixing up schema names.

ALL PRIVILEGES grants all schema-level rights, and sales is the schema name.

5fill in blank

hard

Fill all three blanks to revoke create and usage privileges on a schema from a role.

PostgreSQL

REVOKE [1], [2] ON SCHEMA [3] FROM interns;

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

ACREATE

BUSAGE

Cdevelopment

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Revoking only one privilege instead of both.

Using wrong schema name.

Revoking CREATE and USAGE removes ability to create objects and access the development schema.