Database Administration (MOSC)

MOSC Banner

Created a logon trigger,but can't prevent a user from logining to the database

edited Dec 17, 2014 9:27AM in Database Administration (MOSC) 12 commentsAnswered ✓

1.logined to the database with sysdba and created following trigger:

SQL> conn / as sysdba

Connected.

SQL> create or replace trigger limit_logon

AFTER LOGON on database

declare

dbrole varchar2(16);

loginuser varchar2(30);

begin

loginuser:=sys_context('userenv','current_user');

select database_role into dbrole from v$database;

if dbrole='PRIMARY' and loginuser='A' then

raise_application_error(-20999, 'Cann''t logon this database.');

end if;

end;

/  2    3    4    5    6    7    8    9   10   11   12   13 

Trigger created.

2.create a user named as a and do login test

SQL> create user a identified by oracle ;

User created.

SQL> grant connect,resource to a;

Grant succeeded.

SQL> conn a/oracle;

Connected

It showed that the trigger can't prevent user a from logining.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center