user10978624 wrote:you don't block the database user. you will block an OS-User account.
We have requirement where we need to restrict some user from accessing the schema.So for testing i was creating below trigger to stop user 'rajvardhan' from connecting to a schema.But even after i created this trigger user was able to login to the schema? Can you please help what i am missing here,i don't have dba privelleges on this schema
orcl> connect / as sysdba Connected. Session altered. orcl> CREATE OR REPLACE TRIGGER logon_audit_trigger1 2 AFTER LOGON 3 ON database 4 DECLARE 5 OSUSER VARCHAR2 (100); 6 v_error VARCHAR2 (2000); 7 BEGIN 8 SELECT user INTO OSUSER FROM DUAL; 9 IF SYS_CONTEXT ('USERENV', 'OS_USER') = 'jwdell\john' 10 THEN 11 raise_application_error (-101, 'ACCESS DENIED'); 12 END IF; 13 END; 14 / Trigger created. orcl> grant dba to scott; Grant succeeded. orcl> conn scott/tiger Connected. Session altered. orcl> conn / as sysdba Connected. Session altered. orcl> revoke dba from scott; Revoke succeeded. orcl> conn scott/tiger ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-21000: error number argument to raise_application_error of -101 is out of range ORA-06512: at line 8 Warning: You are no longer connected to ORACLE.