In SQL, what exact statements let a service account write to the orders table, and what takes that access back?
answer
- three slots in a fixed order
- no single write privilege on a table
- ON the object, then the grantee
- the reverse swaps one preposition
- PUBLIC fits the grantee slot
basics
~20 sGRANT 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 sThe 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 linesGRANT 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
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.
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.
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.
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.