-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdemo_use_granted_roles.sql
More file actions
107 lines (78 loc) · 2.31 KB
/
demo_use_granted_roles.sql
File metadata and controls
107 lines (78 loc) · 2.31 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
connect / as sysdba
create user ipc_user identified by ipc_user;
grant
create procedure,
create session,
select_catalog_role
to
ipc_user;
-- Don't directly grant:
--
-- grant select on v_$process to ipc_user;
-- grant select on v_$session to ipc_user;
connect ipc_user/ipc_user
-- User ipc_user may select from v$process
select count(*) from v$process;
create or replace function tq84_proc_memory return varchar2 as
v_result varchar2(200);
begin
select
'Used: ' || round(pga_used_mem /1024/1024)||', '||
'Alloc: ' || round(pga_alloc_mem /1024/1024)||', '||
'Freeable: ' || round(pga_freeable_mem/1024/1024)||', '||
'PGA Max: ' || round(pga_max_mem /1024/1024)
into
v_result
from
v$process
where
addr = (select paddr from v$session where sid =
sys_context('USERENV','SID'));
return v_result;
end tq84_proc_memory;
/
-- function tq84_proc_memory does not compile:
show errors
-- LINE/COL ERROR
-- -------- -----------------------------------------------------------------
-- 5/5 PL/SQL: SQL Statement ignored
-- 15/33 PL/SQL: ORA-00942: table or view does not exist
-- It would compile, if grants on v$* were directly given:
-- grant select on v_$process to ipc_user /
-- grant select on v_$session to ipc_user.
connect / as sysdba
grant execute on dbms_job to ipc_user;
grant execute on dbms_pipe to ipc_user;
grant create job to ipc_user;
connect ipc_user/ipc_user
@IPC.pks
@IPC.pkb
create or replace function tq84_proc_memory return varchar2 as
v_proc varchar2(32000);
begin
v_proc := q'!
declare
x varchar2(200);
begin
select
'Used: ' || round(pga_used_mem /1048576)||', '||
'Alloc: ' || round(pga_alloc_mem /1048576)||', '||
'Freeable: ' || round(pga_freeable_mem/1048576)||', '||
'PGA Max: ' || round(pga_max_mem /1048576)
into
x
from
v$process
where
addr = (select paddr from v$session where sid = !' ||
sys_context('USERENV','SID') || q'!);
:result := x;
end;!';
return ipc.exec_plsql_in_other_session(v_proc);
end tq84_proc_memory;
/
show errors
select tq84_proc_memory from dual;
select ipc.exec_plsql_in_other_session('begin :result := user; end;') from dual;
connect / as sysdba
drop user ipc_user cascade;