Home » RDBMS Server » Security » automatic generated grants on sys_plsql_xxxx_yy_z (Oracle Standard Edition 11.2.0.1.0 & 11.2.0.3.0)
automatic generated grants on sys_plsql_xxxx_yy_z [message #570622] Tue, 13 November 2012 01:48 Go to next message
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 #570623 is a reply to message #570622] Tue, 13 November 2012 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Those are objects created for pipelined functions returning PL/SQL types and not SQL ones (created by CREATE TYPE).
Change for SQL types and you will no more have this problem (I think).

Regards
Michel
Re: automatic generated grants on sys_plsql_xxxx_yy_z [message #570625 is a reply to message #570623] Tue, 13 November 2012 02:55 Go to previous messageGo to next message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Thanks Michel,

I will try this and tell you.

Regards
Rudi
Re: automatic generated grants on sys_plsql_xxxx_yy_z [message #570630 is a reply to message #570622] Tue, 13 November 2012 04:30 Go to previous messageGo to next message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Hello,

I replaced the plsql-types with sql-types and now getting
SQL-Fehler: ORA-00932: inconsistent datatypes: - expected , - got
ORA-06512: in "RECORD_GROUP.F_LISTBOX", Zeile 11
00932. 00000 -  "inconsistent datatypes: expected %s got %s"


The Code:
Record Type
create or replace
TYPE   record_group.rec_listbox                                      
as object  (FELD_01 VARCHAR2(250),
            FELD_02 VARCHAR2(250));

Table Type
create or replace
TYPE               record_group.tab_listbox                                         
as table of RECORD_GROUP.rec_listbox;

Function
CREATE OR REPLACE
FUNCTION  record_group.f_listbox(p_SELECT_Statement IN VARCHAR2)
RETURN record_group.tab_listbox PIPELINED IS
  type r_cursor is ref cursor;
  c_LISTBOX  r_cursor;  DATA_ROW  RECORD_GROUP.REC_LISTBOX;
  
BEGIN

  OPEN c_LISTBOX FOR p_SELECT_Statement;
    LOOP
      FETCH c_LISTBOX INTO DATA_ROW;
      EXIT WHEN c_LISTBOX%NOTFOUND;
      PIPE ROW( DATA_ROW );
    END LOOP;
  CLOSE c_LISTBOX;
END F_LISTBOX;

The calling Statement
SELECT *
FROM   TABLE(record_group.f_listbox('SELECT ''A'',
                                            ''A''
                                     FROM    dual'));


8 eyes Shocked were searching at my monitor (and I didn't counted the glasses) but no one can see the error. Can you?
In the function - which was working fine - we only replaced the former plsql-types with the new generated sql-types.
Regards
Rudi
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 messageGo to next message
Michel Cadot
Messages: 68624
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

Re: automatic generated grants on sys_plsql_xxxx_yy_z [message #570638 is a reply to message #570622] Tue, 13 November 2012 06:48 Go to previous message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Michel

many thanks. I oftener should use sql types. .....
And great, it works, but now we have to change any oracle forms which is using those functions to generate record groups.
The management has to make a decision.

Regards
Rudi
Previous Topic: Differenece between grant all on any table and grant all on table_name
Next Topic: Database Vault
Goto Forum:
  


Current Time: Thu Mar 28 06:32:54 CDT 2024