Role creation and management in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new role named 'reporter'.

PostgreSQL

CREATE ROLE [1];

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

Aadmin

Breporter

Cuser

Dguest

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to specify the role name.

Using reserved keywords as role names.

The CREATE ROLE command followed by the role name creates a new role. Here, 'reporter' is the new role.

2fill in blank

medium

Complete the code to grant login permission to the role 'reporter'.

PostgreSQL

ALTER ROLE reporter [1] LOGIN;

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

AGRANT

BNO

CWITH

DDENY

Attempts:

3 left

💡 Hint

Common Mistakes

Using NO LOGIN which disables login.

Omitting the WITH keyword.

To allow a role to login, use ALTER ROLE role_name WITH LOGIN;. The keyword WITH is required before LOGIN.

3fill in blank

hard

Fix the error in the code to revoke SELECT privilege on table 'sales' from role 'reporter'.

PostgreSQL

REVOKE [1] ON sales FROM reporter;

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

AINSERT

BUPDATE

CDELETE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong privilege like INSERT instead of SELECT.

Misspelling the privilege name.

The REVOKE command removes privileges. To revoke the ability to read data, use SELECT.

4fill in blank

hard

Fill both blanks to grant SELECT and INSERT privileges on table 'employees' to role 'hr'.

PostgreSQL

GRANT [1], [2] ON employees TO hr;

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

ASELECT

BDELETE

CINSERT

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Granting wrong privileges like DELETE or UPDATE.

Mixing up privilege names.

To allow reading and adding data, grant SELECT and INSERT privileges.

5fill in blank

hard

Fill all three blanks to create a role 'manager' with login, password 'secure123', and superuser privileges.

PostgreSQL

CREATE ROLE [1] WITH [2] PASSWORD '[3]' SUPERUSER;

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

Amanager

BLOGIN

Csecure123

DNOSUPERUSER

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOSUPERUSER which denies superuser rights.

Forgetting to put the password in quotes.

This command creates a role named 'manager' that can login with password 'secure123' and has superuser rights.