"Dan's Oracle7 Guide" - cp7Tab26Tab.sh
Date: 07 Sep 1993 07:53:28 GMT
Message-ID: <DBIKLE.93Sep7005328_at_alumni.cco.caltech.edu>
The following discussion is an excerpt from "Dan's Oracle7 Guide".
While the guide could not be described as a great literary work, it might prove useful to those DBA's and Application Developers who work with Oracle7.
The guide will be finished sometime in October.
If you want a copy, I'll put you on my mailing list.
The public domain, email, beta version is free.
If you have an extra $20, please send it to my favorite charity:
Amnesty International 322 8th ave. ny NY usa 10001.
All of the trademarks mentioned in this excerpt are owned by their respective owners.
Of course, everything I create has no warranty. If my software or ideas cause you problems, feel free to send me hate mail.
-Dan
Daniel B. Bikle
dbikle_at_alumni.caltech.edu
415/854-9542
P.O. BOX 'D'
MENLO PARK CA 94026
#! /bin/sh
# cp7Tab26Tab.sh
# Demo of selecting rows from an Oracle7 table into a V6 table # using a database link. # Perhaps the most important feature of this script is # the demonstration that a password be hard-coded # into the database link. In V6, a password needs to be hard-coded # into database links; a major security hole. # assumptions:
# This script assumes that an ops$account for the UNIX user running this # shell script has been created in both the V6 and Oracle7 dbs's.
# setup V6 env
ORACLE_HOME=/q/o6
ORACLE_SID=x6
TWO_TASK=P:x6 # this specifies use of the pipe driver against the
# x6 database; explicitly set here to avoid getting it
# wrong from the parent shell
export ORACLE_SID ORACLE_HOME TWO_TASK
# create the link using a sql script embedded within this shell script
$ORACLE_HOME/bin/sqlplus / << EndOfsql
SET ECHO ON
SET TIME OFF
SET TRIMOUT ON
SET TAB ON
SET LINESIZE 75
SET PAGESIZE 1111
rem let's spool a logfile so we know what happened
spool cp7Tab26Tab
rem remind ourselves we are connected to V6 SET SQLPROMPT 'V6sql> '
rem Demo of selecting rows from an Oracle7 table into a rem V6 table.
rem let's create a "no password" database link
DROP DATABASE LINK tcp_x7_np
/
CREATE DATABASE LINK tcp_x7_np
USING 'T:ava2:x7'
/
rem try to use the "no password" link; IT WILL FAIL
rem (contrast this to what happens if the link is in
rem an Oracle7 dbs: it works just fine)
SELECT SYSDATE FROM DUAL_at_tcp_x7_np
/
rem The rest of this script will demonstrate the best rem way to get around this V6 shortcoming which is a need rem to hard-code passwords into database links. rem The idea of the work-around is simple; use a rem temporary password during the duration of the rem script.
rem Let's keep a copy of the old password for the user rem running this script since we are about to change it and rem we want to be able to change it back to what it was rem before this instant.
rem connect to the Oracle7 dbs
rem CONNECT /_at_T:ava2:x7 /* use TCP/IP */ rem CONNECT /_at_P:x7 /* use Pipe driver if both dbs's */ /* on this machine */
CONNECT /_at_T:ava2:x7 /* use TCP/IP */
SET SQLPROMPT 'Oracle7-sql> '
rem the sqlplus command below creates a variable named Y COLUMN PASSWORD NEW_VALUE Y rem the sql command below fills the variable named Y rem with an encoded version of the old password
SELECT PASSWORD
FROM DBA_USERS
WHERE USERNAME = USER
/
rem the sql command below displays the variable named Y
SELECT &Y FROM DUAL
/
rem now that we have a copy of the old password stored in a rem variable named Y let's change the password to a temp rem password to be used during the duration of this script.
rem but first we need to place the username in to a rem variable named U
COLUMN USERNAME NEW_VALUE U
SELECT MAX(USER) USERNAME FROM DUAL
/
rem the sql command below displays the variable named U
SELECT '&U' FROM DUAL
/
rem now we can change the password; we will use the Oracle7 rem syntax rather than the V6 syntax
ALTER USER &U IDENTIFIED BY GO49ERS
/
rem test the password; re-connect to Oracle7 connect &U/GO49ERS_at_T:ava2:x7
rem now that the password is set to a temp value, we can rem continue with the demo
rem create and fill the table which will get copied from rem Oracle7 to V6
DROP TABLE O7PHONE
/
CREATE TABLE O7PHONE
(
PHONEID NUMBER /* pk */ ,PERSONID NUMBER /* fk */ ,PHONENUM VARCHAR (22) ,TYPE VARCHAR (22) /* modem, fax, voice */)
/
DROP SEQUENCE O7PHONESEQ;
CREATE SEQUENCE O7PHONESEQ;
INSERT INTO O7PHONE VALUES
(O7PHONESEQ.NEXTVAL, '2','415/506-1500', 'voice');
INSERT INTO O7PHONE VALUES
(O7PHONESEQ.NEXTVAL, '2','800/633-1072', 'voice');
INSERT INTO O7PHONE VALUES
(O7PHONESEQ.NEXTVAL, '2','415/598-9350', 'modem');
INSERT INTO O7PHONE VALUES
(O7PHONESEQ.NEXTVAL, '2','207/829-4599', 'fax');
COMMIT;
rem connect to V6
CONNECT /
SET SQLPROMPT 'V6-sql> '
rem let's create a dbs link with hard-coded password
rem maybe it's already there; let's drop it, then create it
DROP DATABASE LINK pipe_x7
/
CREATE DATABASE LINK pipe_x7
CONNECT TO &U IDENTIFIED BY GO49ERS
USING 'P:x7'
/
rem let's create another one based on TCP/IP
DROP DATABASE LINK tcp_x7
/
CREATE DATABASE LINK tcp_x7
CONNECT TO &U IDENTIFIED BY GO49ERS
USING 'T:ava2:x7'
/
rem create table in V6 dbs
rem maybe it's already there; let's drop it, then create it
DROP TABLE V6PHONE
/
rem create an empty copy
CREATE TABLE V6PHONE
AS
SELECT * FROM O7PHONE_at_pipe_x7
WHERE 1=2
/
rem Let's see what it looks like
desc V6PHONE
rem Let's see what the other 1 looks like desc O7PHONE_at_pipe_x7
rem select rows from the Oracle7 table into the V6 table
INSERT INTO V6PHONE
SELECT * FROM O7PHONE_at_pipe_x7
/
rem select rows from the Oracle7 table into the V6 table
rem using the TCP/IP driver link
INSERT INTO V6PHONE
SELECT * FROM O7PHONE_at_tcp_x7
/
rem Let's see what came through the link
SELECT * FROM V6PHONE
/
rem connect to the Oracle7 dbs
CONNECT /_at_T:ava2:x7 /* use TCP/IP */
SET SQLPROMPT 'Oracle7-sql> '
rem change the password to old value
ALTER USER &U IDENTIFIED BY VALUES '&Y'
/
commit;
rem try to connect through the link to verify
rem that it has been disabled
connect /
SET SQLPROMPT 'V6-sql> '
DESC O7PHONE_at_tcp_x7
EndOfsql
#end of cp7Tab26Tab.sh
Output found in in the spool file is displayed below:
/////////////////////////////////////////////////////
OPS$DAN v6sql> OPS$DAN v6sql> rem remind ourselves we are connected to V6 OPS$DAN v6sql> SET SQLPROMPT 'V6sql> ' V6sql> V6sql> rem Demo of selecting rows from an Oracle7 table into a V6sql> rem V6 table. V6sql>
V6sql> rem let's create a "no password" database link V6sql> DROP DATABASE LINK tcp_x7_np
2 /
Database link dropped.
V6sql>
V6sql> CREATE DATABASE LINK tcp_x7_np
2 USING 'T:ava2:x7'
3 /
Database link created.
V6sql> V6sql> rem try to use the "no password" link; IT WILL FAIL V6sql> rem (contrast this to what happens if the link is in V6sql> rem an Oracle7 dbs: it works just fine) V6sql> SELECT SYSDATE FROM DUAL_at_tcp_x7_np2 /
SELECT SYSDATE FROM DUAL_at_tcp_x7_np
*
ERROR at line 1:
ORA-01005: null password given; logon denied
V6sql> V6sql> rem The rest of this script will demonstrate the best V6sql> rem way to get around this V6 shortcoming which is a need V6sql> rem to hard-code passwords into database links. V6sql> rem The idea of the work-around is simple; use a V6sql> rem temporary password during the duration of the V6sql> rem script. V6sql> V6sql> V6sql> rem Let's keep a copy of the old password for the user V6sql> rem running this script since we are about to change it and V6sql> rem we want to be able to change it back to what it was V6sql> rem before this instant. V6sql> V6sql> rem connect to the Oracle7 dbs V6sql> rem CONNECT /_at_T:ava2:x7 /* use TCP/IP */ V6sql> rem CONNECT /_at_P:x7 /* use Pipe driver if both dbs's */ V6sql> /* on this machine */ V6sql> V6sql> CONNECT /_at_T:ava2:x7 /* use TCP/IP */Connected.
V6sql> SET SQLPROMPT 'Oracle7-sql> '
Oracle7-sql> Oracle7-sql> rem the sqlplus command below creates a variable named Y Oracle7-sql> COLUMN PASSWORD NEW_VALUE Y Oracle7-sql> Oracle7-sql> rem the sql command below fills the variable named Y Oracle7-sql> rem with an encoded version of the old password Oracle7-sql> Oracle7-sql> SELECT PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME = USER
4 /
PASSWORD ------------------------------ 3657B9E3EA9C9E7A Oracle7-sql>
Oracle7-sql> rem the sql command below displays the variable named Y Oracle7-sql> SELECT &Y FROM DUAL
2 /
old 1: SELECT &Y FROM DUAL
new 1: SELECT 3657B9E3EA9C9E7A FROM DUAL
B9E3EA9C9E7A
------------
3657
Oracle7-sql>
Oracle7-sql> rem now that we have a copy of the old password stored in a
Oracle7-sql> rem variable named Y let's change the password to a temp
Oracle7-sql> rem password to be used during the duration of this script.
Oracle7-sql>
Oracle7-sql> rem but first we need to place the username in to a
Oracle7-sql> rem variable named U
Oracle7-sql>
Oracle7-sql> COLUMN USERNAME NEW_VALUE U Oracle7-sql> SELECT MAX(USER) USERNAME FROM DUAL 2 /
USERNAME ------------------------------ OPS$DAN Oracle7-sql>
Oracle7-sql> rem the sql command below displays the variable named U Oracle7-sql> SELECT '&U' FROM DUAL
2 /
old 1: SELECT '&U' FROM DUAL
new 1: SELECT 'OPS$DAN' FROM DUAL
'OPS$DA ------- OPS$DAN Oracle7-sql> Oracle7-sql> rem now we can change the password; we will use the Oracle7 Oracle7-sql> rem syntax rather than the V6 syntaxOracle7-sql>
Oracle7-sql> ALTER USER &U IDENTIFIED BY GO49ERS 2 /
old 1: ALTER USER &U IDENTIFIED BY GO49ERS new 1: ALTER USER OPS$DAN IDENTIFIED BY GO49ERS
User altered.
Oracle7-sql> Oracle7-sql> rem test the password; re-connect to Oracle7 Oracle7-sql> connect &U/GO49ERS_at_T:ava2:x7Connected.
Oracle7-sql> Oracle7-sql> rem now that the password is set to a temp value, we can Oracle7-sql> rem continue with the demo Oracle7-sql> Oracle7-sql> rem create and fill the table which will get copied from Oracle7-sql> rem Oracle7 to V6 Oracle7-sql> Oracle7-sql> DROP TABLE O7PHONE
2 /
Table dropped.
Oracle7-sql>
Oracle7-sql> CREATE TABLE O7PHONE
2 (
3 PHONEID NUMBER /* pk */ 4 ,PERSONID NUMBER /* fk */ 5 ,PHONENUM VARCHAR (22) 6 ,TYPE VARCHAR (22) /* modem, fax, voice */7 )
8 /
Table created.
Oracle7-sql>
Oracle7-sql> DROP SEQUENCE O7PHONESEQ;
Sequence dropped.
Oracle7-sql> CREATE SEQUENCE O7PHONESEQ;
Sequence created.
Oracle7-sql>
Oracle7-sql> INSERT INTO O7PHONE VALUES
2 (O7PHONESEQ.NEXTVAL, '2','415/506-1500', 'voice');
1 row created.
Oracle7-sql> INSERT INTO O7PHONE VALUES
2 (O7PHONESEQ.NEXTVAL, '2','800/633-1072', 'voice');
1 row created.
Oracle7-sql> INSERT INTO O7PHONE VALUES
2 (O7PHONESEQ.NEXTVAL, '2','415/598-9350', 'modem');
1 row created.
Oracle7-sql> INSERT INTO O7PHONE VALUES
2 (O7PHONESEQ.NEXTVAL, '2','207/829-4599', 'fax');
1 row created.
Oracle7-sql> COMMIT;
Commit complete.
Oracle7-sql> Oracle7-sql> rem connect to V6 Oracle7-sql> CONNECT /
Connected.
Oracle7-sql> SET SQLPROMPT 'V6-sql> '
V6-sql> V6-sql> rem let's create a dbs link with hard-coded password V6-sql> rem maybe it's already there; let's drop it, then create it V6-sql> DROP DATABASE LINK pipe_x7
2 /
Database link dropped.
V6-sql>
V6-sql> CREATE DATABASE LINK pipe_x7
2 CONNECT TO &U IDENTIFIED BY GO49ERS
3 USING 'P:x7'
4 /
old 2: CONNECT TO &U IDENTIFIED BY GO49ERS
new 2: CONNECT TO OPS$DAN IDENTIFIED BY GO49ERS
Database link created.
V6-sql> V6-sql> rem let's create another one based on TCP/IP V6-sql> DROP DATABASE LINK tcp_x7
2 /
Database link dropped.
V6-sql>
V6-sql> CREATE DATABASE LINK tcp_x7
2 CONNECT TO &U IDENTIFIED BY GO49ERS
3 USING 'T:ava2:x7'
4 /
old 2: CONNECT TO &U IDENTIFIED BY GO49ERS
new 2: CONNECT TO OPS$DAN IDENTIFIED BY GO49ERS
Database link created.
V6-sql> V6-sql> rem create table in V6 dbs V6-sql> rem maybe it's already there; let's drop it, then create it V6-sql> DROP TABLE V6PHONE
2 /
Table dropped.
V6-sql> V6-sql> rem create an empty copy V6-sql> CREATE TABLE V6PHONE
2 AS
3 SELECT * FROM O7PHONE_at_pipe_x7
4 WHERE 1=2
5 /
Table created.
V6-sql> V6-sql> rem Let's see what it looks like V6-sql> desc V6PHONE Name Null? Type ------------------------------- -------- ---- PHONEID NUMBER(38) PERSONID NUMBER(38) PHONENUM CHAR(22) TYPE CHAR(22) V6-sql>
V6-sql> rem Let's see what the other 1 looks like V6-sql> desc O7PHONE_at_pipe_x7
Name Null? Type ------------------------------- -------- ---- PHONEID NUMBER(38) PERSONID NUMBER(38) PHONENUM CHAR(22) TYPE CHAR(22) V6-sql>
V6-sql> rem select rows from the Oracle7 table into the V6 table V6-sql> INSERT INTO V6PHONE
2 SELECT * FROM O7PHONE_at_pipe_x7
3 /
4 rows created.
V6-sql> V6-sql> rem select rows from the Oracle7 table into the V6 table V6-sql> rem using the TCP/IP driver link V6-sql> INSERT INTO V6PHONE
2 SELECT * FROM O7PHONE_at_tcp_x7
3 /
4 rows created.
V6-sql> V6-sql> rem Let's see what came through the link V6-sql> SELECT * FROM V6PHONE
2 /
PHONEID PERSONID PHONENUM TYPE
---------- ---------- ---------------------- ----------------------
1 2 415/506-1500 voice
2 2 800/633-1072 voice
3 2 415/598-9350 modem
4 2 207/829-4599 fax
1 2 415/506-1500 voice
2 2 800/633-1072 voice
3 2 415/598-9350 modem
4 2 207/829-4599 fax
8 rows selected.
V6-sql> V6-sql> rem connect to the Oracle7 dbs V6-sql> CONNECT /_at_T:ava2:x7 /* use TCP/IP */Connected.
V6-sql> SET SQLPROMPT 'Oracle7-sql> '
Oracle7-sql> Oracle7-sql> rem change the password to old value Oracle7-sql> ALTER USER &U IDENTIFIED BY VALUES '&Y'2 /
old 1: ALTER USER &U IDENTIFIED BY VALUES '&Y' new 1: ALTER USER OPS$DAN IDENTIFIED BY VALUES '3657B9E3EA9C9E7A'
User altered.
Oracle7-sql>
Oracle7-sql> commit;
Commit complete.
Oracle7-sql> Oracle7-sql> rem try to connect through the link to verify Oracle7-sql> rem that it has been disabled Oracle7-sql> connect /
Connected.
Oracle7-sql> SET SQLPROMPT 'V6-sql> '
V6-sql>
V6-sql> DESC O7PHONE_at_tcp_x7
ERROR:
ORA-01017: invalid username/password; logon denied
V6-sql>
V6-sql>
Received on Tue Sep 07 1993 - 09:53:28 CEST