Home » SQL & PL/SQL » SQL & PL/SQL » Using SYS tables as a normal user (Oracle 10.2.0.1.0)
Using SYS tables as a normal user [message #407693] Thu, 11 June 2009 02:22 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Hi Friends,

I have written a procedure which uses a table dba_role_privs (belonging to SYS user). When I try compiling the procedure i get an Oracle error saying table or view doesn't exist. Please tell me can I compile this procedure without granting DBA rights to this user? Any alternatives?

Any help would be appreciated.

Thank You,
B Senthil Kumar
Re: Using SYS tables as a normal user [message #407694 is a reply to message #407693] Thu, 11 June 2009 02:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
If you have granted access though role, it won't work in procedure.
Grant it directly.

By
Vamsi
Re: Using SYS tables as a normal user [message #407697 is a reply to message #407694] Thu, 11 June 2009 02:45 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thanks Vamsi. So you mean to say, logging in as SYS user, I should explicitly GRANT SELECT on those tables to my normal user?
Re: Using SYS tables as a normal user [message #407705 is a reply to message #407697] Thu, 11 June 2009 03:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes.
If it's something that you'll need to do a lot, you'd be better off granting SELECT ON ... WITH GRANT OPTION to a DBA account, and then using that account to grant the privileges onwards to users - it avoids using the SYS account for routine DBA work.
Previous Topic: Meaning of cursor.delete
Next Topic: ORA-29283: invalid file operation
Goto Forum:
  


Current Time: Sun Dec 04 04:44:00 CST 2016

Total time taken to generate the page: 0.09904 seconds