Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00942 Grant access to Role for Function (10g)
ORA-00942 Grant access to Role for Function [message #437486] Mon, 04 January 2010 13:25 Go to next message
Monica
Messages: 37
Registered: February 2000
Member
I think I know the answer but I would like to double check. I have a user that has been granted a role which gives the user select access to a users schema/table. If I run select * from schema.table it works fine.
When I use a function with the same select I get ORA-00942 error. If I grant the user select access to the schema/table it works. It will not work using a role via function.

Is there anyway around giving the user direct select access. I would prefer to use a role.

Example of function:
CREATE OR REPLACE FUNCTION EMMA_MAIN."SPCA_EXCLUDE" (
in_ch_id IN NUMBER,
IN_ckc_id IN NUMBER,
IN_MEASURER IN VARCHAR2
)
RETURN VARCHAR2
IS
RESULT VARCHAR2 (1);
BEGIN
RESULT := NULL;

SELECT (CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END) INTO RESULT
FROM CSTM_MAIN.SPACE_USER_CHANNELS
WHERE CH_ID = in_ch_id
AND CKC_ID = IN_ckc_id
AND MEASURER = IN_MEASURER
AND SELECTION_LABEL = 'SPCA Excludes';

RETURN RESULT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN SUBSTR(RESULT,3,100);
WHEN OTHERS THEN
RETURN SUBSTR(RESULT,3,100);
END;
/
Re: ORA-00942 Grant access to Role for Function [message #437487 is a reply to message #437486] Mon, 04 January 2010 13:28 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nope. Grants acquired via roles won't work in PL/SQL, so you *must* grant those privileges directly to that user.
Re: ORA-00942 Grant access to Role for Function [message #437488 is a reply to message #437486] Mon, 04 January 2010 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 24913
Registered: January 2009
Senior Member
>Is there anyway around giving the user direct select access
If the function is owned by table owner, then do
GRANT EXECUTE ON SELECT_FUNCTION TO OTHER_USER;
Re: ORA-00942 Grant access to Role for Function [message #437490 is a reply to message #437486] Mon, 04 January 2010 13:44 Go to previous message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition "WHEN OTHERS THEN RETURN SUBSTR(RESULT,3,100);" is just silly, it hides the error and will lead you to think the function worked.

Also if the result is just Y or N, you don't need to count the number of select rows just check if there is at least 1.

In the end, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: I have a packaged procedure
Next Topic: Parallel DML is not working.
Goto Forum:
  


Current Time: Fri Sep 30 12:25:39 CDT 2016

Total time taken to generate the page: 0.08493 seconds