Re: Schema logon trigger for external user

"Lewis C" <lewisc_at_excite.com> wrote in message news:788i51tlhejntoml0mi7knivmjf8m4l69r_at_4ax.com...
> On Sun, 10 Apr 2005 10:03:16 GMT, "Martin Chatfield"
> <MartinChatfield_at_hotmail.com> wrote:
>
>>I have an external user called OPS$DOM1\appuser, i.e. it's a Windows user
>>called appuser in domain DOM1. appuser can do sqlplus / with no problems
>>so
>>the authentication is fine.
>>
>>I want to setup a schema logon trigger for this user, so I tried:
>>
>>CREATE OR REPLACE TRIGGER
>> AFTER LOGON ON OPS$DOM1\APPUSER.SCHEMA
>>BEGIN
>>
>>END;
>>
>>This doesn't work because '\' is an invalid character, so I tried
>>"OPS$DOM1\APPUSER.SCHEMA", "OPS$DOM1\APPUSER".SCHEMA etc. - no good. Does
>>anyone know how I can do this?
>>
>>Oracle 9.2.0.1, Windows XP.
>>
>>Thanks
>>Martin Chatfield
>>
>>
>
>
> If you select * from dba_users do you see a user named
> OPS$DOM\APPUSER? I've never seen one formatted that way. Although I
> just tried and was able to create the user, yours may be named
> differently. I misspelled the name the first time I tried to create
> the trigger and it said it couldn't do it for views or tables. I'm
> guessing spelling is your problem.
>
> This is what I did:
>
> SQL> CREATE USER "OPS$DOM1\APPUSER" IDENTIFIED EXTERNALLY
> 2 ;
>
> User created.
>
> SQL> ed
> Wrote file afiedt.buf
> 1 select username from dba_users
> 2* where username = 'OPS$DOM1\APPUSER'
> SQL> /
>
> USERNAME
> ------------------------------
> OPS$DOM1\APPUSER
>
> SQL> CREATE OR REPLACE TRIGGER tg2
> 2 AFTER LOGON ON "OPS$DOM1\APPUSER".SCHEMA
> 3 BEGIN
> 4 null;
> 5 END;
> 6 /
>
> Trigger created.
>
>
> Hope that helps,
>
> Lewis
> -----------------------------------------------------------
> Lewis R Cunningham
>
> Author, ItToolBox Blog: An Expert's Guide to Oracle
> http://blogs.ittoolbox.com/oracle/guide/
>
> Topic Editor, Suite101.com: Oracle Database
> http://www.suite101.com/welcome.cfm/oracle
>
> Sign up for courses here:
> http://www.suite101.com/suiteu/default.cfm/416752
> -----------------------------------------------------------

I applied the patch (SQLPLUS 9.2.0.4) and now it works as expected. Thank again,
Martin Received on Mon Apr 11 2005 - 12:37:19 CDT