Transcript 486 Advanced SQL - University of Northern Colorado

SQL Security
Overview of SQL Security
Commands
BACS 485 – SQL Security
Grant Command




The GRANT command is the SQL mechanism
used to give privileges to other users. (It’s in
the DCL group.)
A user must hold a privilege before it can be
granted to others.
The owner of an object (the one who created
it) automatically holds all privilege on that
object.
Grant statements apply to system-level and
object-level privileges and roles.
BACS 485 – SQL Security
GRANT Syntax
GRANT {privileges | role | ALL}
[ON tables/views]
TO { user | role | PUBLIC}
[WITH GRANT/ADMIN OPTION];
Special Keywords:
ALL – grants all privileges
PUBLIC – grants privileges to all users in system
WITH GRANT OPTION – allows the granted privileges to be
passed on to other users.
WITH ADMIN OPTION – allows the granted roles to be passed
on to other users.
BACS 485 – SQL Security
Grant Examples
1) Make everyone a DBA (not a good idea).
GRANT ALL TO PUBLIC WITH GRANT OPTION;
2) Give SELECT access to everyone on table SP
GRANT SELECT ON SP TO PUBLIC;
3) Give SELECT, and INSERT to users Smith and Brown on
EMPLOYEE.
GRANT SELECT, INSERT ON EMPLOYEE TO SMITH, BROWN;
BACS 485 – SQL Security
Grant Examples
4) Give user Gordon all access to a horizontal subset (view) of the
EMPLOYEE table. Note, the CREATE VIEW is not part of the
GRANT command.
CREATE VIEW GORDON_VIEW AS
SELECT *
FROM EMPLOYEE
WHERE OCCUPATION = "PROGRAMMER";
GRANT ALL ON GORDON_VIEW TO SUSAN;
BACS 485 – SQL Security
Grant Examples
5) Give all privileges that you have on the EMPLOYEE
table to user Fran. You also give Fran the ability to
pass any of these privileges on to others.
GRANT ALL ON EMPLOYEE
TO Fran
WITH GRANT OPTION;
BACS 485 – SQL Security
Grant Examples
7) Give the Executive role to user Thomas and allow
Thomas to pass on this role on to others.
GRANT Executive
TO Thomas
WITH ADMIN OPTION;
Note: The ADMIN option is similar to the GRANT option
except it is made to work with roles.
BACS 485 – SQL Security
Revoke



The REVOKE command is the opposite
of the GRANT in that it takes privileges
away.
You can only revoke privileges that a
user currently has.
You can only revoke privilege that you
gave the user (unless you have REVOKE
ALL privilege).
BACS 485 – SQL Security
Revoke Syntax
REVOKE {privileges | role | ALL}
[ON tables/views]
FROM {users | role | PUBLIC};
1) Remove the SELECT access from everyone on the SP
table.
REVOKE SELECT ON SP FROM PUBLIC;
BACS 485 – SQL Security
Revoke Examples
2) Remove the SELECT, and INSERT privilege on the
EMPLOYEE table from users Smith and Brown.
REVOKE SELECT, INSERT ON EMPLOYEE FROM
SMITH, BROWN;
3) Remove the Controller role from user Henry.
REVOKE Controller FROM Henry;
BACS 485 – SQL Security
Revoke Examples
5) This example gives user Tom all available privileges
on the Bonus table. It then takes away the Delete
privilege. While this is somewhat confusing, it has
the effect of giving Tom all privilege except DELETE
(without all the extra typing).
GRANT ALL ON Bonus TO Tom;
REVOKE DELETE ON Bonus FROM Tom;
BACS 485 – SQL Security