Suppose you have a table, EMPLOYEE, with the following attributes: eid, work_tit

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.

Explanation / Answer

grant update(commission_rate) on EMPLOYEE to Smith;
GRANT SELECT (date_ob_birth,fname, lname) ON employee TO Jones;
GRANT SELECT (eid, work_title, fname, lname, salary, commission_rate) ON EMPLOYEE TO SMITH;
GRANT SELECT (eid, work_title, fname, lname, salary, commission_rate) ON EMPLOYEE TO ALL;

create table Emp_data as
select eid, work_title, fname, lname, salary, commission_rate from employee;

create view Birthday
as select date_ob_birth,fname, lname from employee;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.