Authorization in SQL

Lecture 5 · who may do what. Privileges on the data, privileges on the schema, the grant / revoke statements, roles, and the subtleties of granting on a view.

Kinds of authorization

On parts of the database

Privilege	Meaning
Read	allows reading, but not modification of data
Insert	allows insertion of new data, but not modification of existing data
Update	allows modification, but not deletion of data
Delete	allows deletion of data

To modify the schema

Privilege	Meaning
Index	allows creation and deletion of indices
Resources	allows creation of new relations
Alteration	allows addition or deletion of attributes in a relation
Drop	allows deletion of relations

The `grant` statement

grant <privilege list>
on <relation name or view name> to <user list>

<user list> is:

a user-id;
public, which allows all valid users the privilege granted;
a role.

NOTE on granting privilege: granting a privilege on a view does not imply granting any privileges on the underlying relations. And the grantor of the privilege must already hold that privilege on the specified item (or be the database administrator).

Privileges in SQL — the full set of examples

GRANT

-- single privileges
GRANT SELECT ON Student TO user1;
GRANT INSERT ON Student TO user1;
GRANT UPDATE ON Student TO user1;
GRANT DELETE ON Student TO user1;

-- privilege on specific columns (UPDATE only on selected columns)
GRANT UPDATE (cgpa, dept_name) ON Student TO user1;

-- multiple privileges at once
GRANT SELECT, INSERT, UPDATE, DELETE ON Student TO user1;

-- everything on a table
GRANT ALL PRIVILEGES ON Student TO user1;

-- let the user pass the privilege on to others
GRANT SELECT ON Student TO user1 WITH GRANT OPTION;

-- references privilege, needed to create a foreign key
GRANT REFERENCES (dept_name) ON department TO Mariano;

-- MySQL: specify database + table, and flush
GRANT SELECT, INSERT, UPDATE, DELETE ON university.Student TO 'user1'@'localhost';
FLUSH PRIVILEGES;

Revoking authorization

revoke <privilege list>
on <relation name or view name> from <user list>

REVOKE

REVOKE SELECT ON Student FROM user1;
REVOKE SELECT, INSERT, UPDATE, DELETE ON Student FROM user1;
REVOKE ALL PRIVILEGES ON Student FROM user1;

-- column-level
REVOKE UPDATE (cgpa, dept_name) ON Student FROM user1;

-- take away the right to grant to others
REVOKE GRANT OPTION FOR SELECT ON Student FROM user1;

-- MySQL
REVOKE SELECT, INSERT, UPDATE, DELETE ON university.Student FROM 'user1'@'localhost';
FLUSH PRIVILEGES;

Four notes on revoke that examiners love:

<privilege-list> may be all, to revoke every privilege the revokee holds.
If <revokee-list> includes public, all users lose the privilege except those granted it explicitly.
If the same privilege was granted twice by different grantees, the user may retain it after one revocation.
All privileges that depend on the privilege being revoked are also revoked (cascading revocation).

Roles

A role is a named collection of privileges that you can grant to many users. Why roles are used: easier than granting permissions to each user one by one, and it is the standard practice for access control.

roles

-- create a role
CREATE ROLE data_entry;

-- give privileges to the role
GRANT SELECT, INSERT, UPDATE ON Student TO data_entry;

-- assign the role to users
GRANT data_entry TO user1, syeed, user5;

When a new HR officer joins, you grant them one role instead of remembering fifteen individual grants — and when the policy changes you edit the role once.

Authorization on views

view-based authorization

create view geo_instructor as
    ( select *
      from instructor
      where dept_name = 'Geology' );

grant select on geo_instructor to geo_staff;

Suppose a geo_staff member issues select * from geo_instructor;

Two questions to be able to answer:

What if geo_staff has no permission on instructor? — That is fine and it is the whole point: the grant on the view is enough, the query succeeds, and the user still cannot read non-Geology instructors.
What if the creator of the view did not have some permission on instructor? — Then the view cannot hand out what its creator never had; the grant is invalid / the query fails. You cannot grant a privilege you do not hold.

This is the classic row-level security pattern: define a view that filters the rows a group may see, grant on the view, and never grant on the base table.

Applied in the project: Task 2 — role-based access control → · Language families: DDL · DML · DCL · TCL →