Link to home
Start Free TrialLog in
Avatar of jl66
jl66Flag for United States of America

asked on

what privileges needed for S2 for this function (Oracle 12c)?

I had this question after viewing grant user/role question.

Please read the above link for the 1st part to setup the test case.

Continue to 2nd part:
For simplicity, still use "sys" one more time
sqlplus login as sys

CREATE OR REPLACE FUNCTION S2.GET_ID
  RETURN number AS
  v_id   number;
BEGIN
  SELECT id
    INTO v_id
  FROM s1.t1;

  RETURN v_id;
END;
/

If compiling, the error showed
"PL/SQL: ORA-00942: table or view does not exist"
From 1st part, I have granted the privilege below:
grant select on s1.t1 to s2;

Questions:
1) why does it complain that it could not see the s1.t1
2) the final result is that I like to compile and execute "GET_ID" logging in as S2. What kind of privileges does S2 need? I am talking about the least privileges to execute the function as S2.

If any guru can shed some light on it, I will greatly appreciate it.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jl66

ASKER

Thanks a lot. This is what I wanted.