Security and Precautions For the medium-sized business schema used in Assignments 4 and 5, write the SQL statements for the following: Create roles forthe Managers, HR Specialists, and Other Users....

1 answer below »

Security and Precautions



  • For the medium-sized business schema used in Assignments 4 and 5, write the SQL statements for the following:

    • Create roles forthe Managers, HR Specialists, and Other Users.

    • Grant privileges to the roles as follows:

      • All users have read access to all the tables except Employment.

      • Managers have read access to all the tables (including Employment), plus the ability to apply inserts, updates, and deletes to all tables
        except
        Division, Department, Work_Group, Pay_Grade, Employment, and Employee_Work_Group_Assignment

      • HR Specialists have read access to all tables (including Employment), plus the ability inserts, updates, and deletes to the Division, Department, Work_Group, Pay_Grade, Employment, and Employee_Work_Group_Assignment

        Hint: To simplify your task, assume you can grant roles to other roles








  • List the security precautions you would recommend for each of the following database applications:


    • Company personnel database

    • Hospital medical records database

    • Bank checking account database

    • Consumer credit database

    • Automobile insurance claims database

    • Medical insurance claims database

    • Personal music library and playlist database





hw4 hw5 questions and answers are attached




Assignment_4_Instructor_Solution_with_generalized_hierarchy Instructor's Solution to Assignment 5 Organization and Individual Collapsed into Party Notation: IEn,m: Non-unique Index AKn,m: Unique (Alternate Key) Index n = index sequence number m = column sequence number CONTACT_MECHANISM_USAGE PARTY_ID CONTACT_MECHANISM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) CONTACT_USAGE_TYPE_CODE VARCHAR(5) NULL (FK) CONTACT_USAGE_TYPE CONTACT_USAGE_TYPE_CODE VARCHAR(5) NOT NULL CONTACT_USAGE_TYPE_DESCRIPTION VARCHAR(100) NOT NULL CONTACT_MECHANISM CONTACT_MECHANISM_ID INTEGER NOT NULL CONTACT_MECHANISM_TYPE_CODE CHAR(2) NOT NULL DEPARTMENT DEPARTMENT_ID INTEGER NOT NULL DEPARTMENT_NAME DIVISION_ID DEPARTMENT_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER INTEGER NOT NULL NOT NULL NULL (FK) (FK) (AK1.1) (IE1.1) DIVISION DIVISION_ID INTEGER NOT NULL DIVISION_NAME DIVISION_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER NOT NULL NULL (FK) (AK1.1) E_MAIL CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) E_MAIL_ADDRESS VARCHAR(255) NOT NULL EMPLOYEE_WORK_GROUP_ASSIGNMENT EMPLOYEE_ID WORK_GROUP_ID WORK_GROUP_ASSIGNMENT_EFFECTIVE_DATE INTEGER INTEGER DATE NOT NULL NOT NULL NOT NULL (FK) (FK) (IE1.1) WORK_GROUP_ASSIGNMENT_END_DATE DATE NULL EMPLOYMENT EMPLOYEE_ID INTEGER NOT NULL PAY_GRADE_CODE PARTY_ROLE_ASSIGNMENT_ID VARCHAR(20) INTEGER NOT NULL NOT NULL (FK) (FK) (AK1.1) ITEM ITEM_ID INTEGER NOT NULL ITEM_DESCRIPTION ITEM_LIST_PRICE VARCHAR(255) DECIMAL(7,2) NOT NULL NOT NULL PARTY PARTY_ID INTEGER NOT NULL PARTY_TYPE_CODE ORGANIZATION_NAME GIVEN_NAME MIDDLE_NAME FAMILY_NAME BIRTH_DATE CHAR(2) VARCHAR(100) VARCHAR(40) VARCHAR(40) VARCHAR(40) DATE NOT NULL NULL NULL NULL NULL NULL PARTY_ROLE_ASSIGNMENT PARTY_ROLE_ASSIGNMENT_ID INTEGER NOT NULL PARTY_ID PARTY_ROLE_TYPE_CODE ROLE_EFFECTIVE_DATE ROLE_TERMINATION_DATE INTEGER CHAR(3) CHAR(18) CHAR(18) NOT NULL NOT NULL NULL NULL (FK) (FK) (AK1.1) (AK1.3) (AK1.2) PARTY_ROLE_TYPE PARTY_ROLE_TYPE_CODE CHAR(3) NOT NULL PARTY_ROLE_TYPE_NAME VARCHAR(100) NOT NULL PAY_GRADE PAY_GRADE_CODE VARCHAR(20) NOT NULL PAY_GRADE_NAME VARCHAR(100) NOT NULL (AK1.1) POSTAL_ADDRESS CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) ADDRESS_LINE_1_TEXT ADDRESS_LINE_2_TEXT CITY_NAME STATE_PROVINCE_CODE POSTAL_CODE COUNTRY_CODE VARCHAR(255) VARCHAR(255) VARCHAR(128) CHAR(2) VARCHAR(20) CHAR(2) NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL PURCHASE_ORDER PURCHASE_ORDER_NUMBER INTEGER NOT NULL PARTY_ROLE_ASSIGNMENT_ID ORDER_DATE ORDER_STATUS_CODE INTEGER DATE CHAR(2) NULL NOT NULL NOT NULL (FK) (IE1.1) PURCHASE_ORDER_ITEM PURCHASE_ORDER_NUMBER ITEM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) ITEM_PURCHASE_UNIT_PRICE ITEM_PURCHASE_QUANTITY DECIMAL(7,2) INTEGER NOT NULL NOT NULL TELEPHONE CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) AREA_CODE PHONE_NUMBER PHONE_EXTENSION_NUMBER DEVICE_TYPE_CODE CHAR(3) VARCHAR(20) VARCHAR(9) CHAR(2) NULL NOT NULL NULL NOT NULL (IE1.2) (IE1.1) WORK_GROUP WORK_GROUP_ID INTEGER NOT NULL WORK_GROUP_NAME EMPLOYEE_ID DEPARTMENT_ID VARCHAR(100) INTEGER INTEGER NOT NULL NULL NOT NULL (FK) (FK) (AK1.1) (IE2.1) (IE1.1)
Answered Same DayAug 03, 2022

