skip to content

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%

answer

  1. one names an object that exists
  2. nothing for ON to point at yet
  3. each engine scoped that right itself
  4. same word, different slot, different scope

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.

solid answer

~50 s

`GRANT SELECT ON orders TO app` names a privilege on an object that exists, and that shape is common ground across engines, apart from how each names the object and the grantee. A right to create tables concerns something that does not exist yet, so `ON` has nothing to name, and each engine decided for itself where that authority lives. Oracle and SQL Server write `GRANT CREATE TABLE TO app;`, and SQL Server also wants `ALTER` on the target schema. PostgreSQL attaches it to a schema: `GRANT CREATE ON SCHEMA sales TO app;`, and its reference calls privileges on databases and schemas extensions to the standard. MySQL writes `GRANT CREATE ON sales.* TO 'app'@'%';`. These are not synonyms: the scope differs as well as the words. So treat that part of a grants script as per-engine, and test it on each engine you target.

code

sql · 4 lines
sql
GRANT SELECT ON orders TO app;
GRANT USAGE, CREATE ON SCHEMA sales TO app;
GRANT CONNECT ON DATABASE shop TO app;
ALTER ROLE app CREATEDB;

go deeper

for a junior

Recall that GRANT SELECT ON a table looks much the same across engines, and that a statement with no ON clause is a warning sign when a script moves between engines.

for a middle

Explain the difference in shape: a privilege on a named object, against a right with nothing yet to name. Give two spellings of letting an account create tables, and say what scope each one has.

for a senior

Diagnose a failed port by sorting lines into object-shaped and everything else, then rewrite the second group by intent. Explain why a line that parses can still grant the wrong scope, and verify by connecting as the account.

for a principal

Decide whether grants live in one file per engine or behind a generating tool, weighing duplicated files against a layer that hides scope differences. Name who owns those files when a new engine is adopted.

An **object privilege** is the right to do something to a named object that already exists, such as reading a table. A **system privilege**, in the loose sense used here, is a right that is not tied to one existing object, such as creating tables or connecting. When a grants script fails after moving to another engine, the failing line is usually in the second group. This answer is about why the spelling diverges, and how to read a script with that in mind. How to choose privileges for an account is a separate design question. ## Why one shape travels `GRANT SELECT ON orders TO app;` fills three slots: a privilege, an object and a grantee. This is the form the SQL standard uses for table privileges, and engines have kept that shape. The differences are around the edges: - MySQL writes the account as `'app'@'%'`, and may qualify the table as `sales.orders`. - SQL Server may qualify the object as `dbo.orders` or `OBJECT::dbo.orders`. - PostgreSQL accepts a list of tables in one statement, which its reference notes the standard does not. ## Why the other does not `CREATE TABLE` concerns an object that does not exist yet, so the `ON` slot has nothing to name. Something still has to say where the right applies, and each engine answered that in its own way. PostgreSQL's reference is candid about its own answer: privileges on databases, tablespaces, schemas, languages and configuration parameters are PostgreSQL extensions. There is no shared form to fall back on. | Request | PostgreSQL | MySQL | SQL Server | Oracle | |---|---|---|---|---| | Read one table | `GRANT SELECT ON orders TO app;` | `GRANT SELECT ON sales.orders TO 'app'@'%';` | `GRANT SELECT ON dbo.orders TO app;` | `GRANT SELECT ON orders TO app;` | | Create tables | `GRANT CREATE ON SCHEMA sales TO app;` | `GRANT CREATE ON sales.* TO 'app'@'%';` | `GRANT CREATE TABLE TO app;` plus `ALTER` on the schema | `GRANT CREATE TABLE TO app;` | | Connect | `GRANT CONNECT ON DATABASE shop TO app;` | Comes with the account | `GRANT CONNECT TO app;` | `GRANT CREATE SESSION TO app;` | ## The words mislead as well as the grammar The diagnosis is harder than a syntax error, because some lines parse on the wrong engine or look equivalent while meaning something else: 1. **The scope differs.** PostgreSQL's `CREATE ON SCHEMA sales` covers one schema and creating new objects there. Oracle's `CREATE TABLE` lets the account create tables in its own schema. 2. **The word `CREATE` differs.** In PostgreSQL's `GRANT CREATE ON DATABASE shop` it means creating schemas in that database, not tables. 3. **The right may not be a `GRANT` at all.** In PostgreSQL, the ability to create databases is a role attribute: `ALTER ROLE app CREATEDB;`. No `GRANT` statement expresses it. 4. **The right may not exist.** PostgreSQL's reference says the right to drop an object or alter its definition is not a grantable privilege, since it is inherent in the owner. MySQL has `GRANT DROP ON sales.* TO 'app'@'%';`, so that line has no direct PostgreSQL translation at all. ## Reading a failing script When a grants script breaks on a new engine: - **Sort the lines first.** Lines of the form privilege, `ON` named table, grantee usually need only name changes. - **Treat the rest as a rewrite.** Any line without `ON`, or with `ON` something other than a table, needs its intent looked up on the new engine. Do not edit the words until it parses, because a line that parses can still grant the wrong scope. - **Check the result, not the exit code.** After the script runs, connect as the account and try the operations it should and should not be able to perform. ## What to do in a codebase - Keep one grants file per engine. A single script with conditionals hides the differences this answer is about. - Keep the role names the same across those files, so the rest of the code does not care. - Run each file against that real engine in a test. A linter that understands one dialect will not catch a line that means something else on another. - `ALL PRIVILEGES` is not a way out. PostgreSQL's reference says it grants what is available for the object's type, so the same words grant different things on different objects. - SQLite has no `GRANT` at all, so a test suite that runs on SQLite never exercises these statements.

  • In PostgreSQL, how do you give an application the right to drop tables, given that MySQL has GRANT DROP?
    No privilege does it. PostgreSQL's reference says the right to drop or alter an object is inherent in the owner and cannot be granted, and that a similar effect comes from membership in the owning role, as in `GRANT sales_owner TO app;`. So the MySQL line needs a different statement, not a new word.
  • Is ALL PRIVILEGES a portable way to avoid naming these rights in a SQL script?
    No. PostgreSQL's reference says it grants all privileges available for the object's type, and that the `PRIVILEGES` word, optional there, is required by strict SQL. So the phrase changes meaning with the object it is applied to, and it does nothing for rights that have no `ON` object.
  • A grants script runs without error after a port from Oracle to another engine, yet the application is refused. Where do you look first?
    At lines that parsed but mean something different, such as a `CREATE` granted on the database when the application needs it on a schema. Connect as the account and run each operation, because the script's exit code says only that the statements were valid.

  • Assumes GRANT CREATE TABLE TO app is standard SQL that every engine accepts.
  • Treats PostgreSQL's GRANT CREATE ON SCHEMA as an exact synonym for Oracle's CREATE TABLE.
  • Reads GRANT CREATE ON DATABASE in PostgreSQL as permission to create tables there.
  • Edits a failing line until it parses, without checking what scope it now grants.
  • Expects every right to have a GRANT, though PostgreSQL's CREATEDB is a role attribute.
  • Relies on tests run on SQLite to exercise grant statements, which it does not have.