Lecture 10: SQL Data Control Language Commands Revision Flashcards
What is Data control language command
DCL allows users to grant object privileges and system privileges to other database users
object privileges are actions that a user is permitted
List all object privileges
select insert update delete references
Why are insert and update special?
insert and update privilege can be restricted to specific columns of the table, allowing changes to these columns but disallowing changes to any other columns
DCL consists of two commands
GRANT
to grant object privileges to other database users
REVOKE
to revoke previously granted object privileges from database users
General Syntax of Grant Commadn
GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorisationIDList | PUBLIC}
[WITH GRANT OPTION]
PrivilegeList: is one or more object privileges separated by commas
All privileges: grant all object privileges to a database user
What is an authorisation ID?
a valid database user name
e.g. root user in MySQL
what is objectName
objectName can be a base table or view
WITH GRANT OPTION
allows privileges to be passed on
eg of grant all privileges
grant all privileges
on staff
to manager with grant option
granting specific privileges
grant select, update (salary)
on staff
to personnel, director
grant specific privileges to public
grant select
on branch
to public
What is a revoke command
revoke takes away privileges granted with GRANT
syntax for REVOKE
revoke [grant option for] {PrivilegeList | ALL Privileges} ON objectName
From {authorisationIDList | Public | Cascade]
How does revoke fail?
REVOKE fails if it results in an abandoned object, such as a view, unless the CASCADE keyword has been specified
e.g. REVOKE specific privileges from PUBLIC
REVOKE SELECT
ON Branch
FROM PUBLIC
REVOKE ALL PRIVILEGES
ON Staff
FROM Director
