1 / 49

Access control

Access control. Access control. GRANT statement. Access control. GRANT statement GRANT statement may be used by a user to authorise various kinds of access to his/her tables by another user or class of users. Access control. Syntax GRANT. ALL <list of privileges>. Access control.

jaimie
Download Presentation

Access control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Access control

  2. Access control • GRANT statement

  3. Access control • GRANT statement GRANT statement may be used by a user to authorise various kinds of access to his/her tables by another user or class of users

  4. Access control • Syntax GRANT ALL <list of privileges>

  5. Access control • Syntax GRANT ON ALL <list of privileges> <table name> <view name>

  6. Access control • Syntax GRANT ON TO ALL <list of privileges> <table name> <view name> PUBLIC <list of users>

  7. Access control • Syntax GRANT ON TO[WITH GRANT OPTION]; ALL <list of privileges> <table name> <view name> PUBLIC <list of users>

  8. Access control • Privileges

  9. Access control • Privileges • SELECT Grantee is allowed to select data from a table determined by a grantor

  10. Access control • Privileges • SELECT Grantee is allowed to selected data from a table determined by a grantor • DELETE Grantee is allowed to delete data from a table determined by a grantor

  11. Access control • Privileges • SELECT Grantee is allowed to selected data from a table determined by a grantor • DELETE Grantee is allowed to delete data from a table determined by a grantor • INSERT Grantee is allowed to insert data into a table determined by a grantor

  12. Access control • Privileges • SELECT Grantee is allowed to selected data from a table determined by a grantor • DELETE Grantee is allowed to delete data from a table determined by a grantor • INSERT Grantee is allowed to insert data into a table determined by a grantor

  13. Access control • Privileges • UPDATE [<list of columns>] Grantee is allowed to update the columns from a table determined by a grantor

  14. Access control • Privileges • UPDATE [<list of columns>] Grantee is allowed to update the columns from a table determined by a grantor • REFERENCES [<list of columns] Grantee is allowed to reference the columns from a table specified by a grantor

  15. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz;

  16. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz; privilege

  17. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz; privilege table (view) name

  18. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz; privilege user table (view) name

  19. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz; Then user Janusz can say: SELECT * FROM Scott.Supplier;

  20. Access control • Granting privileges User Scott says: GRANT SELECT ON Supplier TO Janusz; Then user Janusz can say: SELECT * FROM Scott.Supplier; user.table

  21. Access control • Granting privileges User Janusz says: CREATE SYNONYM Ssupplier FOR Scott.Supplier;

  22. Access control • Granting privileges User Janusz says: CREATE SYNONYM Ssupplier FOR Scott.Supplier; Then user Janusz can say: SELECT * FROM Ssupplier;

  23. Access control • Granting privileges User Scott says: GRANT UPDATE pname, price ON Part TO Janusz;

  24. Access control • Granting privileges User Scott says: GRANT UPDATE pname, price ON Part TO Janusz; Then user Janusz can say: UPDATE Scott.Part SET price = price + 10 WHERE pname =‘bolt’;

  25. Access control • Granting privileges User Scott says: GRANT ALL ON Supplier TO PUBLIC;

  26. Access control • Granting privileges User Scott says: GRANT ALL ON Supplier TO PUBLIC; Then anyone can say: SELECT * FROM Scott.Supplier; UPDATE Scott.Part SET price = price + 10 WHERE pname =‘bolt’; INSERT INTO Scott.Supplier VALUES( ... ); DELETE FROM Scott.Supplier;

  27. Access control • Granting privileges User Scott says: GRANT REFERENCE s# ON Supplier TO Janusz;

  28. Access control • Granting privileges User Scott says: GRANT REFERENCE s# ON Supplier TO Janusz; Then user Janusz can say: CREATE TABLE MySP( s# … CONSTRAINT MySP_fkey FOREIGN KEY(s#) REFERENCES Scott.SP(s#);

  29. Access control • Granting privileges User Scott says: GRANT REFERENCE s# ON Supplier TO Janusz; Then user Janusz can say: CREATE TABLE MySP( s# … CONSTRAINT MySP_fkey FOREIGN KEY(s#) REFERENCES Scott.SP(s#);

  30. Access control • Granting privileges User Scott says: CREATE VIEW SuppliersFrom London AS SELECT * FROM Supplier WHERE city = ‘London;

  31. Access control • Granting privileges User Scott says: CREATE VIEW SuppliersFromLondon AS SELECT * FROM Supplier WHERE city = ‘London; GRANT DELETE ON SuppliersFromLondon TO Janusz;

  32. Access control • Granting privileges Then user Janusz can say: DELETE FROM Scott.SuppliersFromLondon WHERE dob < ‘1-JAN-38’;

  33. Access control • Granting privileges User Scott says: CREATE VIEW PartShort AS SELECT p#, pname FROM Part;

  34. Access control • Granting privileges User Scott says: CREATE VIEW PartShort AS SELECT p#, pname FROM Part; GRANT SELECT ON PartShort TO Janusz;

  35. Access control • Granting privileges Then user Janusz can say: SELECT p# FROM Scott.PartShort WHERE pname = ‘bolt’;

  36. Access control • Grant propagation

  37. Access control • Grant propagation User Scott says: GRANT SELECT ON SP TO JK02

  38. Access control • Grant propagation User Scott says: GRANT SELECT ON SP TO JK02 WITH GRANT OPTION;

  39. Access control • Grant propagation User Scott says: GRANT SELECT ON SP TO JK02 WITH GRANT OPTION; Then user JK02 says: CREATE VIEW SPShort AS SELECT s#, p# FROM SP;

  40. Access control • Grant propagation User Scott says: GRANT SELECT ON SP TO JK02 WITH GRANT OPTION; Then user JK02 says: CREATE VIEW SPShort AS SELECT s#, p# FROM SP; GRANT SELECT ON SPShort TO Janusz;

  41. Access control • Grant propagation Then user Janusz can say: SELECT * FROM JK02.SPShort;

  42. Access control • Grant propagation Then user Janusz can say: SELECT * FROM JK02.SPShort; User Janusz can’t say: GRANT SELECT ON SPShort TO Greg;

  43. Access control • REVOKE statement

  44. Access control • REVOKE statement REVOKE statement may be used to revoke a subset privileges granted to a user

  45. Access control • Syntax REVOKE ON FROM ; <table name> <view name> ALL <list of privileges> PUBLIC <list of users>

  46. Access control • Revoking privileges User Scott says: REVOKE SELECT ON SP FROM JK02 ;

  47. Access control • Revoking privileges User Scott says: REVOKE SELECT ON SP FROM JK02 ; Then user JK02 can’t say: SELECT * FROM Scott.SP;

  48. Access control • Revoking privileges User Scott says: REVOKE SELECT ON SP FROM JK02 ; Then user JK02 can’t say: SELECT * FROM Scott.SP; and user Janusz can’t say: SELECT * FROM JK02.SPShort;

  49. Access control • Bibliography • P. O’Neil, Database - Principles, Programming, Performance, chapter 6.3 • R. Elmasri, S.B. Navathe, Fundamentals of Database Systems, chapter 20.1, 20.2 • R. K. Stephens, et al. Teach Yourself SQL in 21 Days

More Related