Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031: insufficient privileges
ORA-01031: insufficient privileges [message #291022] Wed, 02 January 2008 15:20 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
I created a view under user SHER:

CREATE OR REPLACE VIEW SHER.VW_SHER_GT_TYPE
(
SHER_GT_TYPE_ID ,
SHER_GT_RM_CLASS_ID ,
RM_TYPE_NAME ,
RM_TYPE_DESC ,
AS
SELECT
p.SHER_GT_TYPE_ID ,
p.SHER_GT_RM_CLASS_ID ,
p.RM_TYPE_NAME ,
i.RM_TYPE_DESC ,
FROM inv.prdct i,
inv.prdct_rsrc_mapping prm,
sher.sher_gt_rm_type p
WHERE
i.prdct_id = prm.prdct_id
AND p.sher_GT_TYPE_ID = prm.rsrc_set_id

(user SHER have select on inv.prdct and inv.prdct_rsrc_mapping)
and granted:

grant select on SHER.VW_SHER_GT_TYPE to TED;
grant select on inv.prdct_rsrc_mapping to TED;
grant select on sher.sher_gt_rm_type to TED;
grant select on inv.prdct to TED;

but when I try to select from the view from user TED, I'm getting ORA-01031: insufficient privileges

Any idea what could be the problem?

Thanks,
GK
Re: ORA-01031: insufficient privileges [message #291024 is a reply to message #291022] Wed, 02 January 2008 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SHER must have the privilege with GRANT option.

Regards
Michel

[Updated on: Wed, 02 January 2008 15:25]

Report message to a moderator

Re: ORA-01031: insufficient privileges [message #291026 is a reply to message #291022] Wed, 02 January 2008 15:33 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I'm granting all the privileges as SYS user.
Re: ORA-01031: insufficient privileges [message #291029 is a reply to message #291026] Wed, 02 January 2008 15:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is very bad to use SYS for anything but maintenance.

SYS is here irrelevant. The owner of the view is SHER.

Regards
Michel
Re: ORA-01031: insufficient privileges [message #291031 is a reply to message #291022] Wed, 02 January 2008 15:51 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
But my question is, when you grant select privilege to all the tables and views as sys user it should work but its failing.
-GK
Re: ORA-01031: insufficient privileges [message #291102 is a reply to message #291031] Thu, 03 January 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you didn't post the actual session you executed I have to guess.

1/ grant statements executed by SYS succeeded
2/ TED is allowed to query SHER view but you misunderstood the error message. It didn't say you that TED is not allowed to query the view it says:
3/ SHER has not the privilege to allow TED to see INV data through his view. To be able to do this, SHER must have the privilege WITH GRANT OPTION.

Regards
Michel
Re: ORA-01031: insufficient privileges [message #291264 is a reply to message #291022] Thu, 03 January 2008 09:52 Go to previous message
caprikar
Messages: 226
Registered: March 2007
Senior Member
I got it. Thanks a lot.
-GK
Previous Topic: Order by clause doesn't work as wanted
Next Topic: Date
Goto Forum:
  


Current Time: Sun Dec 04 18:32:09 CST 2016

Total time taken to generate the page: 0.06201 seconds