there is no such thing as a normal database user, there are users you create and you grant them some (unknown to me) set of privileges.

YOU SHOULD NEVER AND YOU WILL NEVER AGAIN USE THE SYS OR SYSTEM ACCOUNT for your application bits and pieces - never, just stop it, pretend they do not exist.


If your trigger was simply:

"after logon on schema"

then your account would need

a) create session
b) create trigger

if your trigger was

"after logon on database"

then your account would also need

c) administer database trigger


for example:

ops$tkyte%ORA10GR2> create user a identified by a;

User created.

ops$tkyte%ORA10GR2> grant create session, create trigger to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> alter session set nls_sort = 'binary';

Session altered.

a%ORA10GR2> create or replace trigger after_logon
  2  after logon on schema
  3  begin
  4          execute immediate 'alter session set nls_sort = ''binary_ci''';
  5  end;
  6  /

Trigger created.

a%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_SORT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY

a%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_SORT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY_CI

<b>that shows that

a) I can create the trigger
b) I can execute the trigger code without fail
c) the code in the trigger worked

</b>

a%ORA10GR2> drop trigger after_logon;

Trigger dropped.

a%ORA10GR2> create or replace trigger after_logon
  2  after logon on database
  3  begin
  4          execute immediate 'alter session set nls_sort = ''binary_ci''';
  5  end;
  6  /
after logon on database
               *
ERROR at line 2:
ORA-01031: insufficient privileges


<b>that shows I cannot create a database trigger - you do not actually say what you were doing so I presume this is what you were trying...</b>

a%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> grant administer database trigger to a;

Grant succeeded.

ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_SORT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY

ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create or replace trigger after_logon
  2  after logon on database
  3  begin
  4          execute immediate 'alter session set nls_sort = ''binary_ci''';
  5  end;
  6  /

Trigger created.

<b>that shows I can and....</b>

a%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select * from nls_session_parameters where parameter = 'NLS_SORT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       BINARY_CI

<b>that it works</b>

if you do this, create the A account, install the trigger, and then LOCK that account, administer database trigger is a powerful grant, you want to protect that account from access by anyone else.