Re: Confused about Synonyms

From: Gary England <gengland_at_hiwaay.net>
Date: 1997/09/25
Message-ID: <342B18CF.4D0@hiwaay.net>#1/1

Phil Cook wrote:
>
> Compiling pl/sql and creating views requires explicit
> access to the objects that are being referenced if you
> are not the owner of the objects. Granting access through
> a role is not sufficient, you must be granted either execute
> ( pl/sql ) or select ( tables ) on the objects you are referencing.

I have a system running with pl/sql procedure where access is ONLY via roles:

SQL> SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = 'BEGIN_MOVE';

GRANTEE                        OWNER                          TABLE_NAME
------------------------------ ------------------------------
------------------------------
GRANTOR                        PRIVILEGE                               

GRA

------------------------------ ----------------------------------------
---
RFI_DRIVER                     RFI                            BEGIN_MOVE
RFI                            EXECUTE                                 

NO

RFI_OFFICE                     RFI                            BEGIN_MOVE
RFI                            EXECUTE                                 

NO

To my knowledge, only the owner can CREATE OR REPLACE a stored procedure. It cannot be delegated.

> With pl/sql, once you have compiled using privileges to the
> objects, other users can access those objects through the
> pl/sql without having access to the objects themselves, they
> will only need execute on the pl/sql object(s).
>

Then, why do my users log "Insufficient Privileges" when they try to color outside of the lines? Having EXECUTE access to a pl/sql procedure does not guarantee you can modify the referenced tables.

What you say IS true for VIEWS. No access is needed to a table if you have access to the view.

> Phil Cook
> Certified Oracle DBA / Oracle Education DBA Masters
>
> Graham Thornton <dangermouse_at_prodigy.nospam.net> wrote in article
> <01bcca04$35a7ca80$b0f7c9c7_at_dangermouse>...
> > I have created a table called SYS_DATATYPE under one user,
> > and provided a public synonym so that everyone else can
> > access it.
> >
> > I can describe, select, insert and delete from other users.
> >
> > However, when I try to compile a PL/SQL package with the a
> > variable defined as:
> >
> > myVariable in out SYS_DATATYPE.COBOL_STATUS%type
> >
> > Oracle tells me that SYS_DATATYPE is not defined.
> >
> > Chapter 6 of the PL/SQL User's Guide and Reference seems to
> > suggest this should work.
> >
> > Any suggestions?
> >
> > Thanks in advance
> >
> > Graham Thornton
> >
> > ------------------------
> >
> > remove the .nospam from the E-mail address to reply.
> >
Received on Thu Sep 25 1997 - 00:00:00 CDT