Column-level permissions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What are column-level permissions in a database?

Column-level permissions control access to specific columns in a table, allowing users to read or modify only certain columns instead of the entire table.

beginner

How do you grant SELECT permission on specific columns in PostgreSQL?

Use the GRANT command with the column names listed after the table name, for example: GRANT SELECT (column1, column2) ON table_name TO user_name;

intermediate

Why might you use column-level permissions instead of table-level permissions?

To protect sensitive data by restricting access to certain columns while still allowing access to other parts of the table.

intermediate

Can you update specific columns using column-level permissions in PostgreSQL?

Yes, you can grant UPDATE permission on specific columns by specifying them in the GRANT statement, like: GRANT UPDATE (column1) ON table_name TO user_name;

intermediate

What happens if a user has SELECT permission on a table but not on a specific column?

The user can query the table but will not see data from the restricted column; attempts to access it will result in an error.

Which SQL command is used to give column-level SELECT permission in PostgreSQL?

A CREATE PERMISSION ON column_name FOR user_name;

B GRANT SELECT (column_name) ON table_name TO user_name;

C ALTER TABLE table_name ADD PERMISSION column_name;

D SET PERMISSION SELECT ON column_name TO user_name;

The GRANT command with column names specifies column-level permissions in PostgreSQL.

If a user has UPDATE permission on only one column, what can they do?

A Delete rows from the table.

B Update any column in the table.

C Update only that specific column in the table.

D Insert new rows into the table.

Column-level UPDATE permission restricts updates to only the specified columns.

What is a main benefit of using column-level permissions?

A Allows fine control over sensitive data access.

B Improves query speed.

C Automatically backs up data.

D Simplifies table structure.

Column-level permissions help protect sensitive data by limiting access to specific columns.

Which of the following is NOT true about column-level permissions?

A They can restrict INSERT access on columns.

B They can restrict UPDATE access on columns.

C They can restrict SELECT access on columns.

D They can restrict DELETE access on columns.

DELETE permission applies to rows, not individual columns.

How do you revoke column-level SELECT permission in PostgreSQL?

A REVOKE SELECT (column_name) ON table_name FROM user_name;

B DROP PERMISSION ON column_name FROM user_name;

C REMOVE SELECT ON column_name FROM user_name;

D ALTER USER user_name REVOKE SELECT ON column_name;

REVOKE with column names removes column-level permissions.

Explain what column-level permissions are and why they are useful in databases.

Think about how you might hide some information but show other parts of a table.

Describe how to grant and revoke SELECT permission on specific columns in PostgreSQL.

Focus on the GRANT and REVOKE commands with column lists.