Re: client/server to oracle

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

A simpler method to restrict access from adhoc query tools

--demonstration table...

CREATE TABLE DEPT3_15 (
    DEPT_NO NUMBER(10),
    DEPT_NAME VARCHAR2(30));
--demostration user...

CREATE USER MARY IDENTIFIED BY MARY; CREATE ROLE SELECT_ROLE; CREATE ROLE UPDATE_ROLE IDENTIFIED BY SECURE; GRANT SELECT ON DEPT3_15 TO SELECT_ROLE; GRANT SELECT, INSERT, DELETE, UPDATE ON DEPT3_15 TO UPDATE_ROLE; GRANT SELECT_ROLE, UPDATE_ROLE TO MARY; ALTER USER MARY
DEFAULT ROLE ALL EXCEPT UPDATE_ROLE; Insert program code to enable the role into applications allowed to modify data.

BEGIN
   DBMS_SESSION.SET_ROLE('UPDATE_ROLE IDENTIFIED BY SECURE'); END; The downside of this all other roles are now disabled :-(

so...you do something like...

DECLARE
CURSOR C1 IS
   SELECT GRANTED_ROLE
   FROM USER_ROLE_PRIVS;
TMP VARCHAR2(100);
ISENABLED BOOLEAN;
BEGIN
   TMP := 'UPDATE_ROLE IDENTIFIED BY SECURE';    FOR I IN C1 LOOP

     ISENABLED := DBMS_SESSION.IS_ROLE_ENABLED(I.GRANTED_ROLE);
     IF ISENABLED AND I.GRANTED_ROLE != 'MARKETING' THEN
           TMP := TMP||', '||I.GRANTED_ROLE;
      END IF;

   END LOOP;
   DBMS_SESSION.SET_ROLE(TMP);
END; Paul in VT Received on Tue Sep 28 1999 - 10:21:33 CDT