This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx INFORMATION MANAGEMENT EXERCISE 8DCL QueriesJuan Carlo Q Castillo Name of Professor March 7 2020 March 7 2020 This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx I OBJECTIVESAt the end of this exercise students must be able to a Granting other users privileges to your tableb Modifying another user s table through the privileges granted to youc Creating a synonymII BACKGROUND INFORMATIONCreating users The database administrator DBA creates users with the CREATE USER statement After a user is created the DBA can grant specific system privileges to that userAn application developer for example may have the following system privileges CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDUREThe DBA can grant specific system privileges to a user Give a user authority to pass along system privileges Create a role Grant privileges to a role CREATE USER user IDENTIFIED BY password CREATE USER demoIDENTIFIED BY demo GRANT create session create table create sequence create viewTO demo GRANT create session create tableTO demoWITH ADMIN OPTION CREATE ROLE manager This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx Grant a role to users Grant a role to users The DBA creates your user account and initializes your password You can change your password by using the ALTER USER statement Object privileges vary from object to object An owner has all the privileges on the object An owner can give specific privileges on that owner s object Revoking Object PrivilegesYou use the REVOKE statement to revoke privileges granted to other users Privileges granted to others through the WITH GRANT OPTION clause are also revoked III EXPERIMENTAL PROCEDUREINSTRUCTIONS ScenarioTeam up with other students for this exercise about controlling access to database objects Task GRANT create table create view TO manager GRANT manager TO BELL KOCHHAR ALTER USER demo IDENTIFIED BY employ GRANTobject priv columns ONobject TO user role PUBLIC WITH GRANT OPTION REVOKE privilege privilege ALL ON objectFROM user user role PUBLIC CASCADE CONSTRAINTS This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx Write the appropriate SQL statement for the following queries The result of the querieswill be checked from your computer 1 What privilege should a user be given to log on to the Oracle server Is this a system privilege oran object privilege The CREATE SESSION system privilege2 What privilege should a user be given to create tables The CREATE TABLE privilege3 If you create a table who can pass along privileges to other users on your table You can or anyone you have given those privileges to by using WITH GRANT OPTION4 You are the DBA You are creating many users who require the same system privileges Whatshould you use to make your job easier Create a role containing the system privileges and grant the role to the users 5 What command do you use to change your password The ALTER USER statement6 User1 is the owner of the EMP table and grants the DELETE privilege to User2 by using the WITH GRANT OPTION clause User2 then grants the DELETE privilege on EMP to User3 User1 now finds that User3 has the privilege and revokes it from User2 Which user can now delete from the EMP table Only User217 You want to grant SCOTT the privilege to update data in the DEPARTMENTS table You alsowant to enable SCOTT to grant this privilege to other users What command do you use GRANT UPDATE ON departments TO scott WITH GRANT OPTION 8 Grant another user query privilege on your table Then verify whether that user can use theprivilege Note For this exercise team up with another group For example if you are userora21 team up with another user ora22 a Grant another user privilege to view records in your REGIONS table Include an optionfor this user to further grant this privilege to other users GRANT select ON regions TO WITH GRANT OPTION b Have the user query your REGIONS table SELECT FROM team1 oraxx regions This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx c Have the user pass on the query privilege to a third user for example ora23 GRANT select ON team1 oraxx regions TO team3 oraxx d Take back the privilege from the user who performs step b REVOKE select ON regions FROM team2 oraxx Note Each team can run exercises 9 and 10 independently 9 Grant another user query and data manipulation privileges on your COUNTRIES table Makesure that the user cannot pass on these privileges to other users GRANT select update insert ON COUNTRIES TO team2 oraxx 10 Take back the privileges on the COUNTRIES table granted to another user REVOKE select update insert ON COUNTRIES FROM team2 oraxx Note For exercises 11 through 17 team up with another group 11 Grant another user access to your DEPARTMENTS table Have the user grant you query accessto his or her DEPARTMENTS table TEAM 1GRANT select ON departments TO team1 oraxx TEAM 2GRANT select ON departmentsTO team2 oraxx 12 Query all the rows in your DEPARTMENTS table SELECT FROM departments This study source was downloaded by 100000829421545 from CourseHero com on 03 31 2022 02 09 53 GMT 05 00 https www coursehero com file 56756897 CCS0014 Lab Exercise 8docx 13 Add a new row to your DEPARTMENTS table Team 1 should add Education as departmentnumber 500 Team 2 should add Human Resources as department number 510 Query the otherteam s table Team 1 executes this INSERT statement INSERT INTO departments department id department name VALUES 500 Education COMMIT Team 2 executes this INSERT statement INSERT INTO departments department id department name VALUES 510 Human Resources COMMIT 14 Create a synonym for the other team s DEPARTMENTS table CREATE SYNONYM team2 FOR team2 oraxx DEPARTMENTS CREATE SYNONYM team1FOR team1 oraxx DEPARTMENTS 15 Query all the rows in the other team s DEPARTMENTS table by using your synonym Team 1 SELECT statement results SELECT FROM team2 Team 2 SELECT