OraMatt: YABAOracle

#!/bin/bash
 #===============================================================================================================
 #
 # FILE: TDE_Example
 #
 # USAGE: Run it...
 #
 # DESCRIPTION:
 # OPTIONS:
 # REQUIREMENTS: ASM or Oracle Managed Files
 # AUTHOR: Matt D
 # CREATED: 10.15.2012
 # VERSION: 1.0
 # EUL : THIS CODE IS OFFERED ON AN “AS-IS” BASIS AND NO WARRANTY, EITHER EXPRESSED OR IMPLIED, IS GIVEN.
 # THE AUTHOR EXPRESSLY DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED.
 # YOU ASSUME ALL RISK ASSOCIATED WITH THE QUALITY, PERFORMANCE, INSTALLATION AND USE OF THE SOFTWARE INCLUDING,
 # BUT NOT LIMITED TO, THE RISKS OF PROGRAM ERRORS, DAMAGE TO EQUIPMENT, LOSS OF DATA OR SOFTWARE PROGRAMS,
 # OR UNAVAILABILITY OR INTERRUPTION OF OPERATIONS.
 # YOU ARE SOLELY RESPONSIBLE FOR DETERMINING THE APPROPRIATENESS OF USE THE SOFTWARE AND ASSUME ALL RISKS ASSOCIATED WITH ITS USE.
 #
 #
 #
 #
 #
 #
 #===============================================================================================================
export NOW=`date +%m_%d_%y`
# Get the Wallet location
echo "Where would you like to store the TDE wallet? >"
read TDE

# Check for the Wallet location & make if not there
if [ ! -d $TDE ]
 then
 echo "Making TDE Wallet location at: "$TDE
 mkdir -p $TDE
fi
# Add Wallet location to sqlnet.ora
export TNS_ADMIN=`lsnrctl stat | grep -i parameter | awk '{print $4}' |sed 's/listener.ora//g'`

# A
echo "ENCRYPTION_WALLET_LOCATION=" >/tmp/tde.tmp
echo "(SOURCE=" >>/tmp/tde.tmp
echo "(METHOD=file)" >>/tmp/tde.tmp
echo "(METHOD_DATA=" >>/tmp/tde.tmp
echo "(DIRECTORY=${TDE})))" >>/tmp/tde.tmp

# Backup the sqlnet.ora
cp $TNS_ADMIN/sqlnet.ora $TNS_ADMIN/sqlnet.${NOW}

# Add Wallet location to sqlnet.ora
cat /tmp/tde.tmp >> $TNS_ADMIN/sqlnet.ora

# Turn on TDE
export ORACLE_SID=orcl
sqlplus / as sysdba <<EOF
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “ORACLE12C”;
exit;
EOF

# Create a bigfile tablespace that is encrypted
export ORACLE_SID=orcl
sqlplus / as sysdba <<EOF
CREATE BIGFILE TABLESPACE "SECURE" DEFAULT COMPRESS FOR ALL OPERATIONS STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256';
GRANT DBA TO TDE_TEST IDENTIFIED BY ORACLE;
exit;
EOF

# Create a table & move it to the encrypted tablespace
export ORACLE_SID=orcl
sqlplus tde_test/oracle <<EOF
spool /tmp/tde_tests.log

CREATE TABLE TEST TABLESPACE USERS AS SELECT * FROM ALL_OBJECTS ;
CREATE INDEX IDX_TEST ON TEST(OWNER) TABLESPACE USERS;

SELECT A.OWNER "OWNER", A.TABLE_NAME "TABLE NAME", E.ENCRYPTIONALG "ALGORITHM"
FROM
DBA_TABLES A, V\$ENCRYPTED_TABLESPACES E
WHERE
A.TABLESPACE_NAME IN (SELECT T.NAME FROM V\$TABLESPACE T,
V\$ENCRYPTED_TABLESPACES E WHERE T.TS# = E.TS#);

SET AUTOT ON;
SELECT OWNER,OBJECT_NAME FROM TEST WHERE ROWNUM < 10;

SET AUTOT OFF;
ALTER TABLE TEST MOVE TABLESPACE SECURE;
ALTER INDEX IDX_TEST REBUILD TABLESPACE SECURE PARALLEL;

SELECT A.OWNER "OWNER", A.TABLE_NAME "TABLE NAME", E.ENCRYPTIONALG "ALGORITHM"
FROM
DBA_TABLES A, V\$ENCRYPTED_TABLESPACES E
WHERE
A.TABLESPACE_NAME IN (SELECT T.NAME FROM V\$TABLESPACE T,
V\$ENCRYPTED_TABLESPACES E WHERE T.TS# = E.TS#);

SET AUTOT ON;
SELECT OWNER,OBJECT_NAME FROM TEST WHERE ROWNUM < 10;

EXIT;
EOF

more /tmp/tde_tests.log