Database User and Role Creation

set feed off
set echo off
set head on
set verify off

REM
REM
REM ||Title : crtschem.sql
REM ||
REM || Purpose : This scripts is used to create a user which will normally
REM || house the schema for an applications. Addition Privileges
REM || may be needed. Quotas on other tablespaces may be needed.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema :
REM ||
REM


UNDEFINE username
UNDEFINE password
UNDEFINE def_tablespace
UNDEFINE def_tablespace_quota
UNDEFINE temp_tablespace


ACCEPT username PROMPT 'Enter username for schema owner : '
ACCEPT password PROMPT 'Enter password schema owner : '

SELECT tablespace_name,SUM(bytes)/1024/1024 mb_free
FROM dba_free_space
GROUP BY tablespace_name
/


prompt

ACCEPT def_tablespace PROMPT 'Enter default tablespace schema owner : '
ACCEPT def_tablespace_quota PROMPT 'Enter QUOTA on default tablespace schema owner : '
ACCEPT temp_tablespace PROMPT 'Enter temporary tablespace schema owner : '

 

set echo off
set feed on

--
-- Create the Schema Owner
--

CREATE USER &&username
IDENTIFIED BY &&password
DEFAULT TABLESPACE &&def_tablespace
TEMPORARY TABLESPACE &&temp_tablespace
/


GRANT CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE SYNONYM,
CREATE DATABASE LINK,
CREATE SEQUENCE,
CREATE TRIGGER
TO &&username
/

ALTER USER &&username QUOTA &&def_tablespace_quota on &&def_tablespace
/

CREATE ROLE &&username._USER
/

--
-- Create the read only USER
--

CREATE USER &&username._RO
IDENTIFIED BY &&password._RO
DEFAULT TABLESPACE &&def_tablespace
TEMPORARY TABLESPACE &&temp_tablespace
/

GRANT CREATE SESSION,
CREATE SYNONYM
TO &&username._RO
/

ALTER USER &&username._RO QUOTA 2M ON &&def_tablespace
/

CREATE ROLE &&username._USER_RO
/

GRANT &&username._USER_RO TO &&username._RO
/

SELECT username
FROM dba_users
WHERE username IN ( UPPER('&&username._RO'),UPPER('&&username'))
/

SELECT role
FROM dba_roles
WHERE role IN ( UPPER('&&username._USER_RO'),UPPER('&&username._USER'))
/


UNDEFINE username
UNDEFINE password
UNDEFINE def_tablespace
UNDEFINE def_tablespace_quota
UNDEFINE temp_tablespace

 
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.