sf_create_users.sql
Download SQL script
sf_create_users.sql
-- ----------------------------------------------------------------------------
-- Script Name : st_create_users.sql
-- Author : Pete Finnigan
-- Date : November 2023
-- ----------------------------------------------------------------------------
-- Description : SQL Firewall
-- creates the sample schema and connection user
-- ----------------------------------------------------------------------------
-- Maintainer : Pete Finnigan (www.petefinnigan.com)
-- Copyright : Copyright (C) 2003 - 2023 PeteFinnigan.com Limited. All rights
-- reserved. All registered trademarks are the property of their
-- respective owners and are hereby acknowledged.
-- ----------------------------------------------------------------------------
-- License : This software is licensed to you by PeteFinnigan.com Limited. All
-- rights and ownership and copyright in the software are retained by
-- PeteFinnigan.com Limited in all possible cases. Posession of this
-- software does not infer any additonal rights. If you receive this
-- software without these copyright notices and license text, this text,
-- license and copyright still applies. This text, copyright and license
-- must not be removed under any circumstances. This applies to all
-- text displayed as comments up until and including the version history,
-- This text also applies to any copyright, banner or other text
-- refering to PeteFinnigan.com Limited ownership that is output by
-- the program.
--
-- This software is free to use BUT it is NOT open source and NOT GPL
-- or any similar license and it is NOT in the public domain.
--
-- You are permitted to use this software commercially or privately
-- provided these notices or banners as described are not removed. You
-- may modify the software and use it internally but this does not
-- infer any additonal rights in the software. i.e. if you delete
-- some of our code or change variable names or add features that does
-- not make it your code and does not give you the right to remove
-- our ownership in this software shown in these notices. This software must
-- not be made available or published in anyway, any language, any
-- modified form or original form except by PeteFinnigan.com Limited.
-- You must not incorporate this software into any free or commercial
-- product or software and you must not sell or give away any software that
-- includes this sofware.
--
--
-- In short this text is not written by a lawyer so please respect the
-- intent that you can use or modify it freely but not give it away
-- yourself or take away our right to attribution.
-- If someone else needs a copy please ask them to come to PeteFinnigan.com
-- Limited and we will happily let them also have a free copy. Also
-- as you may expect freely use does not include adding this software to
-- a commercial or free product (without our permission) but you
-- can use it internally in projects. We put our time into the free scripts
-- on our website or training courses and give these tools away for free and
-- in return we expect our copyright and ownership to always remain.
-- We like to help people but we also want to benefit from the fact
-- our name becomes known through these scripts and tools and software
-- that we make. We hope this makes sense.
--
-- ----------------------------------------------------------------------------
-- TODO
--
-- Version History
-- ==================
--
-- version who desc
-- ======= === =========================
-- 0.1.1 pxf first issue
-- ----------------------------------------------------------------------------
-- spoof.sql
spool sf_create_users.lis
set echo on
-- ----------------------------------------------------------------------------
-- Connect to SYSDBA to create the sample objects and users
-- ----------------------------------------------------------------------------
set echo off
prompt - connect to SYS to create the sample objects and user...
prompt press any key to continue....
pause
-- connect as sys
@@cs.sql
set echo on
sho user
-- ----------------------------------------------------------------------------
-- Create the orablog user
-- ----------------------------------------------------------------------------
create user orablog identified by orablog;
grant create session, create table, create procedure to orablog;
grant unlimited tablespace to orablog;
-- ----------------------------------------------------------------------------
-- Create orablog objects
-- ----------------------------------------------------------------------------
set echo off
prompt - create the sample tables and data for ORABLOG...
prompt press any key to continue....
pause
-- connect as orablog
define username = 'orablog'
define password = 'orablog'
@@c.sql
set echo on
sho user
create table credit_card
(
name_on_card varchar2(100),
first_name varchar2(50),
last_name varchar2(50),
PAN raw(100)
)
/
insert into credit_card
(name_on_card,first_name,last_name,pan)
values('Pete Finnigan','Pete','Finnigan','4049877198543457');
insert into credit_card
(name_on_card,first_name,last_name,pan)
values('Zulia Finnigan','Zulia','Finnigan','3742345698766678');
commit;
create table customer (fullname varchar2(30),firstname varchar2(30), lastname varchar2(30))
/
insert into customer (fullname,firstname,lastname) values ('Pete Finnigan','Pete','Finnigan');
insert into customer (fullname,firstname,lastname) values ('Zulia Finnigan','Zulia','Finnigan');
commit;
set echo off
prompt - create the sample PL/SQL procedure for ORABLOG...
prompt press any key to continue....
pause
set echo on
create or replace procedure custa(pv_name in varchar2) is
lv_stmt varchar2(2000);
type c_ref is ref cursor;
c c_ref;
name credit_card.name_on_card%type;
Begin
lv_stmt:='select name_on_card from credit_card '||
'where last_name = '''||pv_name||'''';
open c for lv_stmt;
loop
fetch c into name;
if(c%notfound) then
exit;
end if;
dbms_output.put_line('name:=['||name||']');
end loop;
close c;
end;
/
-- ----------------------------------------------------------------------------
-- Create the VM access user and grants
-- ----------------------------------------------------------------------------
set echo off
prompt - connect to SYS to create the access user VM...
prompt press any key to continue....
pause
-- connect as sys
@@cs.sql
set echo on
sho user
create user vm identified by vm;
grant create session to vm;
grant select, insert on orablog.customer to vm;
grant execute on orablog.custa to vm;
spool off
