Authrisation

Privileges
The privileges are used in SQL to protect data. It is fine-grained and specify how a user can interact with a given relation(table) and even limit which columns in relation the user can access or modify.

SELECT ON R or SELECT (A1,A2,…,Am) ON R
• Read privileges
• INSERT ON R or INSERT (A1,A2,…,Am) ON R
• Write privileges
• UPDATE ON R or UPDATE (A1,A2,…,Am) ON R
• Modify privileges
• DELETE ON R
• Delete privileges
• Only a complete relation (table) can be deleted, not individual attributes.

Views and Authorisation
To limit the part of the database that will be affected by the change in privileges, we create a view that only includes people from Ireland.
CREATE VIEW Irish AS
SELECT * FROM Person
WHERE countryID IN
(SELECT countryID FROM Country
WHERE Name = ’Ireland’);
• To allow a user to read the records only from Ireland, we would grant the SELECT privileges on the relation/view called Irish.
SELECT * ON Irish

Granting Privileges
GRANT privileges ON table TO users
[WITH GRANT OPTION];
Revoking Privileges
REVOKE privileges ON table FROM users
[CASCADE | RESTRICT];