Question :

Suppose you have a table, EMPLOYEE, with the following attributes: eid, work_title, fname, lname, ssn, salary, date_of_birth, and commission_rate.

Ms. Smith is vice president of sales. She and three regional sales managers oversee the sales force and need to know the name of each employee whose work title is 'salesperson' along with each salesperson's salary and commission. Ms. Smith and her managers are not allowed to know the ssn or birthday of salespersons.

Only Ms. Smith is allowed to change the commission rate of salespersons. Mr. Jones works in the president's office and is in charge of sending birthday cards to each employee on their birthday. Mr. Jones only needs to see the name and birthday of employees.

Using roles, permissions (privileges), and views describe how you would set up the security of the database to enforce the restrictions described. You must use at least one role.

You must use at least one view. You must assign appropriate permissions.