Restricting Record Access In some to restrict the access instances, you’ll want users’ to records in addition to restricting column and table access. First build a view that includes only the columns and records you want the users to be able to access Then, grant privileges to the view. In these notes, wherever you see you can tablename, insert instead. viewname user THOMPSON is your Example: Let’s say representative to the publishers. THOMPSON should be able to see, change and delete publisher data, but only for those publishers he has responsibility for. CREATE VIEW vwNyPubs AS * tblPublishers SELECT FROM WHERE publisherState=’NY’; GRANT SELECT, UPDATE, DELETE ON vwNyPubs TO Thompson; REVOKE ON FROM privileges tablename usernames; When you want to take privileges away from users, you use the REVOKE command. The REVOKE command is structured just like the GRANT command Can specify multiple privileges and users, but only one table Can use PUBLIC and ALL WITH GRANT OPTION apply doesn’t Examples: tblBooks REVOKE ALL ON FROM PUBLIC; REVOKE UPDATE, DELETE ON tblBooks FROM Davis, Johnson; Showing Privileges SHOW GRANTS; SHOW GRANTS FOR username;
Restricting Record Access In some instances, you’ll want to restrict the users’ access to records in addition to restricting column and table access. First build a view that includes only the columns and records you want the users to be able to access Then, grant privileges to the view. In these notes, wherever you see tablename, you can insert viewname instead. Example: Let’s say user THOMPSON is your representative to the publishers. THOMPSON should be able to see, change and delete publisher data, but only for those publishers he has responsibility for. CREATE VIEW vwNyPubs AS SELECT * FROM tblPublishers WHERE publisherState=’NY’; GRANT SELECT, UPDATE, DELETE ON vwNyPubs TO Thompson; REVOKE privileges ON tablename FROM usernames; When you want to take privileges away from users, you use the REVOKE command. The REVOKE command is structured just like the GRANT command Can specify multiple privileges and users, but only one table Can use PUBLIC and ALL WITH GRANT OPTION doesn’t apply Examples: REVOKE ALL ON tblBooks FROM PUBLIC; REVOKE UPDATE, DELETE ON tblBooks FROM Davis, Johnson; Showing Privileges SHOW GRANTS; SHOW GRANTS FOR username;
6%20-%20Views,%20Indexes%20&%20Security.docx