automatic generated grants on sys_plsql_xxxx_yy_z [message #570622] |
Tue, 13 November 2012 01:48 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Gogetter
Messages: 39 Registered: December 2009 Location: Cologne Germany
|
Member |
|
|
Hello,
in the dba_tab_privs we can find some entries like this:
SELECT *
FROM dba_tab_privs
WHERE GRANTEE IN ('XXXXXXXX')
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
XXXXXXXX YYYYYYYYYYY SYS_PLSQL_71964_26_1 YYYYYYYYYYY EXECUTE YES NO
Those grants were generated automatically by oracle. Is there any way to prevent Oracle to grant them? An external audit-rule tells us not to give any grant directly to user - we always have to use databaseroles.
thanks in advanced & regards
Rudi Zugreif
|
|
|
|
|
|
Re: automatic generated grants on sys_plsql_xxxx_yy_z [message #570632 is a reply to message #570630] |
Tue, 13 November 2012 05:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Your SELECT returns 2 fields of VARCHAR2 datatypes NOT 1 field of rec_listbox datatype:
SQL> create or replace TYPE rec_listbox
2 as object (FELD_01 VARCHAR2(250),
3 FELD_02 VARCHAR2(250));
4 /
Type created.
SQL> create or replace TYPE tab_listbox as table of rec_listbox;
2 /
Type created.
SQL> CREATE OR REPLACE
2 FUNCTION f_listbox(p_SELECT_Statement IN VARCHAR2)
3 RETURN tab_listbox PIPELINED IS
4 type r_cursor is ref cursor;
5 c_LISTBOX r_cursor;
6 DATA_ROW REC_LISTBOX;
7 BEGIN
8 OPEN c_LISTBOX FOR p_SELECT_Statement;
9 LOOP
10 FETCH c_LISTBOX INTO DATA_ROW;
11 EXIT WHEN c_LISTBOX%NOTFOUND;
12 PIPE ROW( DATA_ROW );
13 END LOOP;
14 CLOSE c_LISTBOX;
15 END F_LISTBOX;
16 /
Function created.
SQL> SELECT * FROM TABLE(f_listbox('SELECT ''A'', ''A'' FROM dual'));
SELECT * FROM TABLE(f_listbox('SELECT ''A'', ''A'' FROM dual'))
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MICHEL.F_LISTBOX", line 9
SQL> SELECT * FROM TABLE(f_listbox('SELECT rec_listbox(''A'', ''A'') FROM dual'));
FELD_01
---------------------------------------------------------------------------------
FELD_02
---------------------------------------------------------------------------------
A
A
Regards
Michel
[Updated on: Tue, 13 November 2012 05:45] Report message to a moderator
|
|
|
|