jueves, 30 de septiembre de 2010

Transparent Data Encryption

Transparent Data Encryption

TDE was first introduced in Oracle 10g Release 2. It is an encryption at column level. Oracle 11g introduced the encryption at tablespace level.

Enterprise Edition.

[edit] Test case
Create a "wallet" directory in $ORACLE_BASE/admin/$ORACLE_SID where Oracle can store its encryption key. If not, you will get error: ORA-28368: cannot auto-create wallet.

$ mkdir /app/oracle/admin/orcl/wallet
Create the wallet to hold the encryption key:

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "secretpassword";
The above created wallet must be reopened after an instance restart:

SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "secretpassword";
Create a table with encrypted columns:

CREATE TABLE tde_test (
id NUMBER,
data VARCHAR2(30) ENCRYPT
);
INSERT INTO tde_test (id, data) VALUES (1, 'This data in encrypted!');
Select from the table to see the data (wallet is still open):

SQL> SELECT data FROM tde_test;
DATA
------------------------------
This data in encrypted!
Closing the wallet to prevent access to encrypted columns:

SQL> ALTER SYSTEM SET WALLET CLOSE;
Select from the table to see the data (wallet is closed):

SQL> SELECT data FROM tde_test;
SELECT data FROM tde_test
*
ERROR at line 1:
ORA-28365: wallet is not open

EXAMPLE POWER


1. Crear carpeta wallet en

C:\oracle\product\10.2.0\admin\POWER\wallet
Crear wallet

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "0r1cl310g";

Crear columna encriptada

alter table "DBO"."A" modify ( "A" ENCRYPT USING 'AES192' );

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "0r1cl310g";

ALTER SYSTEM SET WALLET CLOSE;

alter table "DBO"."A" modify ( "A" DECRYPT );





viernes, 17 de septiembre de 2010

Oracle Database 10g Release 2 (10.2.0.2) Installation On Solaris 10 (x86)

Oracle Database 10g Release 2 (10.2.0.2) Installation On Solaris 10 (x86)
In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.2) on Solaris 10 (x86). The article is based on a default server installation as shown here. Alternative installations may require a different setup procedure.

•Download Software
•Unpack Files
•Hosts File
•Set Kernel Parameters
•Setup
•Installation
•Post Installation
Download Software
Download the following software:

•Oracle Database 10g Downloads
Unpack Files
Unzip the files:

unzip 10202_database_solx86.zipYou should now have a single directory called "database" containing installation files.
Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

Set Kernel Parameters
In previous versions of Solaris, kernel parameters were amended by adding entries to the "/etc/system" file, followed by a system reboot.

set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100The Oracle installer recognizes kernel parameters set using this method, but it is now deprecated in favour of resource control projects, explained below.

As the root user, issue the following command.

projadd oracleAppend the following line to the "/etc/user_attr" file.

oracle::::project=oracleIf you've performed a default installation, it is likely that the only kernel parameter you need to alter is "max-shm-memory". To check the current value issue the following command.

# prctl -n project.max-shm-memory -i project oracle
project: 100: oracle
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 254MB - deny -
system 16.0EB max deny -
#To reset this value, make sure at least one session is logged in as the oracle user, then from the root user issue the following commands.

# prctl -n project.max-shm-memory -v 4gb -r -i project oracle
# projmod -s -K "project.max-shm-memory=(priv,4gb,deny)" oracleThe first dynamically resets the value, while the second makes changes to the "/etc/project" file so the value is persistent between reboots.

# cat /etc/project
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
oracle:100::::project.max-shm-memory=(priv,4294967296,deny)
#The Oracle installer seems incapable of recognising kernel parameter set using resource control projects, but if you ignore the warnings the installation completes successfully.
Setup
Add the "SUNWi1cs" and "SUNWi15cs" packages using the "pkgadd" command.

# pkgadd -d /cdrom/sol_10_106_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

Processing package instance from

X11 ISO8859-1 Codeset Support(i386) 2.0,REV=2004.10.17.15.04
Copyright 2004 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

This appears to be an attempt to install the same architecture and
version of a package which is already installed. This installation
will attempt to overwrite this package.

Using as the package base directory.
## Processing package information.
## Processing system information.
16 package pathnames are already properly installed.
## Verifying package dependencies.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of [y,n,?] y

Installing X11 ISO8859-1 Codeset Support as

## Installing part 1 of 1.

Installation of was successful.

Processing package instance from

X11 ISO8859-15 Codeset Support(i386) 2.0,REV=2004.10.17.15.04
Copyright 2004 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

This appears to be an attempt to install the same architecture and
version of a package which is already installed. This installation
will attempt to overwrite this package.

Using as the package base directory.
## Processing package information.
## Processing system information.
21 package pathnames are already properly installed.
## Verifying package dependencies.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of [y,n,?] y

Installing X11 ISO8859-15 Codeset Support as

## Installing part 1 of 1.

Installation of was successful.
#Create the new groups and users:

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba -d /export/home/oracle oracle
mkdir /export/home/oracle
chown oracle:oinstall /export/home/oracle
passwd -r files oracleCreate the directories in which the Oracle software will be installed:

mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle:oinstall /u01If you have not partitioned your disks to allow a "/u01" mount point, you may want to install the software in the "/export/home/oracle" directory as follows:

mkdir -p /export/home/oracle/product/10.2.0/db_1
chown -R oracle:oinstall /export/home/oracleLogin as the oracle user and add the following lines at the end of the .profile file, making sure you have set the correct ORACLE_BASE value:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Select the appropriate ORACLE_BASE
#ORACLE_BASE=/export/home/oracle; export ORACLE_BASE
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATHInstallation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=:0.0; export DISPLAYStart the Oracle Universal Installer (OUI) by issuing the following command in the database directory:

./runInstaller