Re: Rights and Packages

From: Jonathan W. Ingram <jingram_at_teleport.com>
Date: 1997/01/27
Message-ID: <5cjepq$on4$1_at_nadine.teleport.com>#1/1


On Thu, 23 Jan 1997 09:06:57 -0600, Rodger <rodger_at_infobahn.mb.ca> wrote:

>We are using Uniface Six, (Windows) on an Oracle platform.
>Uniface Six makes extensive use of Oracle's
>packages when it creates the scripts to
>create the tables.
 

>We are currently trying to sort out a
>security issue for our applications.
>We figured that we would use Oracle's
>built-in security, of GRANTing
>Select, Update, etc. to the appropriate
>tables. A number of users would have
>select only rights. Easy.
 

>Uniface, however, looks for the packages (associated
>with the data tables) containing the code for
>the I/O. If the packages are not found, then the
>Uniface code will stop / crash.
 

>If we grant execute rights to the packages,
>Oracle seems to also grant all rights to the
>user that the package would have. So, if a
>package is supposed to update, the user
>suddently gets update rights. So, the package
>rights override any CRUD rights.
 

>Does anyone know of anyway to reverse this
>priority?
There are two distinct methods of doing what you want to do that I can think of, each of which has several different methods of implementation.

The first method is to mask all public procedures and functions inside a package with a routine that accepts the username as a parameter and checks the privileges of that username in the sys.dba_tab_privs, sys.dba_sys_privs, sys.dba_roles, and sys.dba_role_privs tables. This requires a lot of code which masks procedures and functions and calls them only after verifying the user's rights, but will accomplish the desired effect.

If you go this route, the procedures and functions that are currently public should be made private and the masking procedures and functions should be made public.

Another variation of this approach would be to have each individual stored procedure and function accept the username as a parameter and check the rights (this would become a standard function call, called once for each type of privilege on each database object that the procedure or function called; any failed result of the function would abort the user's stored object call).

Remember, you MUST pass the user name as a parameter; you cannot use the username variable inside the package to query the user's name (inside the package, this variable is the username under which the package was created).

The second method is to create duplicate packages (which do nothing) inside each user's schema, but this requires granting the user's the create any object privilege. This could be done temporarily and revoked after the objects were created, but I suspect that this is too much hassle to do on a regular basis.

I'm not sure that approach number two will work for you; I've never used Uniface before. Option number one might work though.

I regret that both approaches require a LOT of work to implement, but that's typical when trying to get around built-in functionalities of the database.

>That is, we could grant Select only
>to the user, and Execute to the package. However,
>the package would be disabled if the user did
>not have all the rights to do what the package
>is defined as doing. Or, the Package rights
>would be overridden by any of the other
>rights.
I am anxious to hear your progress on this problem. Please give me a status update on your efforts.

Jonathan Ingram
Meridian Technology Group
503.639.0816 Received on Mon Jan 27 1997 - 00:00:00 CET

Original text of this message