Installing Oracle Database

CAUTION!   CCC does not encrypt the contents of the database. Database encryption is supported by using an Oracle Server with tablespace encryption enabled through TDE.

You can configure CCC to store its data on an Oracle database instance. Oracle supports Transparent Data Encryption (TDE) on a tablespace.

It is recommended that your organization employ a trained Oracle Database Administrator (DBA) to complete the configuration of a CCC Oracle database.

>See the Oracle Database Administrator's Guide for more information about configuring and managing an Oracle database.

>See the Oracle Database Security Guide for more information about Oracle database security and user authentication.

>See theOracle Database Advanced Security Guide for more information about configuring an Oracle database with TDE.

CCC Oracle Database Parameters for Oracle DBA

The following section contains recommendations for structuring and configuring an Oracle database for integration with CCC. We recommend you transfer this information to your Oracle DBA and request that the Oracle DBA configures a database for use with CCC based on these parameters.

Oracle Database Users

CCC requires the configuration of two users, lunadirector and keycloak, to communicate with the Oracle database. The two users require access to the same tablespaces. The lunadirector user and keycloak user passwords are required when you run the CCC server configuration script.

Review Create User in the Oracle Database SQL Language Reference for more information about creating Oracle database users.

The CCC Oracle database must have the following users:

To create Oracle database users

1.Enter the following commands in sqlplus

CREATE user lunadirector identified by <lunadirector_password> default tablespace <tablespace_name> quota unlimited on <tablespace_name> ;

CREATE user keycloak identified by <keycloak_password> default tablespace <tablespace_name> quota unlimited on <tablespace_name> ;

NOTE   The schema names are the same as the user names. The lunadirector user uses the lunadirector schema, and the keycloak user uses the keycloak schema.

The CCC Oracle database users must have the following privileges:

GRANT CREATE SEQUENCE to lunadirector;

GRANT CREATE SESSION to lunadirector;

GRANT CREATE TABLE to lunadirector;

GRANT CREATE VIEW to lunadirector;

GRANT CREATE PROCEDURE to lunadirector;

GRANT CREATE TRIGGER to lunadirector;

GRANT CREATE SEQUENCE to keycloak;

GRANT CREATE SESSION to keycloak;

GRANT CREATE TABLE to keycloak;

GRANT CREATE VIEW to keycloak;

GRANT CREATE PROCEDURE to keycloak;

GRANT CREATE TRIGGER to keycloak;

NOTE   On Oracle databases the CCC uninstall script does not delete the lunadirector or keycloak user. The CCC uninstall script provides the option to drop all objects related to the lunadirector and keycloak users stored on the Oracle database. Inform your Oracle DBA that the users need to be dropped following the removal of CCC from the system.

Configure a Unique Service Name

You must decide on a unique service name for your CCC Oracle database instance. The service name is required when you run the CCC config.sh script.

Review section 2.8.1 of the Oracle Database Administrator's Guide for more information about configuring a service name for your database instance.

Oracle Wallets

CCC uses SSL to communicate with the database, so you must create an Oracle wallet with an SSL certificate. To enable tablespace encryption through TDE you require an encryption wallet.

When the config.sh script is run the Oracle certificate is transferred to the CCC trust store. When CCC communicates with the Oracle server it compares the Oracle certificate with the certificate stored in the CCC trust store.

See Using Oracle Wallet Manager for more information about Oracle Wallets.

To create an Oracle Auto-login Wallet

You must create an Oracle wallet to securely store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL in the Oracle database.

1.Log into the Oracle database and create a wallet directory.

mkdir u01/app/oracle/wallet

2.Create an auto-login wallet.

orapki wallet create -wallet "u01/app/oracle/wallet" -pwd <wallet_password> -auto_login_local

3.Generate a self-signed certificate and load it into the wallet.

orapki wallet add -wallet "/u01/app/oracle/wallet" -dn "CN=oracle,O=<company>C=<country>" -keysize 2048 -self_signed -validity 7300 -pwd <wallet_password> -sign_alg sha256 -nologo

4.Open the sqlnet.ora file in a text editor. The file is located at $ORACLE_HOME/network/admin/sqlnet.ora. Alter the information so it appears as the following:

WALLET_LOCATION =

 (SOURCE =

 (METHOD = FILE)

  (METHOD_DATA =

  (DIRECTORY = /u01/app/oracle/wallet)

  )

 )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_AES_128_CBC_SHA256)

5.Open the tnsnames.ora file in a text editor. The file is located at $ORACLE_HOME/network/admin/tnsnames.ora. Alter the information so it appears as the following:

<tnsname>=

 (DESCRIPTION=

  (ADDRESS=

  (PROTOCOL=TCPS)

  (HOST=0.0.0.0)

  (PORT=2484)

 )

 (CONNECT_DATA=

 (SERVER=dedicated)

 (SERVICE_NAME=CCC)

 )

)

