Q.3) (25 points) – Granting and Revoking Privileges Write statements that do the
ID: 3721308 • Letter: Q
Question
Q.3) (25 points) – Granting and Revoking Privileges
Write statements that do the following:
- Grant privileges to ap_superuser as follows: o SELECT/UPDATE/INSERT/DELETE privileges to all tables in ap database
- Grant privileges to ap_dev as follows: o SELECT/UPDATE/INSERT privileges to all tables in ap database
- Grant privileges to ap_guest as follows: o SELECT privileges to all tables
o UPDATE privileges to VENDORS table only
- Revoke all privileges from ap_guest
- Revoke INSERT privileges from ap_dev
pg. 2
Q.4) (25 points) – Viewing User and Privilege Information
Write statements to do the following:
- Display a list of user and host for all users, sort by host, then user.
- Show grants for the currently logged on user
- Show privileges for lazydaisy
- Show privileges for ap_superuser
- Show privileges for ap_guest
Explanation / Answer
--Q.3) (25 points) – Granting and Revoking Privileges
--Write statements that do the following:
/*- Grant privileges to ap_superuser as follows: o SELECT/UPDATE/INSERT/DELETE privileges to all tables in ap database*/
GRANT SELECT,INSERT,UPDATE,DELETE ON ap.* TO 'ap_superuser'@'localhost'
/* Grant privileges to ap_dev as follows: o SELECT/UPDATE/INSERT privileges to all tables in ap database*/
GRANT SELECT,INSERT,UPDATE ON ap.* TO 'ap_dev'@'localhost'
/*- Grant privileges to ap_guest as follows: o SELECT privileges to all tables*/
GRANT SELECT ON ap.* TO 'ap_guest'@'localhost'
o UPDATE privileges to VENDORS table only
/*- Revoke all privileges from ap_guest*/
REVOKE ALL ON ap.* FROM 'ap_guest'@'localhost';
/*- Revoke INSERT privileges from ap_dev*/
REVOKE INSERT ON ap.* FROM 'ap_dev'@'localhost';
/*pg. 2
Q.4) (25 points) – Viewing User and Privilege Information
Write statements to do the following:*/
/*- Display a list of user and host for all users, sort by host, then user.*/
select User,Host from mysql.user ORDER BY User;
/*- Show grants for the currently logged on user*/
--You can use any of below query
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
/*- Show privileges for lazydaisy*/
SHOW GRANTS FOR 'lazydaisy';
/*- Show privileges for ap_superuser*/
SHOW GRANTS FOR 'ap_superuser';
/*- Show privileges for ap_guest*/
SHOW GRANTS FOR 'ap_guest';
/*ALL Queries based on MYSQL*/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.