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.

Practice

1. What does the USAGE privilege on a schema in PostgreSQL allow a user to do?

easy

A. Access objects within the schema without creating new ones

B. Create new tables and objects inside the schema

C. Delete the schema entirely

D. Modify data in tables outside the schema

Solution

1. Step 1: Understand USAGE privilege meaning

   The USAGE privilege allows a user to access objects inside the schema, such as selecting data from tables, but does not allow creating new objects.

2. Step 2: Differentiate from CREATE privilege

   The CREATE privilege is needed to add new tables or other objects. USAGE alone does not grant this ability.

3. Final Answer:

   Access objects within the schema without creating new ones -> Option A

4. Quick Check:

   USAGE = access only [OK]

Hint: USAGE lets you use, CREATE lets you add [OK]

Common Mistakes:

• Confusing USAGE with CREATE privilege
• Thinking USAGE allows schema deletion
• Assuming USAGE grants data modification outside schema

2. Which of the following is the correct syntax to grant CREATE privilege on a schema named sales to user alice?

easy

A. GRANT CREATE ON sales TO alice;

B. GRANT CREATE TO alice ON SCHEMA sales;

C. GRANT CREATE ON SCHEMA sales TO alice;

D. GRANT CREATE ON DATABASE sales TO alice;

Solution

1. Step 1: Identify correct GRANT syntax for schema

   In PostgreSQL, to grant privileges on a schema, the syntax is: GRANT privilege ON SCHEMA schema_name TO user;

2. Step 2: Match syntax with options

   GRANT CREATE ON SCHEMA sales TO alice; matches this syntax exactly: GRANT CREATE ON SCHEMA sales TO alice;

3. Final Answer:

   GRANT CREATE ON SCHEMA sales TO alice; -> Option C

4. Quick Check:

   GRANT ... ON SCHEMA ... TO ... [OK]

Hint: Use 'ON SCHEMA' when granting schema privileges [OK]

Common Mistakes:

• Omitting 'SCHEMA' keyword
• Using 'ON DATABASE' instead of 'ON SCHEMA'
• Placing TO clause incorrectly

3. Given the commands below, what will be the result of SELECT * FROM sales.orders; when run by user bob?

GRANT USAGE ON SCHEMA sales TO bob;
REVOKE CREATE ON SCHEMA sales FROM bob;

medium

A. Query runs but returns no rows

B. Error: permission denied for schema sales

C. Error: relation sales.orders does not exist

D. Query runs successfully and returns rows from sales.orders

Solution

1. Step 1: Analyze granted privileges

   User bob has USAGE on schema sales, so can access objects inside it. CREATE privilege is revoked, so bob cannot create new objects but can read existing ones.

2. Step 2: Understand effect on SELECT query

   Since bob has USAGE, SELECT on sales.orders will work if bob has SELECT privilege on the table (assumed). The REVOKE of CREATE does not affect SELECT.

3. Final Answer:

   Query runs successfully and returns rows from sales.orders -> Option D

4. Quick Check:

   USAGE allows access, REVOKE CREATE blocks creation only [OK]

Hint: USAGE lets you read; CREATE controls adding objects [OK]

Common Mistakes:

• Confusing CREATE with SELECT privilege
• Assuming REVOKE CREATE blocks all access
• Ignoring USAGE privilege effect

4. You want to allow user carol to create tables in schema inventory, but she gets an error: permission denied for schema inventory. Which command fixes this?

medium

A. GRANT CREATE ON SCHEMA inventory TO carol;

B. GRANT USAGE ON SCHEMA inventory TO carol;

C. GRANT ALL PRIVILEGES ON SCHEMA inventory TO carol;

D. REVOKE USAGE ON SCHEMA inventory FROM carol;

Solution

1. Step 1: Understand error cause

   To create tables, user needs both USAGE and CREATE privileges on the schema. Without USAGE, permission denied error occurs.

2. Step 2: Grant missing privilege

   Granting USAGE on schema inventory to carol allows her to access the schema and create tables if CREATE is already granted.

3. Final Answer:

   GRANT USAGE ON SCHEMA inventory TO carol; -> Option B

4. Quick Check:

   USAGE needed before CREATE works [OK]

Hint: Grant USAGE before CREATE to avoid permission errors [OK]

Common Mistakes:

• Granting CREATE without USAGE privilege
• Revoking instead of granting privileges
• Assuming ALL PRIVILEGES always needed

5. You want to restrict user dave so he can only create objects in schema projects but cannot access any existing objects. Which combination of privileges achieves this?

hard

A. GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave;

B. GRANT USAGE ON SCHEMA projects TO dave; REVOKE CREATE ON SCHEMA projects FROM dave;

C. GRANT ALL ON SCHEMA projects TO dave;

D. REVOKE ALL ON SCHEMA projects FROM dave;

Solution

1. Step 1: Understand privilege effects

   CREATE allows adding new objects. USAGE allows accessing existing objects. To restrict access but allow creation, grant CREATE and revoke USAGE.

2. Step 2: Apply correct commands

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; grants CREATE and revokes USAGE, so dave can create but not access existing objects.

3. Final Answer:

   GRANT CREATE ON SCHEMA projects TO dave; REVOKE USAGE ON SCHEMA projects FROM dave; -> Option A

4. Quick Check:

   Create without usage blocks access [OK]

Hint: Grant CREATE, revoke USAGE to allow creation only [OK]

Common Mistakes:

• Granting USAGE allows access to existing objects
• Revoking CREATE disables creation
• Granting ALL gives too many rights