HOMEWORK > Laboratory Exercise 001 - Manage role and apply privileges_ AMA Computer University ICT ENTREP

Document Content and Description Below

Manage role and apply privileges MACHINE ACTIVITY Directions : Perform the following activity Managing roles by applying different type of privileges. : Copy and paste the DEPARTMENTS table i ... ncluded in this activity. : In each number add the PL/SQL used. : Answer the questions ask in each number. : Skipping a number would cause different output consider as incorrect answer. 1. What privilege should a user be given to log on to the Oracle server? Is this a system or an object privilege? 2. What privilege should a user be given to create tables? 3. If you create a table, who can pass along privileges to other users on your table? 4. You are the DBA. You are creating many users who require the same system privileges. What should you use to make your job easier? 5. What command do you use to change your password? 6. Connect CS352-DBMS2-MAS Page 1 TITLE: Manage role and apply privileges USN ID: [email protected] MACHINE ACTIVITY NO. 01 DATE: November 21, 2018 To iqlplus* using the following session: 7. Populate the given DEPARTMENTS table as shown below: Before proceedings to the next number make sure to double check you should have 8 rows of values. 8. Create 3 different USERS with their corresponding password: N o. User_nam e Passw ord 1 SUPERVIS OR SPVR 2 MANAGER MGR 3 CLERK CLK 9. From the created user grant a different system privilege for each user: Privilege 1 SUPERVIS OR SPVR CREATE SESSION CS352-DBMS2-MAS Page NAME: Marden M. Martin COURSE: BSIT TITLE: Manage role and apply privileges USN ID: [email protected] MACHINE ACTIVITY NO. 01 DATE: November 21, 2018 2 MANAGER MGR CREATE TABLE 3 CLERK CLK CREATE SESSION and CREATE TABLE 10. Try to connect using the three (3) created users. • Which of the following user successfully establish a connection to oracle server? • Which of the following user was not able to connect to oracle server? What is the reason? 11. Establish again a connection using: After establishing a connection to SYSTEM/oracle. Create a role and grant privilege of creating a table. Role Privileges SUP_AD MIN CREATE TABLE • From the role created in No. 11 SUP_ADMIN grant this said role to all USERS who are able to establish a session. 12. Log-on as CLERK and change your password to open. Log-on as SUPERVISOR and change your password to sup CS352-DBMS2-MAS Page 3 NAME: Marden M. Martin COURSE: BSIT TITLE: Manage role and apply privileges USN ID: [email protected] MACHINE ACTIVITY NO. 01 DATE: November 21, 2018 13. Log-on as SYSTEM/oracle • Grant objects privilege to SUPERVISOR by allowing to use the UPDATE for column (DEPARTNAME) only, and SELECT statement to the table DEPARTMENTS. 14. After adding object privilege to SUPERVISOR. • Log-on SUPERVISOR • Manipulate the DEPARTMENTS table, UPDATE the department_name to IT whose manager_id is Null. • Confirm the changes made to DEPARTMENTS table by using the SELECT statement 15. Log-on as SYSTEM then modify the object privilege created to SUPERVISOR to UPDATE , INSERT and SELECT to all columns, Note that SUPERVISOR should have the capability to pass the privilege given to them to other USERS. 16. Log-on as SUPERVISOR, then pass the privilege to UPDATE INSERT and use SELECT to user CLERK. 17. Confirm that the user CLERK can now perform insertion and update. • Log-on as CLERK • Insert a new Departments with the following values as shown below: 5 IT_DE PT Is adding the new values when log-on as CLERK is possible? Why and why not? 18. Log-on as System then revoke all the object privilege given to SUPERVISOR. 19. Log-on as Supervisor. • Create a report that will display all the DEPARTMENT_NAME whose location_id is equal to 1700. • Is creating a report possible? Why and why not? Log-on as Clerk. CS352-DBMS2-MAS Page 4 NAME: Marden M. Martin COURSE: BSIT TITLE: Manage role and apply privileges USN ID: [email protected] MACHINE ACTIVITY NO. 01 DATE: November 21, 2018 • Create a report that will display all the manager_id whose location_id is equal to 2500. • Is creating a report possible? Why and why not? 20. Log-on as SYSTEM then delete all the users created to free-up some memory space. CS352-DBMS2-MAS Page 5 [Show More]

Last updated: 2 years ago

Preview 1 out of 5 pages