SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET ECHO ON
CREATE OR REPLACE PROCEDURE revokes (
    Prevokee IN dba_users.username%TYPE,
    Pprivs   IN VARCHAR2 DEFAULT 'ALL',
    Pobject  IN dba_objects.object_name%TYPE DEFAULT NULL,
    Ptype    IN dba_objects.object_type%TYPE DEFAULT 'TABLE') IS
/*
   Program Name: Revoke object privileges (for this schema)
   Module Name : revokes.sql
   Written By  : Daniel J. Clamage
   Description : This module revokes the specified object privileges
                 on one or all objects in the calling user's schema
                 of a given type from the given user (revokee).
                 The specified privilege string is ignored for
                 certain object types for which only specific
                 privileges are valid anyway (and these are used
                 instead).
                 If the revokee specified is not a valid user or role,
                 an exception is raised and an appropriate message
                 issued. Only tables, views, sequences, stored
                 procedures, functions and packages are valid object
                 types to revoke privileges from.
                 If an object is specified, its existence is
                 validated and its type retrieved. If no object
                 is specified, all objects of the given type are
                 processed.
                 NOTE: Must be compiled under user with access to
                 dba_* views!
   Modification:
     V.001 01-SEP-1998 - djc - Initial release.
     V.002 05-NOV-1998 - djc - Fixed TOO_MANY_ROWS when package is
                       specified by name.
     V.003 26-JAN-1999 - djc/sy - Enable any user to run for own
                       objects. Support role, PUBLIC as revokee.
*/

  -- cursors
  CURSOR get_objects(Cobject dba_objects.object_name%TYPE,
                     Ctype   dba_objects.object_type%TYPE) IS
    SELECT object_name
    FROM dba_objects
    WHERE
      owner = USER AND  -- can only operate on own objects
      (Cobject IS NULL OR object_name = Cobject) AND
      object_type = Ctype;

  -- user-defined exceptions
  NOT_REVOKABLE EXCEPTION;
  INVALID_USER  EXCEPTION;
  INVALID_OBJ   EXCEPTION;

  -- variables
  get_objects_rec get_objects%ROWTYPE;
  local_revokee   dba_users.username%TYPE;
  local_type      dba_objects.object_type%TYPE;
  local_obj       dba_objects.object_name%TYPE;
  dyn_c INTEGER;
  dyn_stmt VARCHAR2(32767);
  dummy INTEGER;
  status NUMERIC;
  revoke_seq NUMERIC := 0;
  error_seq NUMERIC := 0;
BEGIN
  local_type  := UPPER(Ptype);
  local_obj   := UPPER(Pobject);
  local_revokee := UPPER(Prevokee);

  -- validate revokee (could be user or role)
  IF (local_revokee != 'PUBLIC') THEN
    BEGIN
      SELECT username
      INTO local_revokee
      FROM dba_users
      WHERE username = local_revokee
      UNION
      SELECT role
      FROM dba_roles
      WHERE role = local_revokee;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE INVALID_USER;
    END;
  END IF;  -- check for valid user or role

  -- validate object name
  IF (local_obj IS NOT NULL) THEN  -- get object's type
    BEGIN
      SELECT object_type
      INTO local_type
      FROM dba_objects
      WHERE
        owner = USER AND  -- has to be own object
        object_name = local_obj AND
        object_type IN ('TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE',
                        'FUNCTION', 'PACKAGE');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE INVALID_OBJ;
    END;
  END IF;

  -- validate object type
  IF (local_type NOT IN ('TABLE', 'VIEW', 'SEQUENCE',
                         'PROCEDURE', 'FUNCTION', 'PACKAGE')) THEN
    RAISE NOT_REVOKABLE;
  END IF;  -- test object type for revoke syntax

  dyn_c := DBMS_SQL.open_cursor;
  OPEN get_objects(local_obj, local_type);
  LOOP
    FETCH get_objects INTO get_objects_rec;
    EXIT WHEN get_objects%NOTFOUND;
    BEGIN
      -- REVOKE syntax (and privilege options) depends on object type
      IF (local_type IN ('TABLE', 'VIEW')) THEN  -- revoke priv passed in
        dyn_stmt := 'REVOKE ' || Pprivs || ' ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' FROM ' || local_revokee;
      ELSIF (local_type = 'SEQUENCE') THEN  -- only select
        dyn_stmt := 'REVOKE SELECT ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' FROM ' || local_revokee;
      ELSIF (local_type IN  -- only revoke EXECUTE
               ('PROCEDURE', 'FUNCTION', 'PACKAGE')) THEN
        dyn_stmt := 'REVOKE EXECUTE ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' FROM ' || local_revokee;
      END IF;  -- test object type for revoke syntax
      DBMS_OUTPUT.put_line(dyn_stmt);
      DBMS_SYS_SQL.parse_as_user(dyn_c, dyn_stmt, DBMS_SQL.NATIVE);
      revoke_seq := revoke_seq + 1;
    EXCEPTION
    WHEN OTHERS THEN
      status := SQLCODE;
      DBMS_OUTPUT.put_line('>>> Revoke failed: ' || SQLERRM(status));
      error_seq := error_seq + 1;
    END;
  END LOOP;  -- process objects
  CLOSE get_objects;
  DBMS_SQL.close_cursor(dyn_c);
  DBMS_OUTPUT.put_line('Number of Revokes: ' || TO_CHAR(revoke_seq));
  DBMS_OUTPUT.put_line('Number of Errors: ' || TO_CHAR(error_seq));

EXCEPTION
WHEN NOT_REVOKABLE THEN
  DBMS_OUTPUT.put_line(local_type || ' is NOT a REVOKABLE object type!');
WHEN INVALID_USER THEN
  DBMS_OUTPUT.put_line('User '   || Prevokee || ' NOT Found!');
WHEN INVALID_OBJ THEN
  DBMS_OUTPUT.put_line('Object ' || Pobject  || ' NOT Found!');
WHEN OTHERS THEN
  BEGIN
    status := SQLCODE;
    DBMS_OUTPUT.put_line('Revokes: ' || SQLERRM(status));
    IF (get_objects%ISOPEN) THEN
      CLOSE get_objects;
    END IF;
    IF (DBMS_SQL.is_open(dyn_c)) THEN
      DBMS_SQL.close_cursor(dyn_c);
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    NULL;  -- don't care
  END;
END revokes;
/

BEGIN
  showerr('revokes');
END;
/

DROP PUBLIC SYNONYM REVOKES;
CREATE PUBLIC SYNONYM REVOKES FOR REVOKES;
GRANT EXECUTE ON REVOKES TO PUBLIC;