What does it mean to grant a privilege to PUBLIC in a relational database, and why can revoking a privilege from a specific user still leave that user able to run the query?
answer
- PUBLIC = every role, including future ones
- separate additive edge, survives an individual revoke
- engines ship default PUBLIC grants -- trim them
- revoke from PUBLIC, then grant explicitly
- invisible in per-user grant reports
basics
~20 sPUBLIC is a pseudo-role meaning every role in the database, including ones created later. A grant to PUBLIC is a separate, additive source of privilege, so revoking from an individual removes only that individual's grant while the PUBLIC one still applies to everyone.
solid answer
~50 sPUBLIC is not a role you create; it is a built-in stand-in for 'every role, present and future'. Granting SELECT on a table to PUBLIC means every account can read it, and any account created tomorrow can too, with no further action. Because privileges are additive and come from several independent sources -- direct grants, role memberships, and PUBLIC -- removing one source does not remove the others. Revoking from a user when the privilege is also held by PUBLIC changes nothing observable, which is a common cause of 'I revoked it and they still have access'. To actually close it you revoke from PUBLIC (and then grant explicitly to the roles that should keep it). Hardening matters here because engines ship with default PUBLIC grants -- typically things like the ability to connect to a database, use a default schema, or execute built-in functions. A locked-down deployment reviews and trims those rather than assuming the defaults are restrictive.
code
sql · 3 linesREVOKE SELECT ON app.orders FROM analyst; -- no visible effect if PUBLIC holds it
REVOKE SELECT ON app.orders FROM PUBLIC;
GRANT SELECT ON app.orders TO reporting; -- re-grant to the roles that should keep itgo deeper
Know PUBLIC means every role including future ones, and that it is a separate grant an individual revoke does not touch.
Add that engines ship default PUBLIC grants and that hardening means revoking from PUBLIC and re-granting explicitly.
Fold PUBLIC into a source-aware diagnostic routine and into periodic access review and post-upgrade checks.
Define the baseline: which PUBLIC grants are permitted by policy, how new objects get their grants, and how the review is automated.
## What PUBLIC is Every SQL engine has a special grantee meaning 'all roles'. It is not stored as a membership; it is a wildcard evaluated during privilege checks. Two properties follow directly: 1. **It is retroactive to the future.** A role created next month automatically holds every privilege that was granted to PUBLIC, because the check asks 'is this privilege granted to PUBLIC?' rather than enumerating members at grant time. 2. **It is a separate edge.** A grant to PUBLIC is not a grant to Alice. Revoking from Alice deletes the Alice edge and leaves the PUBLIC edge untouched, so her effective privileges are unchanged. ## Why this trips people The usual sequence is: someone reports that an account can see data it should not; an administrator revokes the privilege from that account; the access persists; and the administrator concludes the revoke did not work. The revoke worked perfectly -- it removed an edge that was not carrying the access. Effective privilege is the union of direct grants, role-inherited grants, PUBLIC grants and owner-implicit rights, so the correct first move is always to determine *which source* is granting access before removing anything. ## Default PUBLIC grants Databases ship with usability-oriented defaults, and those defaults are PUBLIC grants. Depending on the engine and version these can include the right to connect to a database, to use a default schema, to execute built-in or user-defined functions, or to read some catalog metadata. None of them are wrong by design -- they make a fresh database usable -- but a security baseline should enumerate and trim them, because they are exactly the privileges nobody remembers to review. The historical example most people cite is a default writable shared schema: any role could create objects there, which is both a data-integrity and a name-resolution hazard, since a locally created object can shadow a name another session expected to resolve elsewhere. ## Where a PUBLIC grant is legitimate PUBLIC is not automatically a defect. Reasonable uses include reference data every account should read (a currency table, a static lookup), or metadata views intended for everyone. The test is: would you be comfortable if a brand-new account, created without your involvement, held this privilege on day one? For reference data, yes. For anything with customer data in it, no. ## Practical hardening pattern 1. Enumerate existing PUBLIC grants from the catalog for every object type -- tables, views, schemas, functions, the database itself. 2. Decide per grant: keep (reference/utility) or revoke. 3. Revoke from PUBLIC, then grant explicitly to the roles that legitimately need it. Do this in one migration so nothing loses access mid-flight. 4. Add PUBLIC to whatever periodic access review exists, and to the checklist for new objects, since it is invisible in per-user reports. ## Interview framing A good answer names PUBLIC as a pseudo-role covering future roles, explains additivity as the reason individual revokes appear ineffective, and mentions default PUBLIC grants as a hardening target. A great answer adds the diagnostic habit: query effective privileges and their sources before revoking anything.
- Is granting to PUBLIC ever the right choice?Yes, for data or utilities that every account may legitimately use: static reference and lookup tables, shared metadata views, or helper functions with no sensitive input. The test is whether you would accept a brand-new account, created without your review, holding that privilege from its first second of existence. If the answer is no, grant to a named role instead.
- How do you find PUBLIC grants that a security review would otherwise miss?Query the catalog's privilege metadata for the PUBLIC grantee across all object types -- databases, schemas, tables, views, sequences and routines -- rather than iterating per user, because per-user reports never show it. Include the engine's shipped defaults in that inventory, since they were never granted by anyone on your team and are easy to overlook, and re-run the check as part of periodic access review and after major version upgrades.
saying these in an interview costs you the question
- Thinking PUBLIC means 'publicly reachable over the network' rather than 'every database role'
- Assuming a revoke from a user removes access that comes from PUBLIC
- Believing PUBLIC covers only roles that existed when the grant was made
- Assuming a fresh database has no PUBLIC grants at all
- Auditing access user by user and never checking PUBLIC