skip to content

questions

6

In SQL, what exact statements let a service account write to the orders table, and what takes that access back?

level: juniorimportance: must knowfreq 45%

answer

  1. three slots in a fixed order
  2. no single write privilege on a table
  3. ON the object, then the grantee
  4. the reverse swaps one preposition
  5. PUBLIC fits the grantee slot

basics

~20 s

GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders; gives the write access, and REVOKE INSERT, UPDATE, DELETE ON orders FROM svc_orders; removes it. SQL has no single write privilege, so the statement lists each operation.

solid answer

~50 s

The shape is privilege list, `ON` object, `TO` grantee: `GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders;`. The reverse keeps that shape with `FROM` in place of `TO`: `REVOKE INSERT, UPDATE, DELETE ON orders FROM svc_orders;`. Tables have no write privilege, so you name each operation, and the request decides the list: drop `DELETE` if the service only inserts and updates. `ALL PRIVILEGES` is the wrong shortcut because it also hands over rights nobody asked for, such as `TRUNCATE` in PostgreSQL. Two surprises follow. `UPDATE orders SET status = 'paid' WHERE id = 1` reads `id`, so in PostgreSQL and MySQL it also needs `SELECT` on that column. And in PostgreSQL a table grant does not reach the sequence behind a `SERIAL` column, so inserts drawing from it need `GRANT USAGE ON SEQUENCE` as well. Writing `PUBLIC` as the grantee means every role.

code

sql · 5 lines
sql
GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders;
GRANT SELECT (id) ON orders TO svc_orders;
GRANT USAGE ON SEQUENCE orders_id_seq TO svc_orders;

REVOKE DELETE ON orders FROM svc_orders;

go deeper

for a junior

Recall the three slots in order: privilege list, ON object, TO grantee, with FROM for REVOKE. Write the statement out by hand and list INSERT, UPDATE and DELETE separately.

for a middle

Explain what the first real statement needs beyond the grant: a column read for a WHERE clause, and in PostgreSQL the sequence behind a SERIAL column. Say that a GRANT adds and never narrows.

for a senior

Show that you turn a loose request into the narrowest list and refuse ALL PRIVILEGES as a shortcut. Mention what you would check first when a freshly granted service still gets refused.

for a principal

Treat the statement as the record of a decision: named operations in a reviewed script read clearly in a later audit, while ALL PRIVILEGES hides what was intended and means more each time an engine adds a privilege.

