Category Archives: security

Oracle 12c – Create the password file

Oracle uses a password file to restrict access to database for users with elevated permissions.   Password file is required for users with the following privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM.   If the password file is lost or corrupted, it must be recreated with the utility ORAPWD.    Syntax of ORAPWD is:

ORAPWD File=<filename> [ENTRIES=number_users] [FORCE={Y/N}] [IGNORECASE={Y/N}]

Command options for ORAPWD are:

  • FILE – Name of the file, can include complete directory or file is written in current directory.
  • ENTRIES – Max number of entries (user accounts) to add. This is users with any of the privileges of SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
  • FORCE – if Y command will overwrite existing password file.
  • IGNORECASE – If Y password will not be case sensitive.
  • FORMAT – When set to 12 which is the default, will create file in 12c format. 12C format is required for SYSBACKUP, SYSDG, SYSKM admin privilege support.  IF set to less, then file will only support for SYSDBA and SYSOPER privileges.
  • SYSBACKUP – If Y creates a SYSBACKUP entry in password file.
  • SYSDG – If Y creates a SYSDG entry in password file.
  • SYSDM – If Y creates a SYSKM entry in password file.


example of use of password file creation for a database with ORACLE_SID of db1:

orapwd File=orapwdb1 Entries=10

The initialization parameter REMOTE_LOGIN_PASSWORDFILE controls the connection to the password file with the following options:

  • NONE – Database will not recognize any password file.
  • EXCLUSIVE – Password file can be used with only one database. It allows you to add multiple users to the database of which any can have one of the privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM.   This is the default.
  • SHARED – Password file can be used by multiple databases running on the same server or multiple instances in a RAC database. Shared password files are read-only and cannot be modified.  All users needing access must be must be added while the REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, then change the init parameter back to SHARED.


Larry Catt


Oracle 12c – Develop and implement a security policy

Oracle provides excellent protection against data loss from equipment failure, accident, or malicious damage.   Administrator should focus on stopping someone from gaining access via stealing passwords, hacking accounts, or accessing restricted data elements.

Security Policy should:

  1. Reduce possibility of unauthorized access to database.
  2. Reduce damage that can be caused by user-level access.
  3. Detect illicit access to the database and restrict data access/damage


Three primary areas to address in Security are:

  1. User Accounts
  2. Privileges and roles given to user
  3. Auditing



Users must authenticate to the database before access is granted.  Most common method is username/password combination.  Since 11g, passwords have been case sensitive.  12c provides two new password complexity functions:

  • ORA12C_VERIFY_FUNCTION (default for 12c using DBCA)

Either function can be modified to increase or customize password complexity.  If you create database with CREATE DATABASE command, no password function is used unless you execute the script.


Privileges and Roles:

Best practice is to provide least privilege and roles required for a user account to do their job.

Privileges can be granted:

  • Explicit – Privilege granted directly to user
  • Role-based – Privilege granted to a role then the role is granted to one or more users.

Role-based privileges is easy to administer because you classify users into groups.  Create a role based on the group and assign members of that group that particular role.



Allows you to monitor and record the actions of user and administrators.  In 12c you can use unified audit policies, DBMS_FA package for fine-grained auditing policies.   Auditing allows you to determine if malicious activity is in the database.



Larry Catt





Oracle 12c – Implement column and tablespace encryption

Transparent Data Encryption (TDE) is used for both column and tablespace encryption.    It uses a software key store which is a container that stores the TDE master encryption key.   There exist one TDE master encryption key per database and the location is defined in the SQLNET.ora file.    In previous releases this key store was referred to as an Oracle Wallet.   Software key store must be open before you can create an encrypted column or tablespace and before you can store or retrieve encrypted data.  Once you open, it is open to all sessions until it is explicitly close it or database shutdown.  Transparent Data Encryption is designed to protect data stored on a disk or other media and protects data from access by other means outside of the database.  It protects backups on tape and from accessing data via the OS directly to datafiles.  TDE supports the encryption types:  3DES168, AES128, AES192, AES256.  You specify the encryption algorithm when creating encrypted column or tablespace.  By default TDE used AES192.   TDE adds a salt to plaintext before encrypt to increase security.   TDE also uses Message Authentication Code (MAC) to data for integrity checking and uses SHA-1 by default.


