Using your music database assume there is a user with userid dba625a. how to Give this user access to the music database. - Give this user read access to the entire database - Give this user write access only to the bandrecognition table - Give this user the ability create views - This user should not have the ability to create any other objects 

 

What to hand in. - Connect to the database using this new userid. - Demonstrate that the user can read data from the database - Demonstrate that the user can update data in the bandrecognition table - Demonstrate that the user cannot update data in other tables - Demonstrate that the user can create a view - Demonstrate that the user cannot create an index 

 

2 - (20%) - how to Create an audit policy which tracks the following: - Any update to the bandrecognition table. This would include an INSERT, UPDATE and DELETE command. - Any change to genre column of the bandinfo table 

 

What to hand in. - Connect to the database using the new userid dba625a - Have the user run an INSERT command against the bandrecognition table - Have the user run an UPDATE command against the bandrecognition table - Have the user run a DELETE command against the bandrecognition table - Have the user run an UPDATE command against the bandinfo table which updates the genre column - Show the created records in the audit table which reflects the above audited activities 

 

3 - (20%) - how to Create a trigger which performs the exact same operation and as part of the trigger firing updates a bandaudit table. 

 

What to hand in. - how to Create a bandaudit table with the following columns o Tblnm (which will contain the table name) o Tblops (which contains an "I" or "U" or "D") o Tbldttm (which contains the current date/time stamp) - Turn off the audit policy from Step 2 - Re-run the same operations as in Step 2 - Provide the output of a SELECT * FROM BANDAUDIT 

 

4 - (20%) - how to Update the bandinfo table so that it is encrypted. NOTE: Depending on the database system you are using you may need to completely recreate the database. If you have to do this, do the following: - EXPORT all the tables - DROP the database - CREATE the database WITH encryption turned on - IMPORT all the tables 

 

What to hand in: -how to Show the commands executed to accomplish the above - Show the results of a SELECT * FROM BANDINFO without the decryption key - Show the results of a SELECT * FROM BANDINFO with the decryption key 

 

5 - (20%) - This will cover roles and row/column filtering - Re-create the userid called dba625b - how to Create a new userid called dba625c - Create a ROLE called BANDCAN and add dba625b to this role. - how to Create a ROLE called BANDANA and add dba625c to this role - BANDCAN role should have read access to bandinfo - BANDCAN role should only be allowed to see information from bandino table if the BASECOUNTRY column is 'Canada' - BANDANA role should have read access to bandinfo table - BANDANA role should only see the first letter of GENRE - the rest of GENRE should be masked with an '*' (for example: ROCK would show up as R***) 

 

What to hand in: - All of the security commands required to create the two roles and provide the appropriate authorizations - All of the security commands required to add the respective userids to their respective roles - All of the security commands required to define the row filters and column filters for the respective roles - The output from you (database owner) running a SELECT * FROM BANDINFO - The output from dba625b running a SELECT * FROM BANDINFO - The output from dba625c running a SELECT * FROM BANDINFO