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 included in this
act
...
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 included 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]