You have a synonym to a synonym going there.

Your DESCRIPTION of your ENVIRONMENT doesn't match REALITY.


Read the output:

public synonym tmproc -> myowner.tmproc
myowner.tmproc -> myuser.tmproc

You <b>told me</b>
SQL> create public synonym TMProc for myowner.tmproc ;

Synonym created.


Well, <b>that is just not what is in place here!!!</b>.


Start over, clean up your example.  You have procedures in schemas that shouldn't have them.  You have synonyms pointing to synonyms.  You don't have the procedure in myowner like you say.  You don't have public synonyms as you describe.


I set up a clean test case -- this works <b>as advertised, perfectly</b>.  It only works when the procedure is in the right schema, the synonym points to the procedure in the correct schema and the other schema has not obscured the synonym!!! 

Look- this is your stuff I just cut and pasted here:

<quote>
11:13:45 SQL> execute tmproc('readtime',sysdate) ;
BEGIN tmproc('readtime',sysdate) ; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TMPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


11:13:59 SQL> execute myowner.TMPROC('readtime',sysdate) ;

PL/SQL procedure successfully completed.

SQL> select owner, SYNONYM_name, TBLE_OWNER, TABLE_NAME
from dba_synonyms where synonym_name like 'TMPROC';

OWNER       SYNONYM_NAME     TBLE_OWNER   TABLE_NAME
----------- ---------------- ------------ -------------MYUSER      TMPROC        
   MYOWNER      TMPROC

PUBLIC      TMPROC           MYOWNER      TMPROC


11:17:53 DBUSR> desc myowner.tmproc

</quote>


So, looking at the results from dba_synonyms.

MYOWNER.TMPPROC -> MYUSER.TMPROC
PUBLIC.TMPROC   -> MYOWNER.TMPROC

You execute tmproc, that is really tmproc, that is really myowner.tmproc, which is REALLY myuser.tmproc.

That is a synonym to a synonym.  

Now, when you run myowner.tmproc, that is really myuser.tmproc.  myuser.tmproc is really a procedure.


I'm not making this up!!!! it is facts based on your screen shots!

 

Run this:

drop user myowner cascade;
drop user test_owner cascade;

grant dba to myowner identified by mypass;
grant connect, resource to test_owner identified by test_pass;

connect myowner/mypass

CREATE OR REPLACE  PROCEDURE TMProc  ( Var_name in varchar2, var_value in varchar2) as
begin
   dbms_session.set_context( 'TMCtx', var_name, var_value );
end;
/

create or replace context TMCtx using TMproc ;

drop public synonym tmproc;
create public synonym TMProc for myowner.tmproc ;

grant execute on tmproc to public ;

connect test_owner/test_pass

desc tmproc

execute tmproc ('var',sysdate) ;

<b>you should get this</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user myowner cascade;

User dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user test_owner cascade;

User dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> grant dba to myowner identified by mypass;

Grant succeeded.

ops$tkyte@ORA817DEV.US.ORACLE.COM> grant connect, resource to test_owner identified by test_pass;

Grant succeeded.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect myowner/mypass
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
myowner@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
myowner@ORA817DEV.US.ORACLE.COM> set termout on
myowner@ORA817DEV.US.ORACLE.COM> 
myowner@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE  PROCEDURE TMProc  ( Var_name in varchar2, var_value in varchar2) as
  2  begin
  3     dbms_session.set_context( 'TMCtx', var_name, var_value );
  4  end;
  5  /

Procedure created.

myowner@ORA817DEV.US.ORACLE.COM> 
myowner@ORA817DEV.US.ORACLE.COM> create or replace context TMCtx using TMproc ;

Context created.

myowner@ORA817DEV.US.ORACLE.COM> 
myowner@ORA817DEV.US.ORACLE.COM> drop public synonym tmproc;

Synonym dropped.

myowner@ORA817DEV.US.ORACLE.COM> create public synonym TMProc for myowner.tmproc ;

Synonym created.

myowner@ORA817DEV.US.ORACLE.COM> 
myowner@ORA817DEV.US.ORACLE.COM> grant execute on tmproc to public ;

Grant succeeded.

myowner@ORA817DEV.US.ORACLE.COM> 
myowner@ORA817DEV.US.ORACLE.COM> @connect test_owner/test_pass
myowner@ORA817DEV.US.ORACLE.COM> set termout off
test_owner@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
test_owner@ORA817DEV.US.ORACLE.COM> set termout on
test_owner@ORA817DEV.US.ORACLE.COM> 
test_owner@ORA817DEV.US.ORACLE.COM> desc tmproc
PROCEDURE tmproc
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAR_NAME                       VARCHAR2                IN
 VAR_VALUE                      VARCHAR2                IN

test_owner@ORA817DEV.US.ORACLE.COM> 
test_owner@ORA817DEV.US.ORACLE.COM> execute tmproc ('var',sysdate) ;

PL/SQL procedure successfully completed.

test_owner@ORA817DEV.US.ORACLE.COM> 

<b>showing this works as it shoud</b>

 

I cannot reproduce your error exactly, but I see what the problem is.  

the owner of the view needs to be granted the select priv WITH GRANT OPTION if they want to be able to convey select on that to others.  Here is an example:

ops$tkyte@ORA920> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

ops$tkyte@ORA920> create user a identified by a;
User created.

ops$tkyte@ORA920> grant create view, create session to a;
Grant succeeded.

ops$tkyte@ORA920> create user b identified by b;
User created.

ops$tkyte@ORA920> grant create session to b;
Grant succeeded.

ops$tkyte@ORA920> create table t ( x int );
Table created.

ops$tkyte@ORA920> grant select on t to a;
Grant succeeded.

ops$tkyte@ORA920> @connect a/a

a@ORA920> create view v as select * from ops$tkyte.t;
View created.

a@ORA920> grant select on v to b;
grant select on v to b
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'OPS$TKYTE.T'

<b>that is what you should be getting -- without a test case from you with create users and all -- I did not reproduce your exact example but close enough</b>


b@ORA920> @connect /

ops$tkyte@ORA920> grant select on t to a WITH GRANT OPTION;
Grant succeeded.

ops$tkyte@ORA920> @connect a/a

a@ORA920> grant select on v to b;
Grant succeeded.

<b>now it works</b>

a@ORA920> @connect b/b

b@ORA920> select * from a.v;
no rows selected

<b>and B can in fact access the table.</b>
 

it is NOT linked to the schema - that is what I said - a context is NOT OWNED by anyone.

A context points to some procedure/package/function in some schema (that is what you see in dba_context)

that owner is NOT the owner of the context, it is the owner of the procedure the context may be set by.  context's are "global"


I said you can use that column (PROBABLY) to find your contexts of interest.  You'll have to generate the sql for them from that.

for example, this would PROBABLY get all of the context's that the SYSMAN schema was interested in (probably, not definitely)

ops$tkyte%ORA10GR2> column ddl format a50 word_wrapped
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'CONTEXT', namespace ) ddl  from dba_context
  2  where schema = 'SYSMAN';

DDL
--------------------------------------------------
CREATE OR REPLACE CONTEXT "EM_GLOBAL_CONTEXT"
USING "SYSMAN"."SETEMUSERCONTEXT" ACCESSED
GLOBALLY


CREATE OR REPLACE CONTEXT "EM_USER_CONTEXT" USING
"SYSMAN"."SETEMUSERCONTEXT"