plgpriv.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGpriv
IS

/* PL/Generator Privilege Administration */

/*----------------------------------------------------------------
||                  PL/Generator from RevealNet 
||----------------------------------------------------------------
||    File: plgpriv.spb
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1998-1999 RevealNet, Inc.
|| All rights reserved.
||
|| For more information, call RevealNet at 1-800-REVEAL4
|| or check out our Web page: www.revealnet.com
-----------------------------------------------------------------*/

/***************** Modification History **************************
|| Date     By     Description
|| -------- ------ -----------------------------------------------
|| 05/2001  SEF    Change "s:plgoir" to "s:plgdoir"
|| 04/98    SEF    Created in Kangaroo Island
******************************************************************/
   g_chkpriv BOOLEAN := FALSE;

   c_global CONSTANT CHAR(4) := PLGdoir.c_global;

   CURSOR priv_cur (
      sch IN VARCHAR2, 
      tab IN VARCHAR2, 
      privtype IN VARCHAR2,
      username IN VARCHAR2
      )
   IS
      SELECT 'x'
        FROM plg_doir
       WHERE driver = PLGdoir.c_global
         AND 
         ((owner = UPPER (NVL (sch, USER)) AND objname = UPPER (tab)) OR
          (owner = c_global AND objname = UPPER (tab)) OR
          (owner = c_global AND objname = c_global) OR
          (owner = UPPER (NVL (sch, USER)) AND objname = c_global) 
          )
         AND objtype = c_plgpriv
         AND attrname = UPPER (username)
         AND attrtype = c_global
         AND infotype = c_global
         AND infoseq = 1
         AND (info = UPPER (privtype) OR info = allprivs)
       ORDER BY 
          DECODE (owner, 
                  '*ALL', DECODE (objname, '*ALL', 99, 98),
                  DECODE (objname, '*ALL', 97, 96));

   PROCEDURE add (
      username IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      assert (tab, sch, modpriv);

      PLGdoir.ins (drv => PLGdoir.c_global,
         sch => sch, 
         objname_in => tab, 
         objtype_in => c_plgpriv, 
         attrname_in => UPPER (username), 
         attrtype_in => c_global, 
         infotype_in => c_global, 
         seq_in => 1,
         info_in => UPPER (privtype));
   END;

   PROCEDURE remove (
      username IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      assert (tab, sch, modpriv);

      DELETE
        FROM plg_doir
       WHERE driver = PLGdoir.c_global
         AND owner = NVL (UPPER (sch), USER)
         AND objtype = c_plgpriv
         AND objname = UPPER (tab)
         AND attrname = UPPER (username)
         AND attrtype = c_global
         AND infotype = c_global
         AND infoseq = 1
         AND info = UPPER (privtype);
   END;

   PROCEDURE add (
      username IN VARCHAR2,
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      add (username, c_global, c_global, privtype);
   END;

   PROCEDURE remove (
      username IN VARCHAR2,
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      remove (username, c_global, c_global, privtype);
   END;

   FUNCTION granted (
      username IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      privtype IN VARCHAR2
      )
      RETURN BOOLEAN
   IS
      v_definer PLGadmin.identifier := PLGobj.definer ('S:PLGdoir');
      priv_rec priv_cur%ROWTYPE;
      retval BOOLEAN;
   BEGIN
      /* OK 
            If you are the owner of the PLGdoir package
            or
            If you own the object and the privilege is modobj.
      */
      retval := 
         v_definer = USER
         OR
         (UPPER (username) = UPPER (sch) AND privtype = modobj);

      IF NOT retval
      THEN
         OPEN priv_cur (sch, tab, privtype, username);
         FETCH priv_cur INTO priv_rec;
         retval := priv_cur%FOUND;
         CLOSE priv_cur;
      END IF;

      RETURN retval;
   END;

   FUNCTION grantedN (
      username IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      privtype IN VARCHAR2
      )
      RETURN INTEGER
   IS
   BEGIN
      RETURN PLGdoir.boolint (granted (username, tab, sch, privtype));
   END;

   FUNCTION granted (
      username IN VARCHAR2,
      privtype IN VARCHAR2
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN granted (username, c_global, c_global, privtype);
   END;

   FUNCTION grantedN (
      username IN VARCHAR2,
      privtype IN VARCHAR2
      )
      RETURN INTEGER
   IS
   BEGIN
      RETURN PLGdoir.boolint 
         (granted (username, c_global, c_global, privtype));
   END;

   /* Validate that the required privilege is present. */
   PROCEDURE assert (
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      IF checking
      THEN
         PLGerr.assert (
            granted (USER, tab, NVL (UPPER (sch), USER), privtype),
            'PLGpriv: ' || USER || 
            ' has insufficient privileges for operation. Requires: ' ||
            privtype
            );
      END IF;
   END;

   PROCEDURE assert (
      privtype IN VARCHAR2
      )
   IS
   BEGIN
      assert (c_global, c_global, privtype);
   END;

/* SUPERSEDED BY PRIV model PL/Generator Administrator Programs 

   PROCEDURE setplgadmin (sch IN VARCHAR2)
   IS
   BEGIN
      assert (tab, sch, modpriv);

      ins (sch => c_global,
         objname_in => c_global, 
         objtype_in => c_plgadmin, 
         attrname_in => c_global, 
         attrtype_in => c_global, 
         infotype_in => UPPER (sch), 
         seq_in => 1, 
         info_in => UPPER (sch), 
         doc_in => 'PL/Generator Administrator');
   END;

   FUNCTION isplgadmin (sch IN VARCHAR2) RETURN BOOLEAN
   IS
      v_definer PLGadmin.identifier := PLGobj.definer ('S:PLGdoir');
      retval BOOLEAN;
   BEGIN
      retval := v_definer = USER;
      IF NOT retval
      THEN
         retval := globalinfo (
            objtype_in => c_plgadmin,
            infotype_in => UPPER (sch),
            sch => c_global)
            IS NOT NULL;
      END IF;
      RETURN retval;
   END;

   FUNCTION isplgadminN (sch IN VARCHAR2) RETURN INTEGER
   IS
   BEGIN
      RETURN boolint (isplgadmin (sch));
   END;
*/

   PROCEDURE turn_on IS 
   BEGIN 
      assert (c_global, c_global, PLGpriv.modpriv);
      g_chkpriv := TRUE; 
   END;

   PROCEDURE turn_off IS 
   BEGIN 
      assert (c_global, c_global, PLGpriv.modpriv);
      g_chkpriv := FALSE; 
   END;

   FUNCTION checking RETURN BOOLEAN 
   IS BEGIN 
      RETURN g_chkpriv; 
   END;

END PLGpriv;                      
/