Home » SQL & PL/SQL » SQL & PL/SQL » Grant Edit Privilege of Package to Another user
Grant Edit Privilege of Package to Another user [message #235644] Mon, 07 May 2007 09:48 Go to next message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Hi All,

I'm facing one issue with granting edit privilege to another user.

Scenario:

App01 DB user
Under App01 DB user there is one package apppack

I created one more user App02
Granted connect and resource roles to App02.

I granted select and dml permissions of all tables of App01 to App02.

I granted all permissions on functions and procedures.

Granted all on package also.



AS APP01 USER

SQL>GRANT ALL ON APPPACK TO APP02;


Still i'm able to see only the specification of the package from APP02 User.
How to see and edit the package body from APP02 user?

Whether Oracle supports this?

Janet Wilson
Re: Grant Edit Privilege of Package to Another user [message #235661 is a reply to message #235644] Mon, 07 May 2007 11:15 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Not supported - you can only grant EXECUTE and DEBUG on a package.
Re: Grant Edit Privilege of Package to Another user [message #235674 is a reply to message #235644] Mon, 07 May 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are only 6 ways to be able to see the source of a package body:
- you are the owner
- PUBLIC is the owner
- you have the DEBUG privilege on the package or package body
- you have the privilege CREATE ANY PROCEDURE
- you have the privilege DEBUG ANY PROCEDURE
- you have the SELECT privilege on DBA_SOURCE

Regards
Michel

[Updated on: Mon, 07 May 2007 11:40]

Report message to a moderator

Re: Grant Edit Privilege of Package to Another user [message #235875 is a reply to message #235674] Tue, 08 May 2007 04:20 Go to previous messageGo to next message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Thanks Frank and Michel.

I have one more issue related with the same scenario.

App01 and App02 schemas.

I want to grant create table privilege to App02 only in App01 schema.

I know that it is not possible.
Checked the Oracle Documentation and find that only two create table privilege exists.
1. CREATE TABLE
2. CREATE ANY TABLE

Can you please tell me whether the above statements are coreect or not.

Janet

[Updated on: Tue, 08 May 2007 04:21]

Report message to a moderator

Re: Grant Edit Privilege of Package to Another user [message #235882 is a reply to message #235875] Tue, 08 May 2007 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no other privilege.
Either you don't grant privilege to create table in another schema either you grant it to ALL schemas.

If you want to achieve this, you have to create a procedure in target schema that can create a table and grant execute privilege on this procedure to who you want.

But I wonder what can be the business need to grant such a privilege. Who needs (but DBA) to create a table in another schema?

Regards
Michel
Re: Grant Edit Privilege of Package to Another user [message #235905 is a reply to message #235882] Tue, 08 May 2007 06:27 Go to previous messageGo to next message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Thanks Michel...

We have one application and one db user for that.
There are many peoples from different departments accessing the DB (only one schema).

So we planned to create two more users and enable the auditing.

Anyway we are going to change our plan.

Thanks again

Janet Wilson

Re: Grant Edit Privilege of Package to Another user [message #235964 is a reply to message #235905] Tue, 08 May 2007 09:34 Go to previous messageGo to next message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Hi Michel,

Is it possible to grant the privilege to enable/disable a trigger or a job created on one schema to another user?
Janet
Re: Grant Edit Privilege of Package to Another user [message #235968 is a reply to message #235964] Tue, 08 May 2007 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is always none or all (ANY privilege).
...or create a procedure to do that.

Regards
Michel
Re: Grant Edit Privilege of Package to Another user [message #235981 is a reply to message #235968] Tue, 08 May 2007 10:33 Go to previous message
janetwilson
Messages: 48
Registered: June 2006
Location: St.Louis
Member
Michel, Thanks a lot
Previous Topic: Field name in table
Next Topic: A single SQL command run for 8 minutes ?? Something wrong ??
Goto Forum:
  


Current Time: Sat Dec 10 20:33:36 CST 2016

Total time taken to generate the page: 0.04768 seconds