DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, September 29, 2016

User access to database within limits of time

SQL> create user gangai identified by gangai;

User created.

SQL> grant connect, resource to gangai;

Grant succeeded.

SQL> conn gangai/gangai
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'gangai' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
23

SQL> conn gangai/gangai
Connected.
SQL> select to_char(sysdate,'hh24') from dual;

TO
--
18

SQL> conn gangai/gangai
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user gangai! You can't login between 08 and 22
ORA-06512: at line 5


Warning: You are no longer connected to ORACLE.
SQL>

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.