Hi,

I have a problem with one pubilc synonyme I using in a stored
procedures. I try to find the role of SP-execute-user with:

SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
grantee=(SELECT USER FROM DUAL);

But if I try to create the procedure with this statment, I get this
error :

LINE/COL ERROR
--------
-----------------------------------------------------------------
42/1 PL/SQL: SQL Statement ignored
42/38 PL/SQL: ORA-00942: Table or View does not exists

But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
this table.
I don't understand, what do I wrong?

Can anybody help me? Thanks in advance!

P.S. Sorry for my bad english! :-)

On 16 feb, 15:53, "nicranet" <n...@freenet.de> wrote:
> Hi,
>
> I have a problem with one pubilc synonyme I using in a stored
> procedures. I try to find the role of SP-execute-user with:
>
> SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
> grantee=(SELECT USER FROM DUAL);
>
> But if I try to create the procedure with this statment, I get this
> error :
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 42/1 PL/SQL: SQL Statement ignored
> 42/38 PL/SQL: ORA-00942: Table or View does not exists
>
> But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
> this table.
> I don't understand, what do I wrong?
>
> Can anybody help me? Thanks in advance!
>
> P.S. Sorry for my bad english! :-)

Grant the owner of the procedure access to the tables/view.
PL/SQL is anonimous; it does not know of roles, so you
need direct (a.k.a object) access

nicranet wrote:
> Hi,
>
> I have a problem with one pubilc synonyme I using in a stored
> procedures. I try to find the role of SP-execute-user with:
>
> SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
> grantee=(SELECT USER FROM DUAL);
>
> But if I try to create the procedure with this statment, I get this
> error :
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 42/1 PL/SQL: SQL Statement ignored
> 42/38 PL/SQL: ORA-00942: Table or View does not exists
>
> But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
> this table.
> I don't understand, what do I wrong?
>
> Can anybody help me? Thanks in advance!
>
> P.S. Sorry for my bad english! :-)

What Frank said and drop DUAL from your statement:

SELECT granted_role
INTO sRolle
FROM DBA_ROLE_PRIVS
WHERE grantee = USER;
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Frank van Bortel schreef:
> On 16 feb, 15:53, "nicranet" <n...@freenet.de> wrote:
>> Hi,
>>
>> I have a problem with one pubilc synonyme I using in a stored
>> procedures. I try to find the role of SP-execute-user with:
>>
>> SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
>> grantee=(SELECT USER FROM DUAL);
>>
>> But if I try to create the procedure with this statment, I get this
>> error :
>>
>> LINE/COL ERROR
>> --------
>> -----------------------------------------------------------------
>> 42/1 PL/SQL: SQL Statement ignored
>> 42/38 PL/SQL: ORA-00942: Table or View does not exists
>>
>> But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
>> this table.
>> I don't understand, what do I wrong?
>>
>> Can anybody help me? Thanks in advance!
>>
>> P.S. Sorry for my bad english! :-)
>
> Grant the owner of the procedure access to the tables/view.
> PL/SQL is anonimous; it does not know of roles, so you
> need direct (a.k.a object) access
>
Make that object grants - not access; obviously you need
access, but that will only happen if you're granted access


--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

nicranet wrote:
> Hi,
>
> I have a problem with one pubilc synonyme I using in a stored
> procedures. I try to find the role of SP-execute-user with:
>
> SELECT granted_role INTO sRolle FROM DBA_ROLE_PRIVS where
> grantee=(SELECT USER FROM DUAL);
>
> But if I try to create the procedure with this statment, I get this
> error :
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 42/1 PL/SQL: SQL Statement ignored
> 42/38 PL/SQL: ORA-00942: Table or View does not exists
>
> But DBA_ROLE_PRIVS is a public synonyme. It means everone can read
> this table.
> I don't understand, what do I wrong?
>
> Can anybody help me? Thanks in advance!
>
> P.S. Sorry for my bad english! :-)
>

A public synonym does not allow public access, it does not provide any access rights.

thanks for your solution, but I can't grant the direct access rights
to the system tables. I solve this problem in other wise: i select the
role with a simple sql-statement: "SELECT granted_role FROM
DBA_ROLE_PRIVS where grantee=USER" and pass the reply as paramater to
the stored procedure.
It works. :-)