Home » SQL & PL/SQL » SQL & PL/SQL » dbms_stats
dbms_stats [message #283498] Tue, 27 November 2007 05:56 Go to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi,

I have a user mdbprod to which DBA role is granted.

I have created a generalise procedure in this user so as to colect the stats on any table. Then i have granted execute privilege to public and created a public synonym as well.

create or replace procedure gatherstats(ownname varchar2, tabname varchar2)
as
begin
dbms_stats.gather_table_stats(ownname,tabname);
end;
/

Procedure created.

Now with in the mdbprod user, i am able to analyze the scott.emp table as

SQL> analyze table scott.emp estimate statistics sample 10 rows;

Table analyzed.

but when i connect to scott user and try to execute the ablove procedure,

SQL> sho user
USER is "SCOTT"

exec gatherstats(user,'emp')
BEGIN gatherstats(user,'emp'); END;

*
ERROR at line 1:
ORA-20000: Object EMP does not exist or insufficient privileges to analyze
ORA-06512: at "SYS.DBMS_STATS", line 7161
ORA-06512: at "SYS.DBMS_STATS", line 7174
ORA-06512: at "MDBPROD.GATHERSTATS", line 5
ORA-06512: at line 1

I am unable to understand the issue.. why this is not working.

The below statement works perfectly.

SQL> exec dbms_stats.gather_table_stats('scott','emp')

PL/SQL procedure successfully completed.
Re: dbms_stats [message #283508 is a reply to message #283498] Tue, 27 November 2007 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: dbms_stats [message #283516 is a reply to message #283498] Tue, 27 November 2007 06:53 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi Michel,

Thanks for the link. I am unable to understand how it is affecting in the procedure i created.

user MDBPROD has all the system privileges.
I tried giving explictly all the object privileges on SCOTT.EMP to mdbprod .. still not working.

Can you please elaborate where i am making mistake .. plzz
Re: dbms_stats [message #283517 is a reply to message #283516] Tue, 27 November 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

user MDBPROD has all the system privileges

...Through a role that is not enable in the procedure.

Regards
Michel
Re: dbms_stats [message #283521 is a reply to message #283498] Tue, 27 November 2007 07:14 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
But those are system privileges and not object privileges.

I gave all the possible object privileges on SCOTT.EMP to MDBPROD... but it is not working.
scott.emp is never getting referenced in gatherstats procedure,
the parameters are getting used to call dbms_stats.gather_table_stats packaged procedure and this package is created with AUTHID clause.
Re: dbms_stats [message #283525 is a reply to message #283521] Tue, 27 November 2007 07:22 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I could be mistaken, but


 create or replace procedure gatherstats(ownname varchar2, tabname varchar2) AUTHID CURRENT_USER



Adding AUTHID CURRENT_USER to the create procedure, could to the trick.

More info

[Updated on: Tue, 27 November 2007 07:23]

Report message to a moderator

Re: dbms_stats [message #283528 is a reply to message #283498] Tue, 27 November 2007 07:28 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Yes , it is doing the trick .. i tried this earlier but i am not able to digest why it is required.
Re: dbms_stats [message #283530 is a reply to message #283521] Tue, 27 November 2007 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

this package is created with AUTHID clause.

This is not what I see in your post.
So post the ACTUAL code and environment and so on.

Regards
Michel
Re: dbms_stats [message #283534 is a reply to message #283498] Tue, 27 November 2007 07:32 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
i meant DBMS_STATS is created with AUTHID clause and not my procedure .

I tried adding AUTHID clause in my procedure gatherstats and it worked but what is the need to add AUTHID clause ..

Re: dbms_stats [message #283535 is a reply to message #283534] Tue, 27 November 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because, as I said, with authid definer (the default), roles are not enabled.

Regards
Michel
Re: dbms_stats [message #283536 is a reply to message #283534] Tue, 27 November 2007 07:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Did you read Marc mentioned link ?
Re: dbms_stats [message #283538 is a reply to message #283535] Tue, 27 November 2007 07:37 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Tue, 27 November 2007 14:34

Because, as I said, with authid definer (the default), roles are not enabled.

Regards
Michel



I'm not quite sure I understand your point.

Creating a procedure/package/function with AUTHID CURRENT_USER would use the privileges of the user running the p/p/f in stead of the definer. As if the p/p/f has been created by the user.
So I don't see the role part ???
Re: dbms_stats [message #283539 is a reply to message #283498] Tue, 27 November 2007 07:37 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
case 1 ( procedure created without AUTHID clause )

SQL> sho user
USER is "MDBPROD"
SQL>

SQL> create or replace procedure gatherstats(ownname varchar2, tabname varchar2)
2 as
3 begin
4 dbms_stats.gather_table_stats(ownname,tabname);
5 end;
6 /

Procedure created.

conn scott/tiger

SQL> sho user
USER is "SCOTT"
SQL>

SQL> exec gatherstats(user,'emp')
BEGIN gatherstats(user,'emp'); END;

*
ERROR at line 1:
ORA-20000: Object EMP does not exist or insufficient privileges to analyze
ORA-06512: at "SYS.DBMS_STATS", line 7161
ORA-06512: at "SYS.DBMS_STATS", line 7174
ORA-06512: at "MDBPROD.GATHERSTATS", line 4
ORA-06512: at line 1


case 2 ( procedure created with AUTHID clause )

SQL> sho user
USER is "MDBPROD"
SQL> create or replace procedure gatherstats(ownname varchar2, tabname varchar2) authid current_user 2 as
3 begin
4 dbms_stats.gather_table_stats(ownname,tabname);
5 end;
6 /

Procedure created.

SQL> sho user
USER is "SCOTT"
SQL> exec gatherstats(user,'emp')

PL/SQL procedure successfully completed.

SQL>

Re: dbms_stats [message #283543 is a reply to message #283498] Tue, 27 November 2007 07:44 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
In case 1, i dont understand which object privileges and on which object is missing.

This is my concern.
Re: dbms_stats [message #283550 is a reply to message #283543] Tue, 27 November 2007 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Creating a procedure/package/function with AUTHID CURRENT_USER would use the privileges of the user running the p/p/f in stead of the definer. As if the p/p/f has been created by the user.

of the caller
In your case the caller of dbms_stat is your procedure.
If you define it without AUTHID CURRENT_USER, then the owner of the procedure WITHOUT ITS ROLES is the caller of dbms_stats.

Regards
Michel

[Updated on: Tue, 27 November 2007 08:05]

Report message to a moderator

Re: dbms_stats [message #283667 is a reply to message #283498] Tue, 27 November 2007 22:22 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Thanks Michel.

As soon as i gave analyze any system privilege to MDBPROD user, the procedure created in case 1 ( without authid clause ) succeded when i called this proc from the user SCOTT.

The conclusion which i am able to draw is the object level priveleges given through roles are still available when invokers rights are used but system level are not as in the above case the MDBPROD user had the DBA role for analyzing any table but i had to specifically/explicitly give the rightss.

Am i correct ...
Re: dbms_stats [message #283697 is a reply to message #283667] Wed, 28 November 2007 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

MDBPROD user had the DBA role for analyzing any table but i had to specifically/explicitly give the rightss

This is what I said in all my posts.

Regards
Michel
Re: dbms_stats [message #283706 is a reply to message #283498] Wed, 28 November 2007 00:35 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi,

why is object level privileges granted through role available when a procedure created with AUTHID CURRENT_USER clause is invoked from other proc/user and why not system level privileges are available granted through roles.

Re: dbms_stats [message #283712 is a reply to message #283706] Wed, 28 November 2007 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a simple test case with dummy functions and table and show your point.

Regards
Michel
Re: dbms_stats [message #283985 is a reply to message #283498] Wed, 28 November 2007 09:16 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi,

Please check the code below, it suggest that the object privileges through roles are available when a procedure created with AUTHID current_user is invoked from any block.


SQL> sho user
USER is "RAJIV"

create table xyz( a number);

SQL> create or replace procedure p1
2 as
3 v_a number;
4 begin
5 select count(*) into v_a from xyz;
6 dbms_output.put_Line(v_a);
7 end;
8 /

Procedure created.


grant select on xyz to sachdev;

create role abc;
grant select on xyz to abc;
grant abc to scott;
grant execute on p1 to sachdev;



SQL> sho user
USER is "SACHDEV"

1 create or replace procedure p2 authid current_user as
2 cursor c1 is select * from rajiv.xyz; 3 begin
4 open c1;
5 rajiv.p1;
6* end;
SQL> /

Procedure created.


SQL> grant execute on p2 to scott;

Grant succeeded.


SQL> sho user
USER is "SCOTT"
SQL> set serverout on
SQL> begin
2 sachdev.p2;
3 end;
4 /
0
Re: dbms_stats [message #284010 is a reply to message #283985] Wed, 28 November 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Inside p2 you are executing with the full privileges of scott and so select on xyz through abc.
Inside p1 you are executing as rajiv who can query his table.

You can do the same thing with a system privilege.
I don't see where is the point.

Regards
Michel
Re: dbms_stats [message #284036 is a reply to message #284010] Wed, 28 November 2007 10:54 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Michel Cadot wrote on Wed, 28 November 2007 21:56

Inside p2 you are executing with the full privileges of scott and so select on xyz through abc.



Object privileges granted through role and not explicitly granted.

In the yesterday's case, i had to explicitly give the analyze any table system privilege to MDBPROD user while invoking the dbms_stats.gather_table_stats which is defined as AUTHID CURRENT_USER
Re: dbms_stats [message #284042 is a reply to message #284036] Wed, 28 November 2007 11:09 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are in the opposite configuration that what you had.
You had your procedure with definer authid (so without roles) that calls a procedure with authid caller (dbms_stat).
In your example, you have a current user authid procedure that calls a definer authid procedure.

Regards
Michel

[Updated on: Wed, 28 November 2007 11:10]

Report message to a moderator

Previous Topic: Sorting UNION results
Next Topic: PLS-00103
Goto Forum:
  


Current Time: Thu Dec 08 06:40:21 CST 2016

Total time taken to generate the page: 0.06710 seconds