Column Encryption

Columns are encrypted and decrypted at the SQL layer and any service that bypasses the SQL layer cannot use TDE column encryption.  TDE column encryption cannot be used with the following features:

  1. Index other than B-TREE
  2. Range scan through indexes.
  3. Synchronous change data capture.
  4. Transportable tablespaces.
  5. Columns used in foreign key constraints.

BUT you can encrypt these columns using DBMS_CRYPTO package.  You can encrypt the following datatypes: Note some size restrictions exist.

  3. CHAR
  4. DATA
  7. NCHAR
  10. RAW
  12. VARCHAR2

Example of encrypted column with AES192:



ssn number,

fname varchar(100),

lname varchar(100),

salary  number encrypt);


Never salt a column that is an index and being encrypted.  Example:



ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt);


Change encrypt type by the USING clause. Example



ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt USING ‘3DES168’);


You can use the ALTER TABLE ADD to add an encrypted column to existing table.  You can use the ALTER TABLE MODIFY to make an existing column encrypted.



Tablespace Encryption

You can encrypt a permanent tablespace to protect the data.  This encrypts all data blocks of any segment type.  Data of an encrypted tablespace is also encrypted in undo, redo and all temp space.   There is no overhead in encrypting tablespace.  Restricting of encrypting tablespaces:

  1. You cannot encrypt an existing tablespace.
  2. Encrypted tablespace cannot be moved to another database.
  3. You must open key store after mount and before open to recover an encrypted tablespace.


Creation of encrypted tablespace example



DATAFILE ‘/u01/oradata/data_secret01.dbf’ size 1g





Larry Catt


Fixing access denied error from Linux Server

You attempt to access a remote Linux machine and receive the following error after logon attempt with correct username/password combination.  This indicates that the machine is not accepting password authentication as an access method.   To resolve this issue, perform the following change to sshd_config file as follows:




login as: root

root@’s password:

Access denied

root@’s password:



Steps to resolve this issue:

  1. Logon directly to Linux console as the root user.
  2. Change directory to /etc/ssh

[root@oel1 ~]# cd /etc/ssh

  1. Open the file sshd_config

[root@oel1 ssh]# vi sshd_config

  1. Change the line <PasswordAuthentication no> to read <PasswordAuthentication yes>
  2. Stop the sshd service

[root@oel1 ssh]# service sshd stop

Stopping sshd:                                            [  OK  ]

[root@oel1 ssh]#

  1. Start the sshd service

[root@oel1 ssh]# service sshd start

Starting sshd:                                            [  OK  ]

[root@oel1 ssh]#

  1. Attempt to connect with username / password combination.

[root@oel1 ssh]# ssh

The authenticity of host ‘ (’ can’t be established.

RSA key fingerprint is 74:e4:db:67:e9:7e:81:6f:dc:16:1d:06:25:7e:20:ae.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘’ (RSA) to the list of known hosts.


Last login: Thu Oct  3 11:09:46 2013 from

[root@oel1 ~]#


You have successfully logon with username and password.


Larry Catt

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle provides the procedure UTL_FILE to allow for the reading of external OS layer files from within the Oracle RDBMS. This article will give an example of how to read the contents of an external OS file and display it in the Oracle database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called read.txt with the following text.

This is line one.

3. Logon to SQL*PLUS with an account which has DBA privileges. In this example we are using the account LJCATT.

mylinux:> sqlplus ljcatt

SQL*Plus: Release – Production on Thu Jan 29 23:28:23 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


4. Create the procedure read_file as detailed below.

create or replace procedure read_file

v_record varchar2(50);
v_file varchar2(30) := ‘read.txt’;
v_dir varchar2(512) := ‘/home/lufsora’;
v_write utl_file.file_type;

v_write:=utl_file.fopen(v_dir, v_file, ‘r’, 2000);

utl_file.get_line(v_write,v_record, v_record);



end read_file;

5. Execute the procedure read_file with the command execute and the test within the OS file will be display.

SQL> execute read_file;
This is line one.

PL/SQL procedure successfully completed.


This completes the use of UTL_FILE to read OS level files into your Oracle RDBMS.

Larry J. Catt, OCP9i, 10g