1 access control. 2 grant statement 3 access control grant statement grant statement may be used by...

1

Access controlAccess control

2


• GRANT statement• GRANT statement

3


• 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

• 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


• Syntax

GRANT

• Syntax

GRANT ALL<list of privileges>

5


• Syntax

GRANT ON

• Syntax

GRANT ONALL<list of privileges>

<table name><view name>

6


• Syntax

GRANT ON

TO

• Syntax

GRANT ON

TO

ALL<list of privileges>


PUBLIC<list of users>

7


• Syntax

GRANT ON

TO [WITH GRANT OPTION];

• Syntax

GRANT ON

TO [WITH GRANT OPTION];




8


• Privileges• Privileges

9


• Privileges

SELECTGrantee is allowed to select data from a table determined by a grantor

• Privileges

SELECTGrantee is allowed to select data from a table determined by a grantor

10


• Privileges

SELECTGrantee is allowed to selected data from a table determined by a grantor

DELETEGrantee is allowed to delete data from a table determined by a grantor

• Privileges



11


• Privileges



INSERTGrantee is allowed to insert data into a table determined by a grantor

• Privileges




12


• Privileges




• Privileges




13


• Privileges

UPDATE [<list of columns>]Grantee is allowed to update the columns from a table determined by a grantor

• Privileges


14


• Privileges


REFERENCES [<list of columns]Grantee is allowed to reference the columns from a table specified by a grantor

• Privileges


REFERENCES [<list of columns]Grantee is allowed to reference the columns from a table specified by a grantor

15


• Granting privileges

User Scott says:GRANT SELECT ON Supplier TO Janusz;



16






privilege

17






privilege

table (view) name

18






privilege

table (view) name

user

19




Then user Janusz can say: SELECT * FROM Scott.Supplier;




20








user.table

21



User Janusz says:CREATE SYNONYM Ssupplier

FOR Scott.Supplier;



FOR Scott.Supplier;

22




FOR Scott.Supplier;

Then user Janusz can say:SELECT * FROM Ssupplier;



FOR Scott.Supplier;

Then user Janusz can say:SELECT * FROM Ssupplier;

23



User Scott says:GRANT UPDATE pname, price ON Part

TO Janusz;



TO Janusz;

24




TO Janusz;

Then user Janusz can say:UPDATE Scott.Part SET price = price + 10

WHERE pname =‘bolt’;



TO Janusz;

Then user Janusz can say:UPDATE Scott.Part SET price = price + 10


25



User Scott says:GRANT ALL ON Supplier TO PUBLIC;



26




Then anyone can say:SELECT * FROM Scott.Supplier;

UPDATE Scott.Part SET price = price + 10


INSERT INTO Scott.Supplier VALUES( ... );

DELETE FROM Scott.Supplier;



Then anyone can say:SELECT * FROM Scott.Supplier;

UPDATE Scott.Part SET price = price + 10


INSERT INTO Scott.Supplier VALUES( ... );

DELETE FROM Scott.Supplier;

27



User Scott says:GRANT REFERENCE s# ON Supplier

TO Janusz;



TO Janusz;

28




TO Janusz;

Then user Janusz can say:CREATE TABLE MySP( s# …

CONSTRAINT MySP_fkey FOREIGN KEY(s#) REFERENCES Scott.SP(s#);



TO Janusz;



29




TO Janusz;





TO Janusz;



30



User Scott says:CREATE VIEW SuppliersFrom London AS

SELECT *

FROM Supplier

WHERE city = ‘London;


User Scott says:CREATE VIEW SuppliersFrom London AS

SELECT *

FROM Supplier


31



User Scott says:CREATE VIEW SuppliersFromLondon AS

SELECT *

FROM Supplier


GRANT DELETE ON SuppliersFromLondon TO Janusz;


User Scott says:CREATE VIEW SuppliersFromLondon AS

SELECT *

FROM Supplier


GRANT DELETE ON SuppliersFromLondon TO Janusz;

32



Then user Janusz can say:DELETE FROM Scott.SuppliersFromLondon

WHERE dob < ‘1-JAN-38’;


Then user Janusz can say:DELETE FROM Scott.SuppliersFromLondon

WHERE dob < ‘1-JAN-38’;

33



User Scott says:CREATE VIEW PartShort AS

SELECT p#, pname

FROM Part;



SELECT p#, pname

FROM Part;

34




SELECT p#, pname

FROM Part;

GRANT SELECT ON PartShort TO Janusz;



SELECT p#, pname

FROM Part;

GRANT SELECT ON PartShort TO Janusz;

35



Then user Janusz can say:SELECT p#

FROM Scott.PartShort

WHERE pname = ‘bolt’;


Then user Janusz can say:SELECT p#

FROM Scott.PartShort

WHERE pname = ‘bolt’;

36


• Grant propagation• Grant propagation

37


• Grant propagation

User Scott says:GRANT SELECT ON SP TO JK02



38




WITH GRANT OPTION;



WITH GRANT OPTION;

39




WITH GRANT OPTION;

Then user JK02 says:CREATE VIEW SPShort AS

SELECT s#, p#

FROM SP;



WITH GRANT OPTION;


SELECT s#, p#

FROM SP;

40




WITH GRANT OPTION;


SELECT s#, p#

FROM SP;

GRANT SELECT ON SPShort TO Janusz;



WITH GRANT OPTION;


SELECT s#, p#

FROM SP;

GRANT SELECT ON SPShort TO Janusz;

41



Then user Janusz can say:SELECT *

FROM JK02.SPShort;



FROM JK02.SPShort;

42




FROM JK02.SPShort;

User Janusz can’t say:GRANT SELECT ON SPShort TO Greg;



FROM JK02.SPShort;

User Janusz can’t say:GRANT SELECT ON SPShort TO Greg;

43


• REVOKE statement• REVOKE statement

44


• REVOKE statement

REVOKE statement may be used to revoke

a subset privileges granted to a user

• REVOKE statement

REVOKE statement may be used to revoke

a subset privileges granted to a user

45


• Syntax

REVOKE ON

FROM ;

• Syntax

REVOKE ON

FROM ;




46


• Revoking privileges

User Scott says:REVOKE SELECT ON SP FROM JK02 ;



47




Then user JK02 can’t say:SELECT * FROM Scott.SP;




48





and user Janusz can’t say:SELECT * FROM JK02.SPShort;




and user Janusz can’t say:SELECT * FROM JK02.SPShort;

49


• 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

• 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

1 access control. 2 grant statement 3 access control grant statement grant statement may be used by...

Documents