Serge Rielau wrote:
> Fernando Nunes wrote:
>> My fellow Oracle DBA tells me something about PL/SQL need to have
>> explicitly grants (user/table) while simple SQL doesn't... I know I'm
>> not making much sense, but in his words: "It's a different engine...
>> SQL and PL/SQL"
>> If I recall correctly if you create a procedure with a user that has
>> the necessarily privileges over the tables, a third user cannot
>> execute the procedure if he doesn't have the underlying table
>> privileges... Does it ring a bell? I'll try to clarify this with him...

> Fernando I think you are confusing two issues here.
> In Oracle dynamic SQL run by a user can use role-membership to execute.

Correct.

> Any DDL objects (like e.g. views) cannot rely on a permission based on a
> role. The definer requires explicit permission to the used objects.

Correct.

> Given that roles are managed in the database this is indeed interesting.
>
> The fact that routines run under "definer"s rights by default is part of
> encapsulation. One common usage of routines is to avoid having to give
> users access to base objects. To the best of my knowledge this is SQL
> standard. There should (and I believe is) a means to use "invoker"s
> rights which basically turns a routine into a macro.

Though any object can also be created/replaced with CURRENT_USER rights.

Part of the reason for this is that objects, with the same name, may
exist in both the user's and the definer's schemas. This makes it easy
to determine which of these objects is used by the code.

> Anyway there are countless differences between Oracle SQL and PL/SQL in
> packages (and they drive me nuts on a daily basis).

Or would if you had Oracle installed eh. <g>

> e.g: routine overloading and defaulting is only supported within packages.

And user defined types and operators.

> VARCHAR2 has different limits inside and outside of PL/SQL (32k vs. 4000),
> You can declare subtypes (like distinct types) in PL/SQL, but not outside.
> In fact there are many objects that can only be used within a package
> and/or routine.
> Since PL/SQL typically contain SQL (such as expressions) inside of it
> it's pretty confusing what can and cannot be done at any given time.

Unless someone works with it regularly in which case it is mindlessly
simple.

> One of the things I have learned over the years is to treat PL/SQL and
> SQL completely separate.

Or would if you had Oracle installed eh. <g>

> I believe this may be one reason why Oracle users perceive the lack of
> package support in other products as such a big minus. It concentrates a
> lot of the capabilities.
>
> Cheers
> Serge

Partially correct. The package initialization section is important,
global persistent variable and type definitions are another. There
are many reasons why packages are a huge improvement over stand-alone
procedures/functions. Among them ease of maintenance and versioning:
One object vs. many.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)