/*----- выполнять от имени пользователя с правами DBA -----*/
DECLARE
  cUser     VARCHAR2( 30 );
  cPassword VARCHAR2( 30 );
  n         PLS_INTEGER;
BEGIN
 
 /* имя схемы (пользователя)(большими буквами) и пароль */
  cUser     := 'DBEMPTY';
  cPassword := 'nhA8PwKPAbyaJnwWW9in';
  /* удаление старого пользователя со схемой */
  cUser := Upper( cUser );
  SELECT Count(*)
    INTO n
    FROM DBA_Users
    WHERE username = cUser;
  IF( n > 0 ) THEN
    EXECUTE IMMEDIATE 'DROP USER ' || cUser || ' CASCADE';
  END IF;
 
 /* удаление связанных с ним ролей */
  FOR x IN( SELECT role FROM DBA_Roles
              WHERE role IN( cUser || '_FADMSUBADMIN', cUser || '_FADMUSER',
                             cUser || '_FADMPUBLIC',   cUser || '_KRIC' ) )
  LOOP
    EXECUTE IMMEDIATE 'DROP ROLE ' || x.role;
  END LOOP;
 
 /* удаление неверного, но связанного с пользователем контекста */
  FOR x IN( SELECT namespace FROM DBA_Context
              WHERE namespace = cUser || '_FADM' AND
                    ( schema <> cUser OR package <> 'PR_FADM_SETCONTEXT' OR type <> 'ACCESSED LOCALLY' ) )
  LOOP
    EXECUTE IMMEDIATE 'DROP CONTEXT ' || x.namespace;
  END LOOP;

  /* создание пользователя заново */
  EXECUTE IMMEDIATE 'CREATE USER ' || cUser || ' PROFILE "DEFAULT"' ||
                    '  IDENTIFIED BY "' || cPassword || '" DEFAULT TABLESPACE "USERS"' ||
                    '  TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK';
  EXECUTE IMMEDIATE 'GRANT CONNECT              TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT CREATE SESSION       TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT ALTER  SESSION       TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT CREATE USER          TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT RESOURCE             TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT EXEMPT ACCESS POLICY TO ' || cUser;
  EXECUTE IMMEDIATE 'GRANT DBA                  TO ' || cUser;

  /* создание связанных ролей без выдачи создателю (DBA) */
  EXECUTE IMMEDIATE 'CREATE ROLE ' || cUser || '_FADMSUBADMIN NOT IDENTIFIED';
  EXECUTE IMMEDIATE 'REVOKE      ' || cUser || '_FADMSUBADMIN FROM ' || USER;
  EXECUTE IMMEDIATE 'CREATE ROLE ' || cUser || '_FADMUSER     NOT IDENTIFIED';
  EXECUTE IMMEDIATE 'REVOKE      ' || cUser || '_FADMUSER     FROM ' || USER;
  EXECUTE IMMEDIATE 'CREATE ROLE ' || cUser || '_FADMPUBLIC   NOT IDENTIFIED';
  EXECUTE IMMEDIATE 'REVOKE      ' || cUser || '_FADMPUBLIC   FROM ' || USER;
  EXECUTE IMMEDIATE 'CREATE ROLE ' || cUser || '_KRIC         NOT IDENTIFIED';
  EXECUTE IMMEDIATE 'REVOKE      ' || cUser || '_KRIC         FROM ' || USER;
 
 /* разрешения связанным ролям */
  EXECUTE IMMEDIATE 'GRANT CREATE USER         TO ' || cUser || '_FADMSUBADMIN';
  EXECUTE IMMEDIATE 'GRANT ALTER  USER         TO ' || cUser || '_FADMSUBADMIN';
  EXECUTE IMMEDIATE 'GRANT DROP   USER         TO ' || cUser || '_FADMSUBADMIN';
  EXECUTE IMMEDIATE 'GRANT SELECT_CATALOG_ROLE TO ' || cUser || '_FADMSUBADMIN';
  EXECUTE IMMEDIATE 'GRANT CONNECT             TO ' || cUser || '_FADMUSER';
  EXECUTE IMMEDIATE 'GRANT CREATE SESSION      TO ' || cUser || '_FADMUSER';
  EXECUTE IMMEDIATE 'GRANT ALTER  SESSION      TO ' || cUser || '_FADMUSER';
  EXECUTE IMMEDIATE 'GRANT CONNECT             TO ' || cUser || '_FADMPUBLIC';
  EXECUTE IMMEDIATE 'GRANT CREATE USER         TO ' || cUser || '_FADMPUBLIC';
  EXECUTE IMMEDIATE 'GRANT CONNECT             TO ' || cUser || '_KRIC';
  EXECUTE IMMEDIATE 'GRANT CREATE SESSION      TO ' || cUser || '_KRIC';
  EXECUTE IMMEDIATE 'GRANT ALTER  SESSION      TO ' || cUser || '_KRIC';

END;


/