SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET ECHO ON
CREATE OR REPLACE PROCEDURE grants (
    Pgrantee IN dba_users.username%TYPE,
    Pgrants  IN VARCHAR2 DEFAULT 'SELECT,INSERT,UPDATE,DELETE',
    Pobject  IN dba_objects.object_name%TYPE DEFAULT NULL,
    Ptype    IN dba_objects.object_type%TYPE DEFAULT 'TABLE') IS
/*
   Program Name: Grant Object Privileges (for this schema)
   Module Name : grants.sql
   Written By  : Daniel J. Clamage
   Description : This module grants the specified object privileges
                 on one or all objects in the calling user's schema
                 of a given type to the given user (grantee).
                 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 grantee 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 grant privileges on.
                 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 28-FEB-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 grantee.
*/

  -- 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_GRANTABLE EXCEPTION;
  INVALID_USER  EXCEPTION;
  INVALID_OBJ   EXCEPTION;

  -- variables
  get_objects_rec get_objects%ROWTYPE;
  local_grantee   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);
  status NUMERIC;
  grant_seq NUMERIC := 0;
  error_seq NUMERIC := 0;
BEGIN
  local_type  := UPPER(Ptype);
  local_obj   := UPPER(Pobject);
  local_grantee := UPPER(Pgrantee);

  -- validate grantee (could be user or role)
  IF (local_grantee != 'PUBLIC') THEN
    BEGIN
      SELECT username
      INTO local_grantee
      FROM dba_users
      WHERE username = local_grantee
      UNION
      SELECT role
      FROM dba_roles
      WHERE role = local_grantee;
    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_GRANTABLE;
  END IF;  -- test object type for grant 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
      -- GRANT syntax (and privilege options) depends on object type
      IF (local_type IN ('TABLE', 'VIEW')) THEN  -- grant priv passed in
        dyn_stmt := 'GRANT ' || Pgrants || ' ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' TO ' || local_grantee;
      ELSIF (local_type = 'SEQUENCE') THEN  -- only select
        dyn_stmt := 'GRANT SELECT ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' TO ' || local_grantee;
      ELSIF (local_type IN  -- only grant EXECUTE
               ('PROCEDURE', 'FUNCTION', 'PACKAGE')) THEN
        dyn_stmt := 'GRANT EXECUTE ON ' ||
                     USER || '.' || get_objects_rec.object_name ||
                    ' TO ' || local_grantee;
      END IF;  -- test object type for grant syntax
      DBMS_OUTPUT.put_line(dyn_stmt);
      DBMS_SYS_SQL.parse_as_user(dyn_c, dyn_stmt, DBMS_SQL.NATIVE);
      grant_seq := grant_seq + 1;
    EXCEPTION
    WHEN OTHERS THEN
      status := SQLCODE;
      DBMS_OUTPUT.put_line('>>> Grant 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 Grants: ' || TO_CHAR(grant_seq));
  DBMS_OUTPUT.put_line('Number of Errors: ' || TO_CHAR(error_seq));

EXCEPTION
WHEN NOT_GRANTABLE THEN
  DBMS_OUTPUT.put_line(local_type || ' is NOT a GRANTABLE object type!');
WHEN INVALID_USER THEN
  DBMS_OUTPUT.put_line('User '   || Pgrantee || ' NOT Found!');
WHEN INVALID_OBJ THEN
  DBMS_OUTPUT.put_line('Object ' || Pobject  || ' NOT Found!');
WHEN OTHERS THEN
  BEGIN
    status := SQLCODE;
    DBMS_OUTPUT.put_line('Grants: ' || 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 grants;
/

BEGIN
  showerr('grants');
END;
/

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