Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Schema logon trigger for external user

Re: Schema logon trigger for external user

From: Lewis C <lewisc_at_excite.com>
Date: Sun, 10 Apr 2005 13:10:27 GMT
Message-ID: <788i51tlhejntoml0mi7knivmjf8m4l69r@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


Received on Sun Apr 10 2005 - 08:10:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US