Re: FGAC, other user attributes.

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Dec 2002 07:49:35 -0800
Message-ID: <asijqf0g3u@drn.newsguy.com>

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.

If they drop the user - you can "clean" your table on a recurring basis if need be. Or you can use a database "drop" trigger:

create or replace trigger drop_trigger AFTER drop on database when (ora_dict_obj_type = 'USER')
begin

    dbms_output.put_line( 'drop' );
    dbms_output.put_line( 'ora_sysevent );'|| ora_sysevent );
    dbms_output.put_line( 'ora_login_user );'|| ora_login_user );
    dbms_output.put_line( 'ora_instance_num );'|| ora_instance_num );
    dbms_output.put_line( 'ora_database_name );'|| ora_database_name );
    dbms_output.put_line( 'ora_dict_obj_type );'|| ora_dict_obj_type );
    dbms_output.put_line( 'ora_dict_obj_name);'|| ora_dict_obj_name);
    dbms_output.put_line( 'ora_dict_obj_owner);'|| ora_dict_obj_owner);

end;
/

if you create that and then:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create user a identified by a;

User created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop user a;

User dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec null; drop

ora_sysevent );DROP
ora_login_user );OPS$TKYTE
ora_instance_num );1
ora_database_name );ORA817DEV.US.ORACLE.COM
ora_dict_obj_type );USER
ora_dict_obj_name);A
ora_dict_obj_owner);

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec null;

PL/SQL procedure successfully completed.

You can see the trigger fires only for the drop user and you can use that to cascade the delete to your table after the user was dropped.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Dec 03 2002 - 09:49:35 CST