Chapter 18 Application

Question 1

Login as user root to create a database named csci and user named developer at localhost with the password ‘whoAmI’.

Answer 1

create database csci;

Question 2

Have root grant privileges to developer for creating and maintaining tables with the option to delegate grants to other users.

Answer 2

grant all on csci.* to developer@localhost identified by ‘whoAmI’ with grant option;

Question 3

Show the privileges granted to the developer.

Answer 3

show grant for developer@localhost;

Question 4

The user named developer is to:

a. create a view named csciMajor that permits access to complete student records that are CSCI majors;

Answer 4

create or replace view csciMajor as select * from student where major = ‘CSCI’;

Question 5

b. create a view named csciList that materializes a row and column subset with access to sId, sName, major, and classYear but not to birthdate in rows for ‘CSCI’ majors;

Answer 5

create or replace view csciList as select sid, sname, major, classYear from student where major = ‘CSCI’;

Question 6

c. create gradeReport, a join view, with sName, cCode and grade;

Answer 6

create or replace view gradeReport as select sname, cCode, grade from student s join enrollemnt e on s.sid = e.sid;

Question 7

d. create courseLoad, a statistical summary view, with columns sName and count(*) of course enrollments by student.

Answer 7

create or replace view courseLoad as select sName, count(*) as courses from student s, enrollment e where s.sid = e.sid group by sname;

Question 8

Show the views created for the student table.

Answer 8

show create view csciMajor;

show create view csci;

Question 9

Have root create users admiss, deptHead, advisor and professor with these privileges:
a) User admiss may select, insert, update, and delete student data;

Answer 9

exit;
mysql -u root -p mysql
grant select, insert, update, delete on csci.student to admiss@localhost identified by ‘secret’;

Question 10

b) User deptHead is to have select privileges over the view courseLoad with authorization to grant this privilege to another user;

Answer 10

grant select on csci.courseLoad to deptHead@localhost identified by ‘secret’ with grant option;

Question 11

c) User advisor may select from csciList and update major in student (this may also require the select privilege on csci.student).

Answer 11

grant select on csci.csciList to advisor@localhost identified by ‘secret’;

grant select,update(major) on student to advisor@localhost;

Question 12

d) User professor is to have permission to query csciList and to update grade in Enrollment.

Answer 12

grant select,update(grade) on csci.enrollment to professor@localhost identified by ‘drSaffer’;

Question 13

Show grants [for ‘user’@’localhost’]; or query information_schema to find information about authorizations granted by the current user. Include grantee, table name and privileges (select, insert, …).

Answer 13

show grants for developer@localhost;

Question 14

Revoke the privileges granted above.

Answer 14

revoke select,insert,update,delete on csci.student from admiss@localhost;