Re: FGAC, other user attributes.

On Tue, 03 Dec 2002, connor_mcdonald_at_yahoo.com wrote:

> 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.

Yes. That sounds like the way to go.

> 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

From your dbms_job idea, the users_auxiliary table is entered first. Then, this users_auxiliary table could have a name field for the actual user's name, as well as other auxiliary information. I then define a unique username, maybe just a sequence number?

    create user a1234567 identified by hello;     grant application_user to a1234567;
    alter user a1234567 grant connect through application_server

           ROLE application_user;

for the actual Oracle user. I store the name, 'a1234567' in another column of the auxiliary table and have the dbms_job execute the above code to create the user. I maintain the RI between the two tables through the triggering mechanism.

Does this sound like the way to proceed?

-- 
Galen Boyer