79781217

Date: 2025-10-02 18:56:01
Score: 2.5
Natty:
Report link

TO allow a role to read from the information_schema and get results about a table 3 things have to happen:

  1. grant usage on the database to the role

  2. grant usage on the schema the table is in to the role.

  3. grant some access to the table to the role. Normally, this could be "SELECT" but that would give read access to all production tables. However, granting REFERENCES to the table will also work, I think. the main purpose of REFERENCES is to allow a role to set up a reference - a FK relationship to another table ( think referencing INVOICE_NUMBER in INVOICE_HEADER from INVOICE_DETAILS to be sure the parent table has the data) The REFERENCE does not allow the role to CRUD any of the data other than "background" reference. REFERENCE seems better for PRODUCTION data than SELECT.

    Any thoughts or caveats welcome

Reasons:
  • Blacklisted phrase (1.5): Any thoughts
  • Long answer (-0.5):
  • No code block (0.5):
  • Low reputation (1):
Posted by: James Brooks