Home » RDBMS Server » Security » grant privilege for package execute
grant privilege for package execute [message #49862] Thu, 14 February 2002 09:37 Go to next message
denise chase
Messages: 2
Registered: February 2002
Junior Member
Within a database, we are having difficulties granting and EXECUTE privilege to a package object. We are using the SYS user to grant the privilege to another user set up within the database. The error message we get is:
ORA-01031 Insufficient privileges
You do not have enough privileges to perform this operation. You must have the appropriate system and object privileges to create, edit, or drop database objects or objects outside your schema.
What privileges does SYS need to do this operation to another user?
Re: grant privilege for package execute [message #49863 is a reply to message #49862] Thu, 14 February 2002 10:56 Go to previous messageGo to next message
Rajesh Aialavajjala
Messages: 15
Registered: January 2002
Junior Member
Greetings Denise!!

It appears that the package that you are seeking to grant the EXECUTE privilege on is not owned by the user SYS. As you will know, database privileges fall into two categories viz:

System Privileges (CREATE,ALTER,DROP etc...)
Object Privileges (SELECT,INSERT,UPDATE etc...)

with the chief distinction being that SYSTEM privileges can be granted by a user with the DBA role attached (typically SYS or SYSTEM), and OBJECT privileges being granted ONLY by the OWNER of the object in question. For example, the following GRANT statement issued by SYS would raise an error stating 'Insufficient Privileges'

SQL>conn sys/change_on_install
SQL>grant select on scott.emp to demo;

The error is owing to the fact that the object EMP is owned by SCOTT and only SCOTT is permitted to grant any object privileges in respect of the table EMP.

Attempt to issue the EXECUTE privilege while connected as the owner of the package.

I hope this information helps.

Best Wishes,
Rajesh Aialavajjala
Re: grant privilege for package execute [message #49867 is a reply to message #49863] Thu, 14 February 2002 13:54 Go to previous messageGo to next message
denise chase
Messages: 2
Registered: February 2002
Junior Member
Thank you Rajesh. We ended up compiling the package under the SYS user and then granted the EXECUTE privilege to the other user. When we tried to grant the privilege when signed on as the other user we got the error message ORA 01749 you may not grant privileges to yourself. It seemed we were in a loop but after reading your reply, it seemed to make sense to make SYS the owner.
Denise
Re: grant privilege for package execute [message #49871 is a reply to message #49867] Thu, 14 February 2002 18:41 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
1) connect to user1

2)compile package (say pkg1)

3)run this command

SQL> grant execute on pkg1 to sys with grant option

4)connect to sys

5) now , you can grant execute to other users.

sql> grant execute on user1.pkg1 to user2;
Previous Topic: Privilege for Creating Package\Procedure
Next Topic: SYSDBA INTERNAL Problem.
Goto Forum:
  


Current Time: Thu May 26 15:34:41 CDT 2022