Re: FGAC, other user attributes.

Galen Boyer wrote:
>
> On 3 Dec 2002, tkyte_at_oracle.com wrote:
> > In article <u4r9whtwh.fsf_at_standardandpoors.com>, Galen says...
> >
> >>
> >>On Tue, 03 Dec 2002, gooiditweg_at_sybrandb.demon.nl wrote:
> >>> On 2 Dec 2002 16:32:19 -0600, Galen Boyer
> >>> <galenboyer_at_hotpop.com> wrote:
> >>>
> >>>> Do I create a user auxiliary
> >>>>table with a foriegn key to sys.user$
> >>>
> >>>
> >>> Definitely no foreign key to user$, as Oracle doesn't
> >>> guarantee the names of the physical tables. You should
> >>> preferably stay away of them like hell.
> >>
> >>Yes. I played around with this while guessing that this was
> >>not the correct idea (hence my post), but then, how do I
> >>maintain the link between the user who has logged in and his
> >>auxiliary data?
> >>
> >>--
> >>Galen Boyer
> >
> >
> >
> > You would just have a table with their username in it and any
> > auxillary data.
>
> Thanks alot for answering Tom.
>
> Two things that confuse me still are (they are really the same
> flavor of question asked different ways),
>
> 1) How do I maintain the link between the user in the auxiliary
> table and the user in the user$ table. The user$ table has a
> user#. Does the user_auxiliary table have its own PK? Where
> is the FK to the user$? It looks like you are saying to just
> use username as the PK on the auxiliary table and not maintain
> any database RI, except through triggers? I think I got that,
> but on to question 2.
>
> 2) In applications, users can have the same names but their
> entered auxiliary information defines their uniqueness (like
> email address/s). Using Oracle's user tables, means all the
> user names must be unique. How do you end up handling this
> name collision when new users are created?
>
> My quess might be on creation attempt, to query the auxiliary
> table for uniqueness of identification of the user, then tack
> on a sequence id for the actual username created? Of, said
> another way, create the row in the auxiliary table and then
> create the user?
>
> [...]
>
> I have already tried your code out and am putting my version into
> source control. A version of this will be used, depending on
> where our auxiliary information is stored.
>
> Thanks a bunch!
>
> Absolutely love your book.
> --
> Galen Boyer

Some ideas...

1. when you insert a user to your table, you submit a dbms_job to create the oracle account. If they rollback the insert, or it goes wrong, then no ddl will result. If the DDL fails (should be very rare), you can trap that with a ddl trigger.
2. You can implement the ref int to dba_users with a trigger - you could code the appropriate locking to enfore serilisation but one would imagine the concurrency/frequency of adding users would be low

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."