Re: Opinions Wanted: Permision Structure

Hi,

>I am creating my first Oracle App (that others are going to use) . I
created
>a user with DBA privies to create all of the tables, constraints,
sequences,
>etc. Now I am creating the forms using Dev2000.

We have an 'owner' user who owns all the objects in the database (except for temporary tables which the users create themselves in their own schemas). This user does not need DBA privs, so it means that it's safer. This user does, however, have privs such as 'create table', 'create view', 'create public synonym'. However, if you are the DBA as well as the develpoer, this is a red herring!

> From anybody's real world
>experience what is the best method to Allow others to log in with their
>account (not the account that owns the tables) access the tables, triggers
>etc with full permissions through the forms.
>
>Specifically, should I:
>
>1)create public synonyms for all objects and use those names in my code
>throughout the app. (query the data dictionary to find all objects owned by
>the creation user and create the synonyms programmatically)

Yes. This is what we do and it works very well. The public synonyms are named exactly for the table/view/sequence/package etc on which they are based.
(e.g. create public synonym FRED for table OWNERUSER.FRED) The advantage of this is that if we ever wish or need to create the objects in
a different schema we only have to change the Public Synonyms, and not all the references throughout the code. Our coding standards actually ban developers from referring to the schemas.

This method can also assist in a multi-developer environment, as it lets the developers create objects in their own schemas with an identical name to the object held in the official area. e.g. user GINGER creates a table called FRED in schema GINGER. This is different in structure from the official version of table FRED. The user can develop using this table - database object access precedences will ensure that this DB will look in the user's own schema for a table named FRED and only if it doesn't find one will it look for a public synonym of FRED. When the user is happy with the revised table FRED, it can be deleted from the GINGER schema and recreated in the OWNERUSER schema. Throughout the whole time, no code needs to be changed simply in order to refer to the temporary object used for development
purposes. If, on the other hand your code contained references directly to OWNERUSER.FRED, you would only ever be able to develop objects in the OWNERUSER schema, and in a multi-user development environment with concurrent development this becomes a nightmare, and you may actually need a separate database for each developer, with all the configuration management nightmares that this can involve.

(NB. if developing Forms and Reports, if you set up your registry settings properly you can take a similar approach to precedence calling for your Forms & Report - if you want to know more, ask me).

This seems to be a fairly common way of doing things: I have 3 major applications with about 1000 tables and 1500 Forms/Reports/Menus/LIbraries/Packages/Procedures/Triggers between them based on this system oif doing things. We have about 30 developers. This solution gives great flexibility and makes coding very easy. We use role grants and permissions to control access to objects.

>
>2)use the actual names of the tables and other objects and code with the
>schema.tablename context (as opposed to tablename).

I see no advantage to doing things this way.

>3)or is there a better organized way to approach this?

I can't think of any other major options. You could try using private synonyms, but we very rarely need to use these, and they tend to overcomplicate things except in specific instances where they may be of particular benefit for various reasons.
>
>A reply would be most helpful, I understand how grants, and synonyms work
>but am not sure what the standard or best practice way to address this is.
I
>would like to start out on the right foot with this application.

I hope this helps.

Simon Hedges
Gloucester
UK Received on Tue Mar 09 1999 - 22:10:15 CET