`GRANT` and `REVOKE` are the SQL statements that hand out and take back access. A **privilege** is the right to perform one kind of operation on one object, such as reading a table. A **grantee** is the account or role that receives it. An interviewer who asks for the exact statement wants the slots in the right order and a privilege list that matches the request, nothing wider. ## The shape of both statements Both statements have three slots, and only one keyword differs between them: ```sql GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders; REVOKE INSERT, UPDATE, DELETE ON orders FROM svc_orders; ``` 1. **Privilege list.** One or more operations, separated by commas. 2. **`ON` object.** What the privileges apply to. For a table the word `TABLE` is optional in PostgreSQL, so `ON orders` and `ON TABLE orders` mean the same thing. 3. **`TO` or `FROM` grantee.** You grant to and revoke from. One or more names, separated by commas. The order is fixed. `GRANT INSERT TO svc_orders ON orders;` uses the right words in an order the grammar rejects, and `REVOKE ... TO` is a syntax error for the same reason. ## Turning a request into a privilege list "The service must write to `orders`" is not yet a statement, because a table has no write privilege. You translate the request into operations: - Creating rows needs `INSERT`. - Changing rows needs `UPDATE`. - Removing rows needs `DELETE`. Grant only the ones the request names. `GRANT ALL PRIVILEGES ON orders TO svc_orders;` is shorter and wrong for this request, because it grants every privilege available for that type of object. In PostgreSQL's table form that list includes `TRUNCATE`, `REFERENCES`, `TRIGGER` and `MAINTAIN`, none of which a service writing rows asked for. A `GRANT` adds. PostgreSQL's reference says new privileges are added to those already granted, so running `GRANT SELECT` after `GRANT INSERT` leaves both in place. A smaller grant never shrinks access; only a `REVOKE` does that, which is why the reverse statement has to name what is being taken away. The reverse can be as narrow as the grant: `REVOKE DELETE ON orders FROM svc_orders;` takes one operation away and leaves `INSERT` and `UPDATE` in place, with no need to grant anything again. ## Two surprises when the service runs A grant that looks complete can still fail on the first real statement: - **Reading while writing.** `UPDATE orders SET status = 'paid' WHERE id = 1` reads `id` to find the row. In PostgreSQL and MySQL that read needs `SELECT` on the column, so `UPDATE` alone is not enough. `GRANT SELECT (id) ON orders TO svc_orders;` covers it without opening the other columns. - **Sequences.** PostgreSQL's reference states that a table grant does not extend to sequences the table uses, including those tied to `SERIAL` columns. If `orders.id` is `SERIAL`, an `INSERT` relying on the default also needs `GRANT USAGE ON SEQUENCE orders_id_seq TO svc_orders;`. ## PUBLIC in the grantee slot `PUBLIC` is a keyword, not a role you create. `GRANT SELECT ON price_list TO PUBLIC;` gives the privilege to every role, and PostgreSQL's reference is explicit that this includes roles created later. `REVOKE SELECT ON price_list FROM PUBLIC;` is the reverse. PostgreSQL, SQL Server and Oracle all accept it as a grantee. What it means for a user who also holds a grant of their own is a question of how privileges combine, which belongs with the engine's access-control semantics, not the statement's syntax. ## Where the spelling moves between engines The three slots travel well; what fills them does not always: | Engine | Same request | |---|---| | PostgreSQL | `GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders;` | | MySQL | `GRANT INSERT, UPDATE, DELETE ON shop.orders TO 'svc_orders'@'%';` | | SQL Server | `GRANT INSERT, UPDATE, DELETE ON dbo.orders TO svc_orders;` | | Oracle | `GRANT INSERT, UPDATE, DELETE ON orders TO svc_orders;` | - MySQL names an account with a host part, and its object can carry the database name. - SQLite has no `GRANT` or `REVOKE` at all, because access is whatever the database file's permissions allow. - PostgreSQL accepts several tables in one statement (`ON orders, order_items`), but its reference notes that the SQL standard does not, so a portable script writes one statement per table.

  • In SQL, does a second GRANT on the same table replace what the first one gave?
    No. PostgreSQL's reference says the privileges are added to those already granted, so `GRANT SELECT ON orders TO svc_orders;` after a write grant leaves the account with both. To end up with less, run a `REVOKE` naming what should go.
  • The service holds UPDATE on orders, yet UPDATE orders SET status = 'paid' WHERE id = 1 is refused. What statement is missing?
    The `WHERE` clause reads `id`, and in PostgreSQL and MySQL reading a column needs `SELECT` on it. Add `GRANT SELECT (id) ON orders TO svc_orders;`, which allows that lookup and leaves the other columns closed. MySQL names the account with its host part, as in `'svc_orders'@'%'`.
  • Can one SQL GRANT statement name several tables?
    In PostgreSQL, yes: `GRANT SELECT ON orders, order_items TO reporting;`. PostgreSQL's reference notes that the SQL standard does not support more than one object per command, so a script meant to travel between engines writes one statement per table.

  • Looks for a single write privilege on a table instead of listing INSERT, UPDATE and DELETE.
  • Writes REVOKE with TO, as if it mirrored GRANT word for word.
  • Thinks a second, smaller GRANT replaces the privileges from the first one.
  • Reaches for GRANT ALL PRIVILEGES when the request names only row writes.
  • Believes that in PostgreSQL a table grant also covers the sequence behind its SERIAL column.
  • Treats PUBLIC as a role that has to be created before it can be used.
open as a page

In SQL, what statements create a reporting role, give it read access to the orders table, and add the user alice?

level: juniorimportance: should knowfreq 40%

basics

~10 s

CREATE ROLE reporting; GRANT SELECT ON orders TO reporting; GRANT reporting TO alice; The last form has no ON clause, because it grants membership in a role instead of a privilege on an object.

open as a page

In SQL, which REVOKE keyword makes it fail when team_lead has passed SELECT on orders to others, and what are both forms?

level: middleimportance: should knowfreq 28%

basics

~10 s

RESTRICT makes it fail: REVOKE SELECT ON orders FROM team_lead RESTRICT;. CASCADE also removes what team_lead passed on: REVOKE SELECT ON orders FROM team_lead CASCADE;. PostgreSQL assumes RESTRICT when neither is written.

open as a page

In SQL, what exact GRANT gives an analyst read access to only the email and signup_date columns of a customers table?

level: middleimportance: should knowfreq 38%

basics

~20 s

GRANT SELECT (email, signup_date) ON customers TO analyst; puts the column list in parentheses right after the privilege. The analyst can select those columns by name, but SELECT * is refused because it asks for every column.

open as a page

In SQL, how do you write a GRANT that lets team_lead pass on SELECT on orders, and a REVOKE that removes only that ability?

level: middleimportance: should knowfreq 33%

basics

~20 s

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION; adds the right to pass the privilege on. REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead; removes that right and leaves team_lead's own SELECT in place.

open as a page

In SQL, why does a GRANT script with GRANT SELECT ON orders TO app port between engines while GRANT CREATE TABLE TO app often fails?

level: seniorimportance: nice to knowfreq 25%

basics

~20 s

SELECT ON orders names a privilege on an existing object, a shape engines share. Creating tables has no such object, so each engine scoped it differently: GRANT CREATE TABLE TO app runs on Oracle and SQL Server, not PostgreSQL.

open as a page