"REM WORKSPACETAB0","DBA views",,19
select * from dba_users
  order by username;

select * from user_tables
  order by table_name;

select * from dba_tablespaces
  order by tablespace_name;

select * from dba_data_files;

select * from dba_procedures
  where owner not in ('SYS');

select * from dba_errors
  where owner not in ('SYS');

select * from dba_profiles
  order by profile, 2;
"REM WORKSPACETAB1","Password verify",,140
-- !!!! TO RUN AS SYS !!!!

Rem    DESCRIPTION
Rem      This is a script for enabling the password management features
Rem      by setting the default password resource limits.

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
--
-- v1.0 - 16-03-2005 - Dawid Wojcik <dawid.wojcik@cern.ch>
--

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
   policies integer;

   pw_err1 CONSTANT VARCHAR2(255) := 'Password same as or similar to user';
   pw_err2 CONSTANT VARCHAR2(255) := 'Password length less than 6';
   pw_err3 CONSTANT VARCHAR2(255) := 'Password too simple';
   pw_err4 CONSTANT VARCHAR2(255) := 'Password should contain at least 2 of the following: letters, digits and punctuations';
   pw_err5 CONSTANT VARCHAR2(255) := 'Password should differ by at least 3 characters from the old one';


BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- Check if the password is same as the username
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20001, pw_err1);
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 6 THEN
      raise_application_error(-20002, pw_err2);
   END IF;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, pw_err3);
   END IF;

   -- Check if the password contains at least at least 3 of the following:
   -- upper case letters, lower case letters, digits and punctuation
   policies:= 0;

   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
            policies:= policies + 1;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;

   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
            policies:= policies + 1;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;

   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
            policies:= policies + 1;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;

   <<endsearch>>
   IF policies < 2 THEN
      raise_application_error(-20003, pw_err4);
   END IF;



   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);

     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       differ := abs(differ);
       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20004, pw_err5);
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

"REM WORKSPACETAB2","Role and profile",,52
-- !!!! TO RUN AS SYS !!!!

--
-- cern_profile_role_creation.sql
--
-- Creates CERN standard Roles and profiles
--
-- v1.1 - 16-03-2005 - Miguel Anjo <miguel.anjo@cern.ch>
--
-- This script must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script
--

-- This user can create any type of object and do SQL tuning sessions
--DROP ROLE cern_dev_role;
CREATE ROLE cern_dev_role;

GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE ROLE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, QUERY REWRITE, ADVISOR  to cern_dev_role;

-- Need to create PLUSTRACE role before grant it
--@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL;
GRANT PLUSTRACE to cern_dev_role;

-- These users can connect and have to be granted privileges to access to objects.
-- They can create synonyms and/or views and query rewrite
--DROP ROLE cern_app_role;
CREATE ROLE cern_app_role;

GRANT CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, QUERY REWRITE TO cern_app_role;

-- Development profile
-- To be given to developers and production owner accounts
CREATE PROFILE cern_dev_profile LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME 1/1440
   PASSWORD_LIFE_TIME 365
   PASSWORD_REUSE_TIME 180
   PASSWORD_GRACE_TIME 10
   PASSWORD_VERIFY_FUNCTION verify_function
   SESSIONS_PER_USER 10
   IDLE_TIME  2880;

-- Application profile
-- To be given to application reader and writer accounts
CREATE PROFILE cern_app_profile LIMIT
   FAILED_LOGIN_ATTEMPTS 10
   PASSWORD_LOCK_TIME 1/1440
   PASSWORD_LIFE_TIME UNLIMITED
   PASSWORD_VERIFY_FUNCTION verify_function
   SESSIONS_PER_USER 1000
   IDLE_TIME  2880;

"REM WORKSPACETAB3","Create tablespace",,15
define data_ts=ATLAS_ESCI;
--define indx_ts=INDX01;

-- drop tablespace "&DATA_TS" including contents and datafiles;

create tablespace "&DATA_TS"
  DATAFILE '/ORA/dbs03/oradata/INTDB10g/&DATA_TS\.dbf' SIZE 15G
  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--create tablespace "&INDX_TS"
--  DATAFILE '/ORA/dbs04/oradata/D10/"&INDX_TS".dbf' SIZE 50M
--  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;



"REM WORKSPACETAB4","Create DEV user",,16
define theuser=ATLAS_ESCI;
define ts_data=ATLAS_ESCI;
--define ts_idx=INDX01;
--define temp_ts=TEMP -- pdb01
define temp_ts=TEMP01 -- devdb10
define thepass=cern2005;

create user &THEUSER identified by &THEPASS
  default tablespace &TS_DATA
  quota 100M on &TS_DATA
  temporary tablespace &TEMP_TS
  profile cern_dev_profile
  PASSWORD EXPIRE;

grant cern_dev_role to &THEUSER;

"REM WORKSPACETAB5","Create APP user",,16
define theuser=ATLAS_ESCI_READER;
define ts_data=ATLAS_ESCI;
--define ts_idx=INDX01;
--define temp_ts=TEMP -- pdb01
define temp_ts=TEMP01 -- devdb10
define thepass=cern2005;

create user &THEUSER identified by &THEPASS
  default tablespace &TS_DATA
  quota unlimited on &TS_DATA
  temporary tablespace &TEMP_TS
  profile cern_app_profile
  PASSWORD EXPIRE;

grant cern_app_role to &THEUSER;

"REM WORKSPACETAB6","Create DBA user",,12
define theuser=MANJO_DBA;
define ts_data=DATA01;
define ts_idx=INDX01;
--define temp_ts=TEMP -- pdb01
define temp_ts=TEMP01 -- devdb10
define thepass=macrev36;

create user &THEUSER identified by &THEPASS
  default tablespace &TS_DATA
  temporary tablespace &TEMP_TS;

grant dba to &THEUSER;