Home » SQL & PL/SQL » SQL & PL/SQL » which privilege i need (oracle 10g)
which privilege i need [message #445518] Tue, 02 March 2010 05:39 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.
Can you tell me how to get output without SYSDBA priv? and advice me what are other privileges i need?
Please find the code snippet.

CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
BEGIN
-- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
    EXIT WHEN v_doc IS NULL;
    v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 END LOOP;
   v_lob                      := v_lob || '';
   return v_lob;
   end;


Regards,
Madhavi.
Re: which privilege i need [message #445519 is a reply to message #445518] Tue, 02 March 2010 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't get anything for SYSDBA or not.
Post your version number with 4 decimals.

Regards
Michel
Re: which privilege i need [message #445521 is a reply to message #445518] Tue, 02 March 2010 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I get the whole stuff with:
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

Regards
Michel
Re: which privilege i need [message #445522 is a reply to message #445518] Tue, 02 March 2010 05:53 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Thanks for reply.

The version is 10.2.0.1.0.

Regards,
Madhavi.
Re: which privilege i need [message #445524 is a reply to message #445518] Tue, 02 March 2010 05:59 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,

Above one is part of my requirement. I need to export total database in the object level (by passing object type, name, owner as parameters). I able to export all schema objects successfully (i use Invoker rights) in the current database. Now iam trying for named objects. Next i'll try for dependent objects, granted objects,....
I need to provide single program for that. I think get_ddl used in browser interface (directly in sql).
Is my choice (fetch instead of get_xxxx) correct?

I need to use METADATA api for that without mention any data dictionaries (for each object need to connect corresponding data dictionary).

Regards,
Madhavi.

[Updated on: Tue, 02 March 2010 06:01]

Report message to a moderator

Re: which privilege i need [message #445525 is a reply to message #445522] Tue, 02 March 2010 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From DBMS_METADATA documentation, Security Model:

Quote:
The object views of the Oracle metadata model implement security as follows:
...
* SYS and users with SELECT_CATALOG_ROLE can see all objects
...

It seems you didn't grant SELECT_CATALOG_ROLE to your user.

In addition, your function must be created with AUTHID CURRENT_USER as role are not enabled in default AUTHID DEFINER function:
SQL> revoke  select_catalog_role from michel
  2  ;

Revoke succeeded.

SQL> CREATE OR REPLACE FUNCTION f
  2  RETURN CLOB
  3  AS
  4  v_meta_handle NUMBER;
  5  v_meta_handle_trans NUMBER;
  6  V_DOC CLOB;
  7  V_LOB CLOB;
  8  BEGIN
  9  -- Specify the object type.
 10    v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
 11    v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 12   LOOP
 13      v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
 14      EXIT WHEN v_doc IS NULL;
 15      v_lob   := v_lob ||v_doc;
 16      v_lob   := v_lob || '/';
 17      -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 18   END LOOP;
 19     v_lob                      := v_lob || '';
 20     return v_lob;
 21     end;
 22  /

Function created.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------


1 row selected.

SQL> grant select_catalog_role to michel
  2  ;

Grant succeeded.

SQL> alter function f compile;

Function altered.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------


1 row selected.

SQL> CREATE OR REPLACE FUNCTION f
  2  RETURN CLOB
  3  AUTHID CURRENT_USER
  4  AS
  5  v_meta_handle NUMBER;
  6  v_meta_handle_trans NUMBER;
  7  V_DOC CLOB;
  8  V_LOB CLOB;
  9  BEGIN
 10  -- Specify the object type.
 11    v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
 12    v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 13   LOOP
 14      v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
 15      EXIT WHEN v_doc IS NULL;
 16      v_lob   := v_lob ||v_doc;
 17      v_lob   := v_lob || '/';
 18      -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 19   END LOOP;
 20     v_lob                      := v_lob || '';
 21     return v_lob;
 22     end;
 23  /

Function created.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------

  CREATE TABLESPACE "SYSTEM" DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSTEM01.DBF' SIZE 524288000
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
 /
  CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE
  'C:\ORACLE\BASES\MIKA\UNDOTBS01.DBF' SIZE 262144000
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  'C:\ORACLE\BASES\MIKA\UNDOTBS01.DBF' RESIZE 461373440
 /
  CREATE TABLESPACE "SYSAUX" DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSAUX01.DBF' SIZE 209715200
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSAUX01.DBF' RESIZE 304087040
 /
...

Regards
Michel




Regards
Michel

Re: which privilege i need [message #445530 is a reply to message #445518] Tue, 02 March 2010 06:12 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Michel,
Thank you for support. I already have SELECT_CATALOG_ROLE privilege but didn't use invoker rights (AUTHID CURRENT_USER) for named objects. Now i make my program with invoker rights. I got output.
I have a doubt....
Named objects doesn't belong to any schema. Why should we use invoker rights for named objects also?
Regards,
Madhavi.
Re: which privilege i need [message #445531 is a reply to message #445530] Tue, 02 March 2010 06:19 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why should we use invoker rights for named objects also?

As I said roles are not enabled in AUTHID DEFINER function, you have to use AUTHID CURRENT_USER just to activate the role as this is a requirement of the function (which is outside ANSI standard but this is another question).
I bet the function checks the role in SESSION_ROLES view which only sees the activated roles.

Regards
Michel
Previous Topic: Duplicate
Next Topic: How to consume exposed services of sharepoint in PL/SQL?
Goto Forum:
  


Current Time: Sun Dec 11 06:28:33 CST 2016

Total time taken to generate the page: 0.11290 seconds