Home » SQL & PL/SQL » SQL & PL/SQL » Cannot View Package Body and [invoker_rights] option on the obect PACKAGE (merged cross-posts)
Cannot View Package Body and [invoker_rights] option on the obect PACKAGE (merged cross-posts) [message #140010] Fri, 30 September 2005 08:35 Go to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
We had package M3LETTER own by schema MOD03 and can view from it using PL/SQL Developer tool and also using the SQL*Plus by

SELECT text FROM all_source WHERE name = '<objectname>';


Our dba had issued the command

GRANT EXECUTE ON MOD03.M3LETTER TO AppUser;


But still could not have view the package body for MOD03.M3LETTER package. What are we missing? Thanks.
Re: Cannot View Package Body [message #140024 is a reply to message #140010] Fri, 30 September 2005 09:05 Go to previous messageGo to next message
somnath1974
Messages: 15
Registered: July 2005
Junior Member
Hi,
You probably do not have a public synonym on the package. If the permissions are okay then you could either invoke it with execute schema.object name or directly by object name should you have created the public synonym.

Hope this helps !!

Best Regards,
Somnath
Re: Cannot View Package Body [message #140029 is a reply to message #140024] Fri, 30 September 2005 09:22 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
You have received an EXECUTE command, that goes for the specification. That's the power of packages.

I'm not sure about this (search AskTom for it) but you could try this:
- GRANT an ALTER ANY ... privilege to your user.
- GRANT a SELECT ON DBA_SOURCE, I believe that's not restricted.

But again, I'm not sure. Second opinions anyone?

MHE
Re: Cannot View Package Body [message #140043 is a reply to message #140024] Fri, 30 September 2005 10:18 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
Thank you.
Re: Cannot View Package Body [message #140045 is a reply to message #140029] Fri, 30 September 2005 10:20 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member

If I am able to do a SELECT on ALL_SOURCE for some objects that means that I had been granted privileges right?
Re: Cannot View Package Body [message #140808 is a reply to message #140024] Wed, 05 October 2005 13:28 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member

The DBAs tried to create a synonym and grant that synomyn to the user. But still did not work.
Re: Cannot View Package Body [message #140809 is a reply to message #140043] Wed, 05 October 2005 13:40 Go to previous messageGo to next message
joy_division
Messages: 4952
Registered: February 2005
Location: East Coast USA
Senior Member
Was it a synonym in the schema MODO3 or a public synonym?
Re: Cannot View Package Body [message #140817 is a reply to message #140809] Wed, 05 October 2005 14:05 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
Here is what our DBA had issued:

create synonym appuser.m2letter for mod2k.m2letter;

grant all on mod03.m2letter to appuser;

[Updated on: Wed, 05 October 2005 19:54]

Report message to a moderator

Re: Cannot View Package Body [message #140821 is a reply to message #140817] Wed, 05 October 2005 14:46 Go to previous messageGo to next message
joy_division
Messages: 4952
Registered: February 2005
Location: East Coast USA
Senior Member
I have removed my answer because after going back and reading your posting I don't really know what you want.
You mention a different schema and a different object than what you originally asked.

Like Maaher said, you have GRANT EXECUTE privileges on the procedure, which is the beauty of packages. You are not given the source code behind the package with GRANT EXECUTE. Your DBA's should know that.

[Updated on: Wed, 05 October 2005 15:00]

Report message to a moderator

Re: Cannot View Package Body [message #140845 is a reply to message #140821] Wed, 05 October 2005 19:57 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
I had edited the previous posted message with a correct one. Sorry for the confusion. I mistakenly updated with the wrong info.

Does it mean that our DBA should have granted privelege on DBA_SOURCE? Inorder for me to see the source code of the package body? Thanks.
Re: Cannot View Package Body [message #141012 is a reply to message #140845] Thu, 06 October 2005 10:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Things may be slightly different from one Oracle version to another, but in general it is by design that users can only view the package and not the package body of the source code of other users, except for users with dba privileges who can view everything. Granting select on dba_source would work, but it would probably be preferable to use a view or procedure to restrict the selection to certain criteria, as demonstrated below.

-- create users and grant privileges:
sys@ORA92> CREATE USER	mod03 IDENTIFIED BY mod03_pass
  2  /

User created.

sys@ORA92> CREATE USER appuser IDENTIFIED BY appuser_pass
  2  /

User created.

sys@ORA92> GRANT CONNECT, RESOURCE TO mod03, appuser
  2  /

Grant succeeded.


-- create view that allows
-- viewing of source code of mod03
-- and grant select on it to appuser:
sys@ORA92> CREATE OR REPLACE VIEW test_view AS
  2  SELECT * FROM dba_source
  3  WHERE  owner = 'MOD03'
  4  /

View created.

sys@ORA92> GRANT SELECT ON test_view TO appuser
  2  /

Grant succeeded.


-- user mod03 creates package,
-- procedure to view source code
-- and grants execute on both to user appuser:
sys@ORA92> CONNECT mod03/mod03_pass
Connected.
sys@ORA92> @ LOGIN
sys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
mod03@ORA92

mod03@ORA92> CREATE OR REPLACE PACKAGE m2letter
  2  AS
  3    FUNCTION test
  4  	 RETURN VARCHAR2;
  5  END m2letter;
  6  /

Package created.

mod03@ORA92> CREATE OR REPLACE PACKAGE BODY m2letter
  2  AS
  3    FUNCTION test
  4  	 RETURN VARCHAR2
  5    IS
  6    BEGIN
  7  	 RETURN 'this is mod03.m2letter.test';
  8    END test;
  9  END m2letter;
 10  /

Package body created.

mod03@ORA92> GRANT EXECUTE ON mod03.m2letter TO appuser
  2  /

Grant succeeded.

mod03@ORA92> CREATE OR REPLACE PROCEDURE mod03_source
  2    (p_ref  OUT SYS_REFCURSOR,
  3  	p_name IN  VARCHAR2 DEFAULT NULL)
  4  AS
  5  BEGIN
  6    OPEN p_ref FOR
  7    SELECT text
  8    FROM   user_source
  9    WHERE  name = NVL (UPPER (p_name), name)
 10    ORDER  BY type, line;
 11  END mod03_source;
 12  /

Procedure created.

mod03@ORA92> GRANT EXECUTE ON mod03_source TO appuser
  2  /

Grant succeeded.


-- user appuser can execute the function
-- within the package,
-- cannot view the package body through all_source,
-- but can view the package body by
-- executing the procedure created to do so
-- or by selecting from the view created to do so:
mod03@ORA92> CONNECT appuser/appuser_pass
Connected.
mod03@ORA92> @ LOGIN
mod03@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
appuser@ORA92

appuser@ORA92> SELECT mod03.m2letter.test FROM DUAL
  2  /

TEST
----------------------------------------------------------------------------------------------------
this is mod03.m2letter.test

appuser@ORA92> COLUMN text FORMAT A40 WORD_WRAPPED
appuser@ORA92> BREAK  ON type
appuser@ORA92> SELECT text
  2  FROM   all_source
  3  WHERE  owner = 'MOD03'
  4  AND    name = 'M2LETTER'
  5  ORDER  BY type, line
  6  /

TEXT
----------------------------------------
PACKAGE m2letter
AS
FUNCTION test
RETURN VARCHAR2;
END m2letter;

appuser@ORA92> VARIABLE g_ref REFCURSOR
appuser@ORA92> EXECUTE mod03.mod03_source (:g_ref, 'M2LETTER')

PL/SQL procedure successfully completed.

appuser@ORA92> PRINT g_ref

TEXT
----------------------------------------
PACKAGE m2letter
AS
FUNCTION test
RETURN VARCHAR2;
END m2letter;
PACKAGE BODY m2letter
AS
FUNCTION test
RETURN VARCHAR2
IS
BEGIN
RETURN 'this is mod03.m2letter.test';
END test;
END m2letter;

14 rows selected.

appuser@ORA92> SELECT text
  2  FROM   sys.test_view
  3  WHERE  name = 'M2LETTER'
  4  ORDER  BY type, line
  5  /

TEXT
----------------------------------------
PACKAGE m2letter
AS
FUNCTION test
RETURN VARCHAR2;
END m2letter;
PACKAGE BODY m2letter
AS
FUNCTION test
RETURN VARCHAR2
IS
BEGIN
RETURN 'this is mod03.m2letter.test';
END test;
END m2letter;

14 rows selected.


-- user scott, who has dba privileges
-- can view the package body by just
-- selecting from all_source:
appuser@ORA92> CONNECT scott/tiger
Connected.
appuser@ORA92> @ LOGIN
appuser@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92

scott@ORA92> SELECT text
  2  FROM   all_source
  3  WHERE  owner = 'MOD03'
  4  AND    name = 'M2LETTER'
  5  ORDER  BY type, line
  6  /

TEXT
----------------------------------------
PACKAGE m2letter
AS
FUNCTION test
RETURN VARCHAR2;
END m2letter;
PACKAGE BODY m2letter
AS
FUNCTION test
RETURN VARCHAR2
IS
BEGIN
RETURN 'this is mod03.m2letter.test';
END test;
END m2letter;

14 rows selected.

[Updated on: Thu, 06 October 2005 10:56]

Report message to a moderator

[invoker_rights] option on the object PACKAGE [message #141013 is a reply to message #140010] Thu, 06 October 2005 10:54 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
I had a problem on viewing the source code of the package body of an object. I was thinking it might be related to the [invoker_rights] option that was included when the package was created. The package was created as:

CREATE OR REPLACE PACKAGE MOD2K.m2letter AUTHID CURRENT_USER as
...


Is the [invoker_rights] was the reason why I cannot view the package body? A "GRANT ALL..." was already issued to my account but still cannot view the package body. Thanks.
Re: Cannot View Package Body [message #141047 is a reply to message #141012] Thu, 06 October 2005 14:29 Go to previous messageGo to next message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
Thank you Barbara we tried your solutions and it works for us.
Re: Cannot View Package Body [message #141058 is a reply to message #140010] Thu, 06 October 2005 14:53 Go to previous message
wtolentino
Messages: 348
Registered: March 2005
Senior Member
we also found other solution by issuing the:

GRANT CREATE ANY PROCEDURE TO AppUser;
Previous Topic: ORA-00972: identifier is too long
Next Topic: Overlapping Ranges
Goto Forum:
  


Current Time: Fri Sep 24 15:23:30 CDT 2021