SQL Security

1. SQL Security Overview of SQL Security Commands BACS 485 – SQL Security

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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