OraFAQ Forum: Server Administration » Dbms

Home » RDBMS Server » Server Administration » Dbms_Metadata issue (11.2.0.1.0 Windos XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dbms_Metadata issue [message #519012]

Mon, 08 August 2011 21:51

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
Is there a method to get all priv of a table by Dbms_Metadata?

Report message to a moderator

Re: Dbms_Metadata issue [message #519013 is a reply to message #519012]

Mon, 08 August 2011 21:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there a method to get all priv of a table by Dbms_Metadata?
HUH?
tables do not have privileges

Report message to a moderator

Re: Dbms_Metadata issue [message #519028 is a reply to message #519013]

Tue, 09 August 2011 00:52

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
I means is how to get the SQL as flowing:

 grant select on tb_test to user_a;
 grant select on tb_test to user_b;

[Updated on: Tue, 09 August 2011 01:12]

Report message to a moderator

Re: Dbms_Metadata issue [message #519033 is a reply to message #519028]

Tue, 09 August 2011 01:32

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

SQL> SELECT 
  2  DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 
  3  'EMPLOYEES','HR') FROM DUAL;

DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMPLOYEES','HR')
--------------------------------------------------------------------------------

  GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"

  GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"



SQL> sho user
USER is "SYSTEM"
SQL> grant SELECT ON HR.EMPLOYEES TO scott;

Grant succeeded.

SQL> SELECT 
  2  DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 
  3  'EMPLOYEES','HR') FROM DUAL;

DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMPLOYEES','HR')
--------------------------------------------------------------------------------

  GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT"

  GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"

  GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"



SQL>

Report message to a moderator

Re: Dbms_Metadata issue [message #519036 is a reply to message #519028]

Tue, 09 August 2011 01:38

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> grant select , update on t to scott;

Grant succeeded.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

PL/SQL procedure successfully completed.

SQL> select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','T','MICHEL') from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','T','MICHEL')
------------------------------------------------------------------------------------------------------

  GRANT SELECT ON "MICHEL"."T" TO "SCOTT";

  GRANT UPDATE ON "MICHEL"."T" TO "SCOTT";


1 row selected.

Regards
Michel

Report message to a moderator

Re: Dbms_Metadata issue [message #519146 is a reply to message #519036]

Tue, 09 August 2011 20:49

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks a lot!

Report message to a moderator

Re: Dbms_Metadata issue [message #519309 is a reply to message #519036]

Thu, 11 August 2011 00:53

dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA

Senior Member

My question is why one needs to execute:

SQL> select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','T','MICHEL') from dual;
DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','T','MICHEL')

when we can have all this information from "user_tab_privs". Where we can specially use the above mentioned command.

Regards
Deepak

Report message to a moderator

Re: Dbms_Metadata issue [message #519317 is a reply to message #519309]

Thu, 11 August 2011 01:23

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I do not use it, just OP asked to do it with dbms_metadata.
Say it is another to do it, it does not hurt to have several ways.

Regards
Michel

Report message to a moderator

Previous Topic:	Better Approach For Recmpile the Object
Next Topic:	Invalidation of dependencies

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 02:53:47 CDT 2024