6.Open the listener.ora file in a text editor. The file is located at $ORACLE_HOME/network/admin/listener.ora. Alter the information so it appears as the following:

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

 (SOURCE =

 (METHOD = FILE)

  (METHOD_DATA =

  (DIRECTORY = /u01/app/oracle/wallet)

  )

 )

LISTENER =

 (DESCRIPTION_LIST =

  (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))

  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

  (ADDRESS = (PROTOCOL = TCPS)(HOST = oracle)(PORT = 2484))

  )

 )

TRACE_LEVEL_LISTENER = 4

TRACE_FILE_LISTENER = listener.trc

7.Check the status of Insrctl by running the command Insrctl status. If the wallet is configured properly the command will complete successfully.

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle)(PORT=2484)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/<wallet>))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "CCC" has 1 instance(s).

Instance "CCC", status READY, has 1 handler(s) for this service...

Service "CCCXDB" has 1 instance(s).

Instance "CCC", status READY, has 1 handler(s) for this service...

The command completed successfully

To create an encryption wallet

For creating an encryption wallet to enable tablespace encryption through TDE on an Oracle database:.

1.Log onto the Oracle database as the oracle user.

2.Create an encryption wallet directory:

mkdir /u01/app/oracle/encryption_wallet

3.Open the sqlnet.ora file in a text editor and add the new section ENCRYPTION_WALLET_LOCATION section to point to the encryption wallet directory.

NOTE   The ENCRYPTION_WALLET_LOCATION information should be made in addition to the WALLET_LOCATION section of the sqlnet.ora file.

The sqlnet.ora file is available at /u01/app/oracle/product/<product_version>/dbhome_1/network/admin. Add the following information to the sqlnet.ora file.

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=FILE)

(METHOD_DATA =

(DIRECTORY= /u01/app/oracle/encryption_wallet)))

4.Create the wallet and assign a wallet password:

orapki wallet create -wallet /u01/app/oracle/encryption_wallet -pwd <encryption_wallet_password>

This will create an ewallet.p12 file in the encryption_wallet directory.

Oracle TDE Example Procedural Sets

The following procedural sets are sample documentation on enabling tablespace encryption through TDE on a CCC Oracle Database. These Oracle databases were configured using the SQLPlus command line interface and the Oracle DBCA tool. They provide information about the process of configuring an Oracle database with tablespace encryption enabled through TDE.

NOTE   Access to the TDE software keystore will allow the user full access to the database.

See theOracle Database Advanced Security Guide for more information about configuring an Oracle database with TDE.

Configuring an Oracle Database with TDE (Optionally: to be used by CCC)

You can configure an Oracle database with tablespace encryption enabled through TDE to support CCC. You create the Oracle database and run the CCC config.sh server configuration script. The following objects and users must exist to configure an Oracle database with tablespace encryption to support CCC:

>a running database instance

>ORACLE_SID environment variable set to SID for database instance

>Oracle Advanced Security

To enable TDE the Oracle user must have access to:

>ADMIN privileges

>Oracle wallet

>Encryption wallet

To configure an Oracle database with TDE to be used by CCC

1.Log in to the database as the database administrator and create the encrypted tablespace.

sqlplus / as sysdba

a.Generate the master encryption key:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <encryption_wallet_password> ;

b.Create an encrypted tablespace:

CREATE TABLESPACE <tablespace_name> DATAFILE /u01/app/oracle/oradata/<database_instance>/datafile/<database_file>SIZE 200M AUTOEXTEND ON NEXT 20M ENCRYPTION USING AES128 DEFAULT STORAGE (ENCRYPT) ;

c.Check if the tablespace was encrypted:

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES ;

The command returns:

TABLESPACE_NAME                   ENC

------------------------------------

SYSTEM                             NO

<Tablespace Name>                 YES

2.Create the lunadirector user and grant the user privileges.

a.Create the lunadirector user and set the default tablespace:

CREATE user lunadirector IDENTIFIED BY <lunadirector_password> DEFAULT TABLESPACE <tablespace_name> QUOTA UNLIMITED ON <tablespace_name> ;

Now, when tables are created for the lunadirector user they will be created in the encrypted tablespace.

b.Grant the lunadirector user privileges.

GRANT CREATE SEQUENCE to lunadirector;

GRANT CREATE SESSION to lunadirector;

GRANT CREATE TABLE to lunadirector;

GRANT CREATE VIEW to lunadirector;

GRANT CREATE PROCEDURE to lunadirector;

GRANT CREATE TRIGGER to lunadirector;

3.Create the keycloak user and grant the user privileges:

a.Create the keycloak user and set the default tablespace:

CREATE user keycloakIDENTIFIED BY <keycloak_password> DEFAULT TABLESPACE <tablespacename> QUOTA UNLIMITED ON <tablespacename> ;

Now, when tables are created for the keycloak user they will be created in the encrypted tablespace.

b.Grant the keycloak user privileges:

GRANT CREATE SEQUENCE to keycloak;

GRANT CREATE SESSION to keycloak;

GRANT CREATE TABLE to keycloak;

GRANT CREATE VIEW to keycloak;

GRANT CREATE PROCEDURE to keycloak;

GRANT CREATE TRIGGER to keycloak;

4.Run the CCC config.sh script.

Enabling Tablespace Encryption on an Oracle Database for an Existing CCC Installation

You can migrate your existing CCC Oracle database to an Oracle database with tablespace encryption. The following objects and users must exist to enable TDE for CCC on the Oracle database:

>Oracle database

>Oracle Advanced Security

>lunadirector user

>keycloak user

To enable TDE the Oracle user must have access to:

>ADMIN privileges

>Oracle Wallet

>Encryption Wallet

To enable tablespace encryption on an Oracle database for an existing CCC installation

1.Stop your CCC server.

service ccc stop

2.Log into the Oracle Database as Oracle user.

3.Export the Oracle data directory:

a.In sqlplus create a directory to export the data to.

create directory <dump_directory> as '/u01/app/oracle/admin/<database_instance>/dpdump/';

b.From a server console as the Oracle user, export the entire <tablespace_name> to the dump directory.

expdp userid="'/ as sysdba'" dumpfile=<dumpfile_name> directory=<dump_directory> logfile=<tablespace_logfile>tablespaces=<tablespace_name>

<dumpfile>.dmp should now be visible in the /u01/app/oracle/admin/<database_instance>/dpdump/ directory.

4.Take a backup of your database.

5.Log into sqlplus and print out the command that created the tablespace.

SELECT dmbs_metadata.get_ddl('TABLESPACE', '<tablespace_name>') FROM DUAL;

The following is an example of the commands output:

CREATE TABLESPACE "<tablespace_name>" DATAFILE

'/u01/app/oracle/oradata/<database_instance>/datafile/<database_file>.dbf' SIZE 20971520

AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO

Copy the output as you will need to edit it in sqlplus at a later stage.

6.Remove the existing tablespace.

a.In sqlplus take the existing tablespace offline.

alter tablespace<tablespace_name> offline;

b.In sqlplus drop the tablespace and delete the data files.

Drop tablespace <tablespace_name> including contents and datafiles;

c.From a Linux command prompt confirm that your data files no longer exists on the tablespace. The following command will fail if the data files were removed.

ls -l /u01/app/oracle/oradata/<database_instance>/datafile/<database_file>.dbf

7.Edit the create tablespace command output from step 5 to add encryption.

a.Take the output from the SELECT dmbs_metadata.get command and remove the following:

PERMANENT

DEFAULT

NOCOMPRESS

NOTE   In our configuration we had to remove these values. Your results may vary.

b. Add the following information as the final line of the CREATE TABLESPACE command.

ENCRYPTION using 'AES256' DEFAULT STORAGE (ENCRYPT)

c.Create your tablespace and the .dbf file. In sqlplus run the new CREATE  TABLESPACE command.

CREATE TABLESPACE "<tablespace_name>" DATAFILE

'/u01/app/oracle/oradata/<database_instance>/datafile/<database_file>.dbf' SIZE 20971520

AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M

LOGGING ONLINE BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO

ENCRYPTION using 'AES256' DEFAULT STORAGE (ENCRYPT);

NOTE   Enabling encryption on a tablespace results in a full table update like any ALTER TABLE command.

d.From a Linux command prompt import the <tablespace> file you created earlier.

impdp userid="'/ as sysdba'" dumpfile=<tablespacedump_file> directory=<dump_directory> logfile=<tablespace_logfile> tablespaces=<tablespace_name>

e.In sqlplus confirm you can see any tables associated with your users.

select table_name from dba_tables where owner = 'LUNADIRECTOR';

select table_name from dba_tables where owner = 'KEYCLOAK';

f.Confirm that users are associated with the correct tablespace.

select * from dba_users where username in ('LUNADIRECTOR', 'KEYCLOAK');

DEFAULT_TABLESPACE

------------------------------

USERS

USERS

8.In sqlplus confirm that the tablespace was encrypted.

SELECT TABLESPACE_NAME,ENCRYPTED FROM DBA_TABLESPACES where TABLESPACE_NAME = '<tabespace_name>';

If the tablespace is properly encrypted, the commands output will be the following:

TABLESPACE_NAME              ENC

--------------------------------

<tablespace_name>             YES

9.Restart the CCC Service.

service ccc start