USERS
set pagesize 100
col profile format a10
col temporary_tablespace format a10
col default_tablespace format a10
set linesize 200
col account_status format a10
col username format a10
col grantee format a10
col owner format a10
col table_name format a10
col grantor format a10
col privilege format a15
col column_name format a11
col role format a10
AFTERWARDS THAT GO THROUGH THE FOLLOWING STEPS:
1. Connect as sys
2. Create a permanent tablespace (obo) of size 5m
3. Create a temporary tablespaces (marian) of size 5m
4. Create a user
create user cipd identified by cipd
default tablespace obo
temporary tablespace marian
profile default
password expire
account lock
quota 1m on obo;
5. Run the query below to check the properties of the users
Select username,account_status,lock_date,expiry_date,default_tablespace,
temporary_tablespace,profile from dba_users;
6. Conn as the user created in (4) above
7. What is the error encountered. Take closely look at step 4
8. Connect as sys
9. Run the query below and repeat step 6. What were your observations
alter user cipd account unlock;
10. Log in as sys and run the query below, repeat step 6 and show user
grant create session to cipd;
11. Repeat step 5. What were your observations
12. Run queries below:
select * from user_sys_privs;
select * from user_role_privs;
select * from user_tab_privs_recd;
select * from user_tab_privs_made;
select * from user_tab_privs;
select * from user_col_privs_recd;
select * from user_col_privs_made;
select * from user_col_privs;
select * from role_role_privs;
select * from role_sys_privs;
select * from role_tab_privs;
select * from session_privs;
select * from session_roles;
What is your observation
NB: You will be running the queries above several times, kindly observe the rows that will be return as the queries are being run.
13. Create a table by using the query below.
create table ttb ( id number );
Note the error
14. run insert into ttb values (1); and commit
15. Repeat step 12
16. Log in as hr and select * from cipd.ttb, note down the error
17. Log in as cipd and run the query below
grant select on ttb to hr;
repeat step 16
18. Repeat step 12
19. Create another table ssb
Create table ssb (id number, name varchar2(10));
20. Log in as hr and select * from cipd.ssb
21. Log in as cipd and issue: grant insert (id) on ssb to hr;
22. Log in as hr and issue: insert into cipd.ssb(id) values(1); and again
select * from cipd.ssb
23. Log in as cipd and select from ssb
24. Log in again as hr and issue: insert into cipd.ssb values(2,'KKK').
Note down the error. Issue this other statement: insert into cipd.ssb(id) values(2);
25. Connect as cipd and repeat step 12
26. Connected as cipd create a user kwame.
27. If the above fail, conn as sys and issue: grant create user to cipd and repeat
26. Please make sure you can log in as Kwame.
28. Log in as cipd and issue: grant create table to Kwame, what happened..?
29. Connect as sys and issue: grant create table to cipd with admin option
30. Repeat step 28
31. Connect as hr and issue: grant select on employees to cipd;
32. Connect as cipd and repeat step 12
33. Connect as scott and issue : grant insert (empno) on emp to cipd;
34. Connect as cipd and repeat step 12
35. Connected as cipd issue: grant insert (empno) on scott.emp to kwame;
36. Note the error
37. Connect as scott and issue : grant insert (empno) on emp to cipd with grant option;
38. Repeat step 35
39. Connected as cipd run step 12
40. Connected as cipd issue: create role mama identified by mama;
41. Connect as sys and grant create role to cipd. And repeat step 40
42. Connect as cipd and issue: grant create table to mama;
43. Run step 12
44. Connected as cipd issue: grand select on ttb to mama; and repeat step 12
45. As cipd issue: create role papa identified by papa and repeat step 12
46. AS cipd issue: grant mama to papa and repeat step 12
47. As sys issue: grant connect to cipd and repeat step 12
