Skip to content

Authorisation

Data Access Privileges

  • Read / Select - can read data, but not modify it
  • Insert - can insert data, but not modify existing data
  • Update - allows modification, but not deletion of data
  • Delete - allows deletion of data

Schema Modification Privileges

  • Index - allows the creation and deletion of indices
  • Resources - allows the creation of new relations
  • Alteration - allows addition or deletion of attributes within a relation
  • Drop - allows deletion for relation
  • Reference - allows the user to create a foreign key referencing this table

GRANT / REVOKE

  • the GRANT keyword is used to give some privileges to some user
    • GRANT <privilege list> ON <relation / view> TO <user list>
  • the REVOKE keyword has the opposite function
  • any privilege between select, insert, update, delete, and all can be granted to a user, on a certain table or view
  • the granter must already have the privilege that they are granting
  • the default role is public. it includes all users without a role
  • revoke may contain cascade or restrict at the end, which decides how to handle permission dependency conflicts

### Roles * an SQL role is like a discord server role * CREATE role <name> creates a role * GRANT <role> to <users> grants this role to some users