Answer To: Security and Precautions For the medium-sized business schema used in Assignments 4 and 5, write the...

Salony answered on Aug 04 2022
79 Votes
1)
CREATE ROLE manager;
CREATE ROLE HR_Specialist;
2)
GRANT SELECT ON
party||Division||Party_role_type||Party_role_assign||cont
act_usage_type||contact_mechnaism||postal_address||email||item||purchase_order||Department||purchase_oder_item||Telephone||pay_grade||Wrokgroup||Employee_Work_group_Assignment to Manager
GRANT SELECT ON
party||Division||Party_role_type||Party_role_assign||contact_usage_type||contact_mechnaism||postal_address||email||item||purchase_order||Department||purchase_oder_item||Telephone||pay_grade||Wrokgroup||Employee_Work_group_Assignment to HR_Specialist
GRANT SELECT ON
party||Division||Party_role_type||Party_role_assign||contact_usage_type||contact_mechnaism||postal_address||email||item||purchase_order||Department||purchase_oder_item||Telephone||pay_grade||Wrokgroup||Employee_Work_group_Assignment to Other
REVOKE SELECT ON EMPLoyment to Other
3)
Grant select on party||Division||Employment||Party_role_type||Party_role_assign||contact_usage_type||contact_mechnaism||postal_address||email||item||purchase_order||Department||purchase_oder_item||Telephone||pay_grade||Wrokgroup||Employee_Work_group_Assignment to Manager;
Revoke insert,update,delete on
Division||Employment ||pay_grade||Wrokgroup||Employee_Work_group_Assignment to Manager;
Grant insert,update,delete on
party...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here