Practice Exercise:  Data Sharing and Security

                
SQL syntax(for CS 340):
  create user userName@localhost identified by password;
  drop user userName@localhost;
  grant {ALL | privilegeList} on dbName.table to user [identified by password]
     [with grant option];
  revoke {ALL | privilegeList} [, grant option] on [dbName.]table
     from userName@localhost; 
  rename user userName@localhost to newUserName;
  select host,user from mysql.user where password='';
  set password [for userName@localhost] = password('password');
  show grants [for userName@localhost];
  
Given the relations Student(sId, sName, major, classYear, birthdate) and
    Enrollment(sId, cCode, grade) and a database with corresponding tables
    loaded with data, write SQL statements to:

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

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

3.  Show the privileges granted to developer.

4.  The user named developer is to:
    a. create a view named csciMajor that permits access to complete student
       records that are CSCI majors;

    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;

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

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

5.  Show the views created for the student table.

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

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

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

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

7.  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, ...).

8.  Revoke the privileges granted above.