/*
 * create_user.sql
 * Appendix G, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script verifies and defines the PHP user. It should be run
 * as the SYSTEM user or a user that has the DBA privilege role.
 */

-- Unremark for debugging script.
-- SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 999
SET SERVEROUTPUT ON SIZE 1000000

DECLARE

  -- Define an exception.
  wrong_schema EXCEPTION;
  PRAGMA EXCEPTION_INIT(wrong_schema,-20001);

  -- Define a return variable.
  retval VARCHAR2(1 CHAR);

  /*
  || Define a cursor to identify whether the current user is either the
  || SYSTEM user or a user with the DBA role privilege.
  */
  CURSOR privs IS
    SELECT   DISTINCT null
    FROM     user_role_privs
    WHERE    username = 'SYSTEM'
    OR       granted_role = 'DBA';

BEGIN

  -- Open cursor and read through it.
  OPEN privs;
  LOOP

    -- Read a row.
    FETCH privs INTO retval;

    -- Evaluate if cursor failed.
    IF privs%NOTFOUND THEN

      -- Raise exception.
      RAISE wrong_schema;

    ELSE

      -- Evaluate whether PLSQL user exists and drop it.
      FOR i IN (SELECT null FROM dba_users WHERE username = 'PHP') LOOP
        EXECUTE IMMEDIATE 'DROP USER php CASCADE';
      END LOOP;

      -- Create user and grant privileges.
      EXECUTE IMMEDIATE 'CREATE USER php IDENTIFIED BY php';
      EXECUTE IMMEDIATE 'GRANT connect TO php';
      EXECUTE IMMEDIATE 'GRANT resource TO php';

      -- Print successful outcome.
      DBMS_OUTPUT.PUT_LINE(CHR(10)||'Created PHP user.');

    END IF; 

    -- Exit the loop.
    EXIT;

  END LOOP;

  -- Close cursor.
  CLOSE privs;

EXCEPTION

  -- Handle a defined exception.
  WHEN wrong_schema THEN
    DBMS_OUTPUT.PUT_LINE('The script requires the SYSTEM user and '
    ||                   'you are using the <'||user||'> schema or '
    ||                   'the script requires a user with DBA role '
    ||                   'privileges.');

  -- Handle a generic exception.
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RETURN;

END;
/

-- Define SQL*Plus formatting.
COL grantee          FORMAT A8
COL granted_role     FORMAT A30
COL grantor          FORMAT A12
COL privilege        FORMAT A12
COL owner            FORMAT A6
COL table_name       FORMAT A10

-- Query user granted roles.
SELECT   grantee
,        granted_role
FROM     dba_role_privs
WHERE    grantee = 'PHP';

-- Query resources.
SELECT   grantor
,        owner
,        table_name
,        grantee
,        privilege
FROM     dba_tab_privs
WHERE    grantee = 'PHP';

COL admin_option     FORMAT A3
COL privilege        FORMAT A30
COL username         FORMAT A10

-- Query user system privileges.
SELECT   grantee
,        privilege
,        admin_option
FROM     dba_sys_privs
WHERE    grantee = 'PHP';
