Re: client/server to oracle

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 28 Sep 1999 15:21:34 GMT
Message-ID: <19990928112134.08718.00001479@ngol04.aol.com>

The more complicated way to restrict access from adhoc query tools

Grant SELECT on V$SESSION TO <ACCOUNT BEING RESTRICTED>

Quick test table...
CREATE TABLE DEPT3_16 (
    DEPT_NO NUMBER(10),
    DEPT_NAME VARCHAR2(30)); Test script - This will return a value to match up with SID in v$session... set serveroutput on
DECLARE
    X VARCHAR2(40);
    Y NUMBER;
BEGIN
    X := DBMS_SESSION.UNIQUE_SESSION_ID;     SELECT DECODE(SUBSTR(X,1,1),'A',10,'B',11,'C',

            12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,1,1))) +
         (DECODE(SUBSTR(X,2,1),'A',10,'B',11,'C',
            12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,2,1))) * 16) +
         (DECODE(SUBSTR(X,3,1),'A',10,'B',11,'C',
            12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,3,1))) * 256) 
    INTO Y FROM DUAL;         

    DBMS_OUTPUT.PUT_LINE(X||', '||TO_CHAR(Y)); END; SP to do the deed...
CREATE OR REPLACE FUNCTION STOP_UPDATE           RETURN BOOLEAN AS
   X VARCHAR2(20);
   SSID NUMBER(5);
   CNT NUMBER(3);
BEGIN
   X := DBMS_SESSION.UNIQUE_SESSION_ID;    SELECT DECODE(SUBSTR(X,1,1),'A',10,'B',11,'C',

           12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,1,1))) +


        (DECODE(SUBSTR(X,2,1),'A',10,'B',11,'C',


           12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,2,1))) * 16) +


        (DECODE(SUBSTR(X,3,1),'A',10,'B',11,'C',


           12,'D',13,'E',14,'F',15, TO_NUMBER(SUBSTR(X,3,1))) * 256) 
   INTO SSID FROM DUAL;         
   SELECT COUNT(*) INTO CNT

   FROM V$SESSION
   WHERE
   SID = SSID AND
   PROGRAM IN ('SQL Plus 8.0','C:\Program Files\Microsoft Office\Office\Shortcut Bar\Office\SQL','another tool'); --<<<This line will vary

   IF CNT > 0 Then

        RETURN TRUE;
   ELSE
        RETURN FALSE;
   END IF;
END; Trigger to stop the sql_plus weenie...
CREATE OR REPLACE TRIGGER BI_DEPT3_16 BEFORE INSERT        ON DEPT3_16
BEGIN
    IF STOP_UPDATE THEN

      RAISE_APPLICATION_ERROR(-20001,
            'INVALID OPERATION FOR THIS TOOL');

    END IF;
END; SQL> select sid,program from v$session;

       SID PROGRAM

---------- ----------------------------------------------------------------
         1 ORACLE80.EXE
         2 ORACLE80.EXE
         3 ORACLE80.EXE
         4 ORACLE80.EXE
         5 ORACLE80.EXE
         6 ORACLE80.EXE
         7
         8
         9 C:\Program Files\Microsoft Office\Office\Shortcut Bar\Office\SQL
        11 C:\orant\agentbin\DBSNMP.EXE

Paul in VT Received on Tue Sep 28 1999 - 10:21:34 CDT