[ОТВЕТИТЬ]
Опции темы
12.10.2008 19:33  
OlegON
Код:
This trigger verifies whether a user logging into the database is (pre)authorized to connect to the database using the approved client machine, client software, etc.
-- connect as sys/*****

CREATE TABLE authorize_users_tab(
username VARCHAR2(30),
osuser VARCHAR2(30),
program VARCHAR2(64),
machine VARCHAR2(64)
);

ALTER TABLE authorize_users_tab
ADD CONSTRAINT user_prog_mach_uk
UNIQUE (username,osuser,program,machine);

-- Insert appropiate values into this table, so only the entered 
-- users will be permitted to connect to that database, query 
-- V$SESSION for correct info, data entered into the table are 
-- case sensitive.

insert into authorize_users_tab values ('SCOTT','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE');
insert into authorize_users_tab values ('TESTUSER','DOMAIN\OSUSER','SQLPLUSW.EXE','DOMAIN\MACHINE');
commit;

CREATE OR REPLACE TRIGGER authorize_users_trig
AFTER LOGON ON DATABASE

-- Description: This trigger helps database administrators to
-- control users logging into the database using various client
-- tools and machines. List of USERNAME, OSUSER, PROGRAM and
-- MACHINE entered in table AUTHORIZE_USERS_TAB will only be
-- permitted to login into the database.
-- Ref Oracle Bug No.2628258 and Doc No.2628258.8 in Metalink for
-- using RTRIM(machine,CHR(0)) in the below code.
-- Caveat: When a user tries to connect via SQL*Plus and if the
-- trigger refuses connection, info about that session is shown
-- as INACTIVE in V$SESSION, unless that user closes the SQL*Plus
-- window/session.

DECLARE
p_count NUMBER(1);
BEGIN
SELECT COUNT(*) INTO p_count
FROM sys.authorize_users_tab
WHERE UPPER(username||osuser||program||RTRIM(machine,CHR(0)))
IN (
SELECT UPPER(username||osuser||program||RTRIM(machine,CHR(0)))
FROM v$session
! WHERE au dsid = USERENV('sessionid')
AND logon_time = (
SELECT MAX(logon_time)
FROM v$session
WHERE audsid = USERENV('sessionid')));
IF p_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '---> Sorry, Access Denied <---');
END IF;
END ;
/
вот, для памяти, отсюда:
 
 
Опции темы



Часовой пояс GMT +3, время: 21:37.

Все в прочитанное - Календарь - RSS - - Карта - Вверх 👫 Яндекс.Метрика
Форум сделан на основе vBulletin®
Copyright ©2000 - 2016, Jelsoft Enterprises Ltd. Перевод: zCarot и OlegON
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.