Re: Stop users from connecting to database

From: Burt Peltier <burttemp1REMOVE_THIS_at_bellsouth.net>
Date: Thu, 22 May 2003 18:38:14 -0500
Message-ID: <v3dza.5505$Jk1.4818@fe09.atl2.webusenet.com>

The code below worked in our 8.1.7.4 test database on Solaris 8. But, of course, you would need to test in your environment and probably put something like Oracle accounts (batch jobs) in the table and then only allow those accounts in the table and then maybe only if the sysdate time is during the window of time you are concerned about. But, with more checks, the only other thing is although I don't see any performance degradation for connects, it makes me wonder if this would be a problem in a production database with many connects happening at the same time. Also, I noticed it puts entries in the alert.log for connect failures. This could present a problem for anyone who doesn't monitor the alert.log with a script (and reduce the size when it gets too big). Otherwise, an alert.log could grow so big , it might fill up your disk ?

Note: I copied this from an IOUG 2003 paper by Joseph Trezzo (TUSC) (Title=NEW DATABASE TRIGGERS: AN EXPANDED ARCHITECTURE WITH EXTREME VALUE), and modified it some (mainly the comments and checking for cursor%FOUND). Joe has given me permission to post this. Thanks Joe.

.
.
.

CREATE OR REPLACE TRIGGER logon_allow_trigger AFTER LOGON ON DATABASE
--

• Purpose: Dynamically control whether logons are allowed or disallowed.
  --
  
• The SYSTEM and SYS accounts are automatically exempt from this trigger.
  --
  
• A record is inserted into the table below with the value of YES.
• This logon trigger is created to reference this table
• upon logon and look for the value of the record to determine
• if logins are allowed.
  --
  
• Must first create table:
• CREATE TABLE logons_allowed
• (logons_allowed VARCHAR2(3) NOT NULL);
  --
  DECLARE CURSOR logon_allowed IS SELECT logons_allowed FROM logons_allowed; lv_allowed logons_allowed.logons_allowed%TYPE; BEGIN OPEN logon_allowed; FETCH logon_allowed INTO lv_allowed; IF lv_allowed != 'YES' and logon_allowed%FOUND THEN CLOSE logon_allowed; RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Logins Not Currently Allowed.'); ELSE CLOSE logon_allowed; END IF; END; /

--

"Burt Peltier" <burttemp1REMOVE_THIS_at_bellsouth.net> wrote in message news:u2Dya.26690$jH2.19355_at_fe09.atl2.webusenet.com...

> Lots of ways to do everything ...
>
> It would be nice if you could just easily run the batch jobs on the server
> and disable/stop the listener.  This is clean and simple, but I know in

our

> environment it would not be possible.
>
> If this is not possible in your environment, then another possible

solution
> is if you are on Oracle8i or 9i, then create a database level logon trigger
> to check the account logging in and not allow them to logon unless from a > specific Oracle account . Actually, there are other things (a few in 8i and

> more in 9i) you could check to determine if the logon session is a batch
> job.
>
> -- 
>
> "Bernd Sandmann" <b.sandmann_at_gmx.net> wrote in message
> news:3eca0d95.363278468_at_127.0.0.1...
> > Hello,
> >
> > what is the best way to avoid users from connecting to a database but
> > some other users should connect anyway.
> >
> > The reason for this is that some batch jobs should run under a special
> > users while other users should not connect to the database.
> >
> > regards.
> > Bernd Sandmann
> >
>
>
>

Received on Thu May 22 2003 - 18:38:14 CDT