Grant Role/grant Privilege

While Creating the following trigger at "Scott" User I've got an error as 
follows:

create or replace trigger data_logon_trigger 
after logon 
ON DATABASE 
begin 
    execute immediate 
        'alter session set nls_date_format = ''yyyymmdd'' '; 
end; 
/

ERROR at line 3:
ORA-01031: insufficient privileges

I've given the following Grants to Scott 
Grant Create Trigger to Scott
Grant Alter Any Trigger to Scott

After Above also I've got the same error Later I've given the following:
Grant DBA to Scott
Still I've got the same error, later I've given the following Grant:
Grant Administer Database Trigger to Scott
Then I can able to create the trigger.

By Default "Administer Database Trigger" is a Privelege is part of DBA role
Even If I 've granted that role to Scott, then why it is not able to created 
that trigger ...........? 
Can u please explain problem might be what..?

Thanks in advance..
Giri K.Y.

> After Above also I've got the same error Later I've given the following:
> Grant DBA to Scott
> Still I've got the same error, later I've given the following Grant:
> Grant Administer Database Trigger to Scott
> Then I can able to create the trigger.


> 
> By Default "Administer Database Trigger" is a Privelege is part of DBA role
> Even If I 've granted that role to Scott, then why it is not able to 
> created that trigger ...........? 
> Can u please explain problem might be what..?

Giri,

Stored objects such as VIEWS, TRIGGERS, PROCEDURES, PACKAGES etc. require a 
user to have a DIRECT GRANT (grant given directly to a user not via a GRANTED 
ROLE).

Bottom line you need "CREATE ANY TRIGGER", "ALTER SESSION" and "ADMINISTER 
DATABASE TRIGGER" granted directly to Scott for this to work:

13:10:53 SYSTEM@LMON:stechkin> create user scott identified by tiger;

User created.

13:14:20 SYSTEM@LMON:stechkin> grant CONNECT, RESOURCE, CREATE ANY TRIGGER, 
ALTER SESSION,
13:14:42   2  ADMINISTER DATABASE TRIGGER to scott;

Grant succeeded.

13:14:53 SYSTEM@LMON:stechkin> connect scott/tiger@LMON
Connected.

create or replace trigger data_logon_trigger
after logon
ON DATABASE
begin
    execute immediate
        'alter session set nls_date_format = ''yyyymmdd'' ';
end;
13:16:35   8  /

Trigger created.

13:16:35 SCOTT> connect system@lmon
Enter password:
Connected.


Regards,
- Vitaliy