El blog de Nelson Javier Martinez Acosta: enero 2011

http://www.oracle.com/technetwork/articles/index-087388.html
http://www.oracle.com/technetwork/articles/idm/index.html
http://www.oracle.com/technetwork/articles/project-lockdown-133916.pdf

Proyecto de bloqueo de seguridad

Imagine que usted acaba de aterrizar un nuevo trabajo como DBA. En su primer día, se oye rumores de una inminente auditoría de seguridad impulsado por los requisitos de Sarbanes-Oxley. Es necesario conocer el entorno lo más pronto posible, así que usted sabe dónde mirar y entender el momento de tomar medidas inmediatas o preventivas

O imaginar una situación menos grave (aún siendo alarmante) en la que ha "heredado" de base de datos y el servidor que usted sabe que nunca se han endurecido, y una auditoría en el horizonte. Tienes que hacer algo rápidamente para asegurar, y no hay nadie a quien recurrir. Estás por tu cuenta.
O, tal vez usted es un experimentado DBA y se han cuidado de una base de datos por un tiempo. N de auditoría es inminente, pero le preocupa la seguridad en general y quieres ser firme al respecto.
Independientemente de la situación específica, usted puede hacer con seguridad tres supuestos:

1. Usted tendrá que trabajar con rapidez. Sea o no una auditoría es inminente, no puede permitirse el lujo de salir de su entorno en un estado sin garantía para cualquier cosa menos un período corto de tiempo (si acaso).
2. Usted tendrá que trabajar con cuidado y metódicamente, porque está modificando la base de datos de producción.
3. Usted tendrá que trabajar en este proyecto en el desempeño de otras actividades de rutina, el cuidado de la base de datos, lucha contra incendios, manejo de clientes afectados, y así sucesivamente.
Con base en estos supuestos, claramente se necesita un enfoque por fases para asegurar su infraestructura de base de datos, y que hace uso de la tecnología de Oracle actualmente a su disposición. En esta serie, usted recibirá un anteproyecto de dicho plan. Yo lo llamo proyecto de bloqueo de seguridad.
Escuchar
Leer fonéticamente
Diccionario - Ver diccionario detallado

Este proyecto está dividido en cuatro fases distintas, cada una de las cuales se pueden lograr y obtener mejoras medibles en un período concreto de tiempo: un día, una semana, un mes, y una cuarta parte:

Fase 1 (Duración: un día)

Fase 2 (Duración: Una semana)

Fase 3 (Duración: Un mes)

Fase 4 (Duración: una cuarta parte)

(Estas duraciones son sólo estimaciones, dependiendo de su instalación, es posible que tenga más o menos de la cantidad prescrita de tiempo.) En cada etapa usted aprenderá específicamente lo que tiene que hacer a través de ejemplos de código, ejemplos y listas de tareas.

Antes de comenzar este proyecto, le sugiero que lea la cartilla de seguridad brevemente a continuación los términos y conceptos comunes.

Diccionario - detalladoBecause Ver Diccionario estas actividades varían mucho según la versión de Oracle, sólo las actividades pertinentes a las versiones de Oracle 9.2.0.x (base de datos Oracle9i Release 2) a través de 11.2.x (Oracle Database 11g Release 2) serán discutidos. Siempre que sea posible y adecuado, las diferencias obvias en los sistemas operativos se abordarán.
Como siempre, el contenido proveído aquí es para propósitos educativos solamente y no es validada por Oracle, el uso es bajo su propio riesgo! En ningún caso se debe considerar que sea parte de una consultoría o la oferta de servicios.
Una Guía Breve de Seguridad

En primer lugar, es importante entender lo que la "seguridad" se entiende en el contexto de este proyecto.

de seguridad de base de datos es similar a un techo de un edificio. El techo está sostenido por cuatro pilares de autenticación, autorización, control y rendición de cuentas con cada uno de los pilares que contribuyen al principio básico de la seguridad de una manera diferente.

Autenticación significa establecer la identidad de un usuario que confirme que es lo que él o ella dice ser. Si la autenticación es débil, un intruso puede entrar en la infraestructura. Cuando el usuario se autentica, que le permiten ver sólo los datos que está "autorizado" para ver.

Autorización implica el control sobre lo que el usuario puede o no puede ver después de que se autentica, en esta etapa, la identidad no es cuestionada. ("Autenticación" y "autorización" a menudo se confunden o se utilizan indistintamente, lo cual es incorrecto.) Básicamente, la autorización se establece el perímetro de acceso del usuario dentro de la base de datos, y si ese perímetro es porosa, un usuario legítimo podrá ver una mucho más de lo que debería

Como un usuario legítimo, debidamente autenticada actuando dentro de los límites de la autorización manipula los datos, los datos se mueven dentro y fuera de la protección "techo" de la seguridad de base de datos. Para garantizar que los datos no está contaminado o ilegalmente manipulada durante este proceso, la garantía entra en juego.

Por último, la seguridad es incompleta sin que el usuario tener que rendir cuentas por sus acciones. Sin ella, cualquier persona que potencialmente puede hacer algo que es perfectamente legal, pero puede afectar a la estabilidad general del sistema. Por lo tanto, la seguridad se refiere no sólo a permitir a los usuarios legítimos de trabajar bajo un régimen de autorización, sino también con lo que están haciendo y por qué. Por lo tanto, el concepto de rendición de cuentas es el último pilar de la infraestructura de seguridad.

Los cuatro pilares son necesarios para mantener el techo en su lugar. Si alguno de ellos se derrumba, el techo se derrumba por completo. Usted debe construir y reforzar los cuatro pilares para crear una estructura de seguridad razonable, una debilidad individual en esta arquitectura hará que toda su infraestructura vulnerables.

Un enfoque por capas
Aquí hay otra manera de ver la seguridad. Suponga que usted está enviando una pieza muy valiosa y delicada del tema en un soporte comercial regular. No basta con meterlo dentro de un sobre y colóquelo en el buzón o el recipiente de la compañía? No es probable. Más bien, es probable que lo envuelven en plástico de burbujas o el periódico lo puso en una caja protectora. (Dependiendo de la naturaleza del artículo, es posible que desee agregar un contenedor hermético también.) Ahora tiene capas de protección alrededor del objeto de valor, con cada capa realiza una función específica. Si alguien tiene la intención de recibir el artículo, tiene que quitar (o penetrar) estas capas, una por una, hasta que alcanza el contenido del paquete.

de seguridad de base de datos funciona de forma similar, que hay niveles de seguridad alrededor de sus datos, con cada capa de la protección de un área específica. Por ejemplo, su organización puede tener un servidor de seguridad alrededor de la red de la empresa para evitar ataques externos. Dentro de la organización, usted puede tener un segundo servidor de seguridad alrededor de los servidores de base de datos, con los puertos abiertos sólo para los servidores de aplicaciones. Después de estas capas estén perforadas, el intruso debe superar un tercer obstáculo: la autenticación (es decir, lo que demuestra que es un usuario legítimo). Si el intruso consigue más allá de esa capa a través de robo de contraseñas, por ejemplo, todavía está restringido a lo que ella puede ver por la capa de la autorización correcta, con el que podrás proteger los componentes clave, tales como tablas que contienen información sensible. Por último, si no todas estas capas y el intruso obtiene acceso a la "sancta sanctorum", tiene una última línea de defensa: disfrazar los datos (encriptación).

Los riesgos
¿Este modelo de trabajo así? En la mayoría de los casos, sí, pero hay excepciones. Por ejemplo, ¿qué pasaría si el robo se originan desde el interior de su organización (de hecho, la mayoría de los robos lo hacen), o si lo copias de seguridad de datos se pierde o es robada?

El primer caso muestra que si bien es relativamente fácil construir un firewall, si el intruso ya está dentro de él, el objetivo de un firewall es discutible. Como conocedores como los empleados curiosos o descontentos son a menudo ya debidamente autenticada-que son los usuarios legítimos-la capa siguiente, control de acceso, debe ser sólida. Debe comprobar que sólo los privilegios necesarios para el trabajo se conceden, y nada más. El principio de "privilegios mínimos", en oposición a los privilegios redundantes para realizar una tarea, es un requisito aquí.

El segundo caso es aún más aterrador. Incluso si usted ha diseñado la base de datos como una fortaleza, hay al menos una ocasión en que los datos de las hojas que la fortaleza: para el almacenamiento de copia de seguridad. Muchas organizaciones de enviar cintas de copia de seguridad en una ubicación remota para protegerlos de los desastres locales, tales como incendios, inundaciones, etc; en muchos casos, esta función a distancia es una empresa diferente especializada en ese negocio. Sin la seguridad adecuada, las cintas pueden ser robados (o simplemente perdidos) de la propia instalación o en el camino a la misma. Un intruso que obtenga estas cintas teóricamente pueda restaurar un servidor que tiene acceso a los datos y navegar a su gusto. Más temible de todos, en algunos casos, las cintas pueden ser devueltos con seguridad a las instalaciones de almacenamiento sin la violación cada vez se están descubriendo.

Estos dos riesgos se puede tirar una llave inglesa en la seguridad de su cuidadosamente planificada. Sin embargo, es posible para proteger la base de datos, incluso de estos dos eventos a través de una combinación de lo siguiente:
• Un esquema de autenticación fiable, si un usuario es SCOTT, no debe haber ninguna duda de que él es, en efecto SCOTT, no MARTIN
• Prevención de la suplantación
• Prevención de escuchas por parte de usuarios no autorizados
• Protección de datos "en reposo" de la modificación ilegal
• Auditoría de las actividades del usuario
... Que es lo que usted aprenderá a aplicar en el Proyecto de bloqueo de seguridad. Vamos a empezar!

Arup Nanda, un Oracle ACE Director, ha sido un DBA de Oracle durante más de 12 años, manejo de todos los aspectos de administración de base de datos de seguridad de la optimización del rendimiento y recuperación de desastres. Es coautor de PL / SQL para el DBA (O'Reilly Media, 2005). Fue DBA Oracle Magazine del Año en 2003.
Descargas más populares
Berkeley DB
Enterprise Manager
Base de datos de EE y XE
Enterprise Pack para Eclipse
Fusion Middleware
Java EE y GlassFish
Java SE
JDeveloper y ADF
MySQL
NetBeans IDE
Pre-construidos para desarrolladores máquinas virtuales
Solaris 10 y 11 Express
SQL Developer
VM VirtualBox
Zend Server para PHP

Serie: Lockdown Proyecto

Un enfoque gradual para asegurar su infraestructura de base de datos

Un enfoque gradual para asegurar su infraestructura de base de datos

Phase 1
Duration: One Day

It's Phase 1 of your security and compliance project. Let's see what you can do within 24 hours to lock-down your infrastructure.

Updated August 2010

Download: Phase 1 Checklist (PDF)

Covered in this Installment:
• 1.1 Remove Default Passwords
• 1.2 Remove Case-sensitive Passwords
• 1.3 Configure Oracle Binary Permissions
• 1.4 Secure Other Executables
• 1.5 Change DBSNMP Password
• 1.6 Limit SYSDBA Login
• 1.7 Create a Listener Password
• 1.8 Protect the Listener
• 1.9 Trim Sweeping Privileges
• 1.10 Move Audit Trail to a Different Tablespace

1.1 Borrar contraseñas por defecto

Antecedentes
Durante la instalación del software de Oracle y la creación de bases de datos, es común para las cuentas que se crean y luego olvidado. Estas cuentas, que suelen llevar las contraseñas por defecto (como "tigre" de Scott), se favorecen los puntos de entrada a los intrusos. Usted se sorprendería de escuchar la cantidad de producción de base de datos de instalaciones He comprobado que el uso change_on_install u Oracle como la contraseña para SYS. Su primera línea de acción debe ser identificar y eliminar inmediatamente las contraseñas por defecto.
Estrategia
En Oracle Database 11g, esta actividad se ha convertido en muy fácil, casi al punto de ser trivial. La base de datos tiene una visión especial, dba_users_with_defpwd, que enumera los nombres de usuario con las contraseñas por defecto. He aquí un ejemplo de uso

SQL> select * from dba_users_with_defpwd
2 /

USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
CTXSYS
OLAPSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
… output truncated …

The output clearly shows the usernames that have the default password. You can join this view with DBA_USERS to check on the status of the users:

1 select d.username, account_status
2 from dba_users_with_defpwd d, dba_users u
3* where u.username = d.username
SQL> /

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
PM EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
BI EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
OWBSYS EXPIRED & LOCKED
XS$NULL EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
APPQOSSYS EXPIRED & LOCKED
… output truncated …

En versiones anteriores de Oracle Database 11g, ¿cómo identificar las cuentas con contraseñas por defecto? Una opción es tratar de acceder a la cuenta mediante la contraseña por defecto, pero este es definitivamente un método engorroso, por no mencionar un tiempo.

Afortunadamente, hay una opción más elegante. Echa un vistazo a la columna de la contraseña en la vista DBA_USERS:

SQL> select username, password
2 from dba_users
3 where username = 'SCOTT';
USERNAME PASSWORD
------------------------------ ------------------
SCOTT F894844C34402B67

La contraseña es hash y lo indescifrable, pero sabemos que la contraseña de Scott es tigre. Por lo tanto, el valor hash de tigre cuando identificador de usuario es SCOTT es F894844C34402B67. Ahora bien, si los cambios de contraseña de Scott, este valor hash también cambia. Puede volver a comprobar en el DBA_USERS vista para ver si la contraseña de Scott coincide con este valor hash, que verificará la contraseña como el tigre. Nótese, sin embargo, que el valor hash no es un valor hash de la contraseña misma, si otro usuario tiene la contraseña de tigre, que el valor hash será diferente

SQL> create user scott2 identified by tiger;

User created.

SQL> select username, password
2 from dba_users
3 where username = 'SCOTT2';

USERNAME PASSWORD
------------------------------ --------------------
SCOTT2 C44C11D4C34DB67D

Tenga en cuenta el valor hash diferentes (C44C11D4C34DB67D), a pesar de que la contraseña es el mismo.
Entonces, ¿cómo se puede utilizar esta información? Es muy sencillo. Si crea los usuarios por defecto con contraseñas por defecto, se llega a conocer los valores hash de las contraseñas. A continuación, puede crear una tabla de las cuentas y los valores hash de las contraseñas por defecto y compararlos contra los hashes de contraseñas almacenadas en el diccionario de datos.
En enero de 2006, Oracle hizo una utilidad de descarga disponibles para la identificación de contraseñas por defecto y los usuarios. Esta herramienta, disponible a través de un parche 4926128 está disponible en OracleMetaLink como se describe en el documento de identidad 361.482,1. Al escribir estas líneas, la utilidad de los controles de un puñado de cuentas por defecto de forma similar a la descrita anteriormente, por el momento de leer esto, sin embargo, su funcionalidad y puede haber aumentado.

Por otra parte, el experto en seguridad Pete Finnigan ha hecho un excelente trabajo de recopilación
todas las cuentas creadas por defecto como en diversas instalaciones de Oracle y de terceros, que ha expuesto para el uso público en su página web, petefinnigan.com. (descargo de responsabilidad:. Oracle no valida el contenido de los sitios web de terceros) En vez de reinventar la rueda, vamos a utilizar el trabajo de Finnigan y le doy las gracias profusamente. He cambiado su enfoque un poco más, sin embargo.
En primer lugar, crear la tabla para almacenar las cuentas por defecto y la contraseña por defecto:

CREATE TABLE osp_accounts
(
product VARCHAR2(30),
security_level NUMBER(1),
username VARCHAR2(30),
password VARCHAR2(30),
hash_value VARCHAR2(30),
commentary VARCHAR2(200)
)

Then you can load the table using data collected by Finnigan. (Download the script at petefinnigan.com/default/osp_accounts_public.zip.)[ After the table is loaded, you are ready to search for default passwords. I use a very simple SQL statement to find out the users:

col password format a20
col account_status format a20
col username format a15
select o.username, o.password, d.account_status
from dba_users d, osp_accounts o
where o.hash_value = d.password
/

USERNAME PASSWORD ACCOUNT_STATUS
--------------- -------------------- --------------------
CTXSYS CHANGE_ON_INSTALL OPEN
OLAPSYS MANAGER OPEN
DIP DIP EXPIRED & LOCKED
DMSYS DMSYS OPEN
EXFSYS EXFSYS EXPIRED & LOCKED
SYSTEM ORACLE OPEN
WMSYS WMSYS EXPIRED & LOCKED
XDB CHANGE_ON_INSTALL EXPIRED & LOCKED
OUTLN OUTLN OPEN
SCOTT TIGER OPEN
SYS ORACLE OPEN

Aquí puedes ver algunos de los más vulnerables de las situaciones, especialmente la última línea, donde el nombre de usuario es SYS y la contraseña es oráculo (como es la de SYSTEM)! Puede que no sea change_on_install, pero es igual de predecibles.
La vulnerabilidad varía según las versiones. En Oracle Database 10g y más tarde, la instalación de base de datos tiene un mensaje que le pregunta cuál es la contraseña debe ser, en lugar de asumir que sea change_on_install o alguna otra cosa. Debido a que el usuario se ve obligado a tomar una decisión, es probable que la contraseña será un no por omisión. Sin embargo, si el usuario opta por algo tan predecible como oráculo, entonces el punto es discutible. (Tal vez oráculo fue elegido cuando la base de datos se estaba construyendo antes de la producción como una conveniencia para el DBA. Después se fue a la producción, la contraseña pegado alrededor.)
En versiones anteriores de Oracle Database 10g, la contraseña no se le pide que se introducirán, y por lo tanto es probable que la contraseña por defecto, por ejemplo, change_on_install para SYS y director de EL SISTEMA está activo. Esta herramienta le ayudará a identificar estos casos.
También tenga en cuenta que el identificador de usuario SCOTT-la cuenta de demostración para el aprendizaje de técnicas de SQL puede estar bien para una base de datos de desarrollo, pero no para una producción de uno. Se trata de una posible entrada por la puerta trasera para los intrusos, y de inmediato se le caiga.
Cuentas como CTXSYS, DMSYS y OLAPSYS se requieren para las herramientas de Oracle. La mejor estrategia es dejar caer estos usuarios si no está utilizando estas opciones. Si no está seguro de que los están utilizando, o simplemente quiere reservar la oportunidad, usted puede mantener estas cuentas, pero cierra a las conexiones. Para bloquear una cuenta y la contraseña expire, deberá expedir el presente SQL

alter user dmsys account lock expire password;

which will set the account status to EXPIRED & LOCKED. When the user tries to log in, the following error will be raised:
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

Change the password for all accounts that you cannot lock. One such account is DBNSMP, but we’ll discuss that later.

Implications
The locking of unused accounts shouldn’t cause any problems.
Action Plan
Identify the unused accounts. Lock them and expire their passwords.
For the used accounts, change the password if the default password is being used.

1.2 Use Case-Sensitive Passwords

Antecedentes
Las contraseñas en Oracle Database históricamente han sido mayúsculas y minúsculas, es decir, el abc123 contraseñas y abc123 "fueron considerados idénticos. Esto llevó a la consternación en algunos círculos. Muchas regulaciones y mandatos de seguridad requieren que las contraseñas se de mayúsculas y minúsculas (por lo menos una letra mayúscula).
Desde Oracle Database 11g Release 1, las contraseñas distinguen mayúsculas y minúsculas están disponibles. Por lo tanto, abc123 y abc123 no se consideran lo mismo. Si el usuario establece la contraseña como abc123, abc123 entonces no funcionará. Aquí hay una demostración

SQL> alter user sh identified by Abc123;
User altered.

Here’s what happens if you try to connect with abc123:
SQL> conn sh/abc123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

The password is not accepted. Now let’s try the actual mixed-case password:
SQL> conn sh/Abc123
Connected.

Un sec_case_sensitive_logon parámetro de base de datos controla el comportamiento. Si se establece en TRUE, la base de datos Oracle diferencia entre abc123 y abc123. Si se establece en FALSE ignora la sensibilidad del caso, volviendo a la pre-enfoque de Oracle Database 11g. He aquí una demostración de si se establece en false:

SQL> alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn sh/abc123
Connected.
SQL> conn sh/Abc123
Connected.

En caso de que establezca el valor VERDADERO o FALSO? Esa es una pregunta importante. En Oracle 11g Release 1, si ha utilizado DBCA para crear la base de datos, se le preguntó si quería mantener la base de datos Oracle mayúsculas y minúsculas contraseña 10g de estilo o aplicar la nueva seguridad de Oracle Database 11g estilo. Si acepta el valor por defecto, en otras palabras, Oracle Database 11g-estilo-el parámetro se establece en TRUE y las contraseñas se revisaron para el caso. En Oracle 11g Release 2, DBCA no pide, sino que establece los valores por defecto.

Supongamos que la contraseña de un usuario llamado ARUP es dream1ng. Lo más probable es que el usuario introduce la contraseña en SQL * Plus, SQL Developer, y así sucesivamente como dream1ng. Sin embargo, algunas herramientas pueden convertir a mayúsculas haciendo el DREAM1NG contraseña. Cuando la contraseña se pasa a la base de datos, la autenticación se negó. Esta elección tendrá lugar inmediatamente después de la actualización de base de datos, por lo que es probablemente la razón se atribuye a un error en Oracle Database 11g o algo similar esotérico.
A menudo, los usuarios también cometen errores. Debido a que en la pre-base de datos Oracle 11g que no estaban obligados a diferenciar entre mayúsculas y minúsculas contraseñas, que podrán seguir introducir sus claves en cualquier caso que se sientan cómodos, a falta de autenticación.

De cualquier manera, tal vez no sea posible cambiar el código rápidamente para aprobar la contraseña tal cual o el cambio de comportamiento del usuario. Por lo tanto usted debe tomar un enfoque por fases para cambiar este comportamiento en particular, de lo contrario, puede causar cortes de aplicación.
Si los usuarios conectarse a una base de datos Oracle Database 11g través de un enlace db, que ahora debe utilizar la contraseña en caso de derecho de lo contrario, serán rechazadas.

Plan de Acción

Si la base de datos no está sujeta a una condición que requiere contraseñas distinguen mayúsculas y minúsculas, es posible que no se preocupan por él. Sin embargo, siempre es una buena idea hacer caso de las contraseñas sensibles por lo que la base de datos se cumple con la normativa aún por venir. Además, hace que las contraseñas más seguras.
Si se trata de una base de datos de nueva construcción, debe activar la función de mayúsculas y minúsculas (deje el valor predeterminado). Asegúrese de que los usuarios saben acerca de esta característica para que se tenga cuidado en introducir la contraseña en caso de derecho.
Si está actualizando la base de datos de Oracle Database 10g, usted debe ser un poco poco más flexible. Siga los pasos que se muestran a continuación:
Asegúrese de que el parámetro de inicialización AUDIT_TRAIL se establece en la base de datos db antes de que se reinicie:

audit_trail = db

Esto se convierte en la pista de auditoría. (base de datos Oracle 11g ya tiene este parámetro por defecto.)

Asegúrese de que tiene activada la auditoría período de sesiones. Ejecute el siguiente comando:

SQL> audit session;

Permite a auditoría por parte de la sesión. (base de datos Oracle 11g ya tiene este parámetro por defecto, así que si usted no deshabilita explícitamente, el que está.)
En lugar de convertir la función de mayúsculas y minúsculas de forma inmediata, se descarta el parámetro de forma explícita en el archivo de parámetros después de actualizar la base de datos:

sec_case_sensitive_logon = false

Esto restablece el comportamiento de Oracle Database 10g, es decir, permite que la base de datos ignorar el caso de las contraseñas.
Realiza tus usuarios conscientes de la función de mayúsculas y minúsculas y animarles a introducir las contraseñas en el caso apropiado. Nota: Debido al parámetro de ser falso, aún pueden introducir las contraseñas en cualquier caso y ser autenticado.
Que las solicitudes pasan las contraseñas en el caso apropiado. Las contraseñas seguirán siendo autenticado si no lo hacen, pero por lo menos las solicitudes estará listo. Durante una ventana de cambio, la edición de esta declaración:
Escuchar

SQL> alter system sec_case_sensitive_logon = true;

Esto permite a mayúsculas y minúsculas en las contraseñas. Inmediatamente después, comprobar si los intentos de acceso no válida en la pista de auditoría:
Escuchar

select username, userhost, terminal
from dba_audit_trail
where returncode = 1017;

Si algún usuario no autenticación, que habrían recibido un mensaje de error "ORA-01017: nombre de usuario válido y contraseña, inicio de sesión denegado" y este hecho se han registrado en la pista de auditoría con el valor de código de retorno de 1017. La consulta anterior se han puesto de manifiesto los intentos de acceso no válido.
Si usted ve cualquier registro, de la acogida y la terminal, se puede determinar el origen de estos inicios de sesión, lo que le permite tomar medidas correctivas

1.3 Configure Oracle Binary Permissions

Background

Oracle Database utiliza varios archivos binarios. El más importante es el oráculo ejecutable en UNIX y Linux y sabores oracle.exe en Windows.
Tenga en cuenta el permiso de estos archivos. Por ejemplo, en UNIX, es posible ver algo como esto

# cd $ORACLE_HOME/bin
# ls -l oracle

-rwsr-s--x 1 oracle oinstall 69344968 Jun 10 14:05 oracle

Los permisos (lo mismo en todas las versiones de Oracle pertinentes) son el valor predeterminado. Vamos a ver lo que significan. (Si está familiarizado con los permisos de UNIX, puede saltarse esta subsección y continuar con la subsección "dos tareas Arquitectura.")
La primera posición indica el tipo de archivo. En UNIX, los archivos de regular todo, directorios y dispositivos, se considera un archivo. Se trata de un archivo real, por lo tanto, la primera posición aparece "-." Si hubiera sido un directorio, esta posición se han mostrado "d", en el caso de un dispositivo especial de caracteres, que se han mostrado "c", y así sucesivamente

La segunda posición en adelante se muestran los permisos figuran en el archivo. Los permisos se muestran en bloques de tres, lo que indica el estado de la lectura, escritura y ejecución, respectivamente. Las tres primeras posiciones muestran los permisos para el propietario, los próximos tres mostrar los permisos otorgados al grupo pertenece el archivo, y los tres últimos muestran los permisos proporcionados a todos los demás.

Position 1 2 3 4 5 6 7 8 9 10
Value - r w s r - s - - x
Owner Group Other

En cada conjunto de permisos, los permisos se muestran como un valor o "-." Si "-" aparece en el lugar, indica que el permiso no se concede ese privilegio. Por ejemplo, en el caso anterior, tenga en cuenta la sexta posición, lo que indica que el permiso de escritura para el Grupo está en "-", que indica que el grupo "dba" (el grupo pertenece el archivo) no puede escribir en este archivo. Si el permiso es concedido, entonces el valor se establece en la letra correspondiente. Una vez más, en el ejemplo anterior, el permiso de lectura para el grupo (indicado por la quinta posición) muestra la "r", indicando que el grupo "dba" se puede leer este archivo.
Tenga en cuenta las últimas tres posiciones, que indican los permisos para los Otros (no el propietario, el oráculo, o los usuarios que pertenecen al grupo dba). De los permisos, se puede ver que otros simplemente se pueden ejecutar este archivo, pero no leer ni escribir en él.
Esto explica la "r", "w" y "x" para leer, escribir y ejecutar, respectivamente, pero ¿qué pasa con el carácter "s" en el lugar donde debería haber habido una "x"? Este es un giro interesante a los privilegios de ejecución. La presencia de este "s" en el permiso anterior indica que este programa es setuid activado. Cuando se ejecuta el programa, independientemente de quién lo ejecuta, se ejecutará como el usuario que posee, oracleThis es decir, es una manera en la que puede ser el programa de propiedad de software de Oracle, pero a cargo de cualquier persona que intente conectarse a él. Así, el programa puede funcionar con los privilegios del oráculo y no el usuario que lo ejecuta, lo que hace posible abrir archivos de base de datos y así sucesivamente.
Dos tareas Arquitectura. Recordemos cómo los procesos de base de datos Oracle operar, al desvincular el proceso de usuario en el proceso de servidor. Si no te acuerdas de este completo, le recomiendo volver a leer los primeros capítulos de la Base de Datos Oracle 10g Manual de Conceptos. A continuación se muestra una versión muy destilada de la interacción, que sólo sienta las bases para la comprensión de los permisos, no es un sustituto para la revisión de los contenidos del manual.
Cuando un usuario se conecta a una base de datos Oracle, por ejemplo, con SQL * Plus de Oracle crea un nuevo proceso de servicio de programas de este usuario. Este nuevo proceso se llama el proceso del servidor de Oracle, que difiere del proceso del usuario (sqlplus, sqlplus.exe, TOAD.exe, o lo que bien puede ser). Este proceso de servidor interactúa con las estructuras de memoria como el Área de Sistema Global (SGA) y lee en los archivos de datos, si los datos no se encuentra en la caché del búfer en el SGA, y así sucesivamente. En ningún caso es un proceso del usuario (sqlplus) permite interactuar directamente con los archivos de datos de base de datos Oracle. Debido a que hay dos procesos (el proceso de usuario y el proceso de servidor) que trabajan en conjunto para hacer el trabajo, esto es a veces conocida como arquitectura de dos tareas. Si un proceso de usuario hace algo potencialmente problemáticos, tales como una violación de la gestión de memoria en el ordenador central, la base de datos Oracle en sí no se ve afectado y el daño se limita al proceso del usuario.
(Tenga en cuenta que lo anterior se aplica a las conexiones de Oracle en un entorno de servidor dedicado. En un entorno de servidor multiproceso, este modelo es un poco diferente en el sentido de que un proceso de servidor solo puede dar servicio a más de un proceso de usuario. Es todavía dos tareas, pero en lugar de una relación 1:1 entre los procesos del servidor y el usuario, es una: muchos).
Los procesos del servidor se ejecutan bajo el usuario que posee el software de Oracle. He aquí un ejemplo. Supongamos que el usuario se registra en la base de datos con SQL * Plus:

$ sqlplus arup/arup

After this, if you search for this process:
$ ps -aef|grep sqlplus

it shows this:
oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus

This, of course, assumes that no other SQL*Plus sessions have been running on the server.
Note the process id (6339). Now if you search that process ID:
$ ps -aef|grep 6339

You will get two processes:
oracle 6339 6185 0 13:06 pts/0 00:00:00 sqlplus
oracle 6340 6339 0 13:06 ? 00:00:00 oracleDBA102
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The first one you’ve seen before (the process for the SQL*Plus session). The second one—process ID 6340—is the server process that Oracle creates for the user. Note the Parent Process ID of the process; it’s 6339, which is the process ID of the SQL*Plus session.

The process name is oracleDBA102 (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))), which tells you several things. First, the presence of the clause LOCAL=YES indicates that this process started due to another process that is running locally, on the same server as the database itself. It also shows PROTOCOL=beq, which means that the connection was made through a bequeath connection.
You can also find the information about the server process from the dynamic views:

select spid
from v$session s, v$process p
where s.sid = (select sid from v$mystat where rownum <2)
and p.addr = s.paddr;

The value returned by the above query is the process ID of the server process. This is the only way to get the process ID if the client process is on a different server, such as someone running SQL*Plus on a laptop connecting to the database.
Now, assume that the user connects through a slightly modified manner. Instead of connecting directly on the server, she uses the TNS string. Assume that your TNS string looks like this (on the server oradba):
DBA102 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradba)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBA102)
)
)

Now the user connects (on the same server, oradba) as follows:
sqlplus arup/arup@dba102

Check the process ID from the dynamic views:
SQL> select spid
2 from v$session s, v$process p
3 where s.sid = (select sid from v$mystat where rownum <2)
4 and p.addr = s.paddr
5 /

SPID
------------
6428

The process ID is 6428. Search for this on the server:
$ ps -aef|grep sqlplus | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

Now when you search for the server process on the database server:
$ ps -aef|grep 6426 | grep -v grep
oracle 6426 6185 0 13:20 pts/0 00:00:00 sqlplus

you don’t see the server process. There is no child process of the user process 6426. But you know from the dynamic performance views that the server process is 6428, so what is the parent process of that?

$ ps -aef|grep 6428 | grep -v grep
oracle 6428 1 0 13:20 ? 00:00:00 oracleDBA102 (LOCAL=NO)

El proceso primario es 1. Pero ¿por qué no es 6426?
Para entender la respuesta, usted tiene que entender cómo los diferentes procesos del servidor de Oracle se crean. En el primer caso, cuando el usuario no utilice un TNS cadena de conexión, la conexión se envían directamente a la base de datos sin tener que ir a la primera escucha. La base de datos creado un proceso de usuario y luego entregó el control del proceso para el dueño del proceso, un proceso conocido como legar, de ahí el proceso legar plazo, que se presentó en el nombre del proceso.
En el segundo caso, cuando el usuario todavía estaba en el mismo servidor, pero conectado a través de la escucha, el oyente creó el proceso para el usuario, que se conoce como bifurcación. Del mismo modo, si el proceso de usuario se ejecuta en un equipo diferente (como un ordenador portátil), la conexión tendría que hacerse para el oyente y el oyente se ha creado el proceso. El proceso fue creado por un servidor remoto, de ahí el nombre del proceso contiene la cláusula LOCAL = NO. Incluso si el período de sesiones de SQL * Plus se ejecuta en el mismo servidor, el hecho de que se trataba de una conexión no legar hizo un no-local.
(Nota: Dependiendo del sistema operativo, no puede ver el identificador de los padres del proceso del servidor de la misma manera que usted ve el SQL * Plus en sesión de conexiones legar En algunos casos, a pesar de que la conexión es legar, el ID de padre. muestran como una consecuencia, no se basan en la identificación de los padres para determinar qué tipo de proceso de servidor que es,.. usar el nombre del proceso en su lugar)
Ahora que usted entiende el modelo de dos tareas, vamos a ver si le da el punto sobresaliente en esta discusión. La base de datos crea y ejecuta el proceso del servidor, no el usuario que inició el proceso de cliente como SQL * Plus. El proceso de servidor utiliza el oráculo ejecutable o oracle.exe, por lo que sólo el propietario del software de Oracle, llamado orasoft (nombre con el fin de evitar la confusión con el término "oráculo", que es el nombre del ejecutable), debe tener privilegios para ejecutar -nadie más. Entonces, ¿por qué necesitan permisos para los demás?
La respuesta corta es que no lo hacen. Usted puede quitar los permisos innecesarios mediante el siguiente comando:

$ chmod 4700 $ORACLE_HOME/bin/oracle

After executing the command, the permissions will look like this:
-rws------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Now we can move on to strategy—via the SUID bit. In this case the SUID bit is set to ON (indicated by rws permissions for the owner).

Strategy
Because you don’t need anyone other than the Oracle software owner (orasoft, in this case) to run the Oracle executable, you should remove the SUID bit from the executable and make it accessible by only the owner—no one else:
$ chmod 0700 $ORACLE_HOME/bin/oracle

The permissions now look like this:
-rwx------ 1 orasoft oinstall 248754168 Oct 8 07:11 oracle

Implications

This is a major change, and it’s important that you understand its impact. When a user (not the Oracle software owner) on the server tries a local connection, the executable oracle is run on his behalf as if the user orasoft is running it. Because the server process will open the datafiles (owned by orasoft), either it must run as orasoft or the user must have permissions to open the datafiles.
For example, suppose the UNIX user ananda logs in to the same server the database is on and connects locally:

$ sqlplus arup/arup

The user will immediately get an error:
ERROR:
ORA-12546: TNS:permission denied

Enter user-name:

La razón es muy simple: se ha quitado el permiso SUID en el oráculo de archivo. Cuando el usuario ejecuta una conexión local, que básicamente trata de correr el oráculo ejecutable, sino porque el SUID no está definida, no es juzgado como orasoft usuario, sino más bien como Ananda. Como ananda usuario no tiene permiso para ejecutar este archivo, no se ejecutará, de ahí el error ORA-12546.
Entonces, ¿cómo puede ananda conectarse a la base de datos? Hay dos opciones. Una de ellas es hacer que todos los procesos de usuario se ejecutan en un servidor distinto del servidor de base de datos propia, por lo tanto no hay ninguna conexión legar a la base de datos, sólo los no locales. Debido a que las conexiones no locales pasan por el proceso de escucha y la escucha genera un proceso de servidor para ellos, el proceso del servidor es propiedad de orasoft (el propietario del software de Oracle) y no por el usuario que está ejecutando el proceso del cliente. No hay permiso para emitir.

Como alternativa, si tiene que ejecutar algunos procesos de usuario en el servidor de base de datos en sí, se puede conectar a través de la escucha con
Escuchar

$ sqlplus arup/arup@dba102

que tiene el mismo efecto que un usuario que se conecta desde fuera del servidor. Ahora sólo el usuario que posee el software de Oracle (en este caso, orasoft) se puede conectar a la base de datos a través de una conexión de lego.
DBA con los distintos identificadores de sistema operativo no será capaz de apagar o poner en marcha la base de datos utilizando el comando connect / as sysdba, incluso si pertenecen al grupo dba. Pueden hacerlo con

$ sqlplus /nolog

SQL> connect sys/Password_of_SYS@dba102 as sysdba

Sí, este enfoque hace uso de la contraseña SYS, pero eso es una mejor práctica en comparación con / as sysdba. Una práctica mucho mejor aún es la creación de identificadores de usuario de Oracle para administradores de bases individuales:
conectar ANANDA / Password_of_ANANDA @ dba102 como sysdba

Un truco favorito de los piratas cibernéticos es entrar en el servidor mediante una cuenta y luego tratar de forzar a la base de datos. (Un típico "puerta floja" es el usuario "nobody"). Incluso si el hacker no entrar en la base de datos, puede crear un ataque de denegación de servicio por desbordamiento de búfer del oráculo ejecutable. Si la capacidad de ejecutar el archivo es eliminado, entonces la efectividad del ataque es muy limitado. Al mismo tiempo, como se vio, no hace desaparecer la funcionalidad de los usuarios legítimos. La mayoría de los usuarios se conectan a la base de datos mediante la escucha de todos modos, y no se verá afectado mucho.

Plan de Acción

A ver si cualquier otro usuario en el sistema hace una conexión de lego. Usted puede lograr esto
• Basta con preguntar
• Búsqueda de procesos en el servidor para ver si encuentra algo tan obvio como
Escuchar

• Checking the column MACHINE of V$SESSION:

select program
from v$session
where machine = '';

If something comes up, you can identify the exact program running by turning on auditing (which you will learn about in the subsequent phases of this article series)[ and capturing any program coming from the server.
Action

IF no programs connect from the server, THEN
Change the permissions of the oracle executable
chmod 0700 $ORACLE_HOME/oracle
ELSIF some program connects from the server
Change the connection from UserID/Password to UserID/Password@Connect_String
END IF
IF you frequently connect from shell scripts as sqlplus / as sysdba THEN
Change it to use DBAUser/Password@Connect_String
END IF

1.4 Secure Other Executables

Antecedentes

Echa un vistazo a los otros ejecutables en el directorio bin $ ORACLE_HOME /, y algunos pueden parecer familiares, como sqlplus o lsnrctl (la utilidad para iniciar el oyente), mientras que otros no pueden.
Algunos de estos archivos, como tnslsnr, la utilidad que se ejecuta el proceso de escucha, o dbsnmp, que fue utilizado en Oracle Intelligent Agent-no son afectados directamente por el usuario final. Para fijarlos correctamente, usted debe entender lo que hacen y tomar las medidas oportunas.
Recordemos que si el bit SUID se establece para un archivo, a continuación, independientemente de quién ejecuta el archivo que se ejecuta con los privilegios del propietario, no el ejecutor. También ha aprendido que la fijación de la SUID puede ser peligroso y debe ser desalentado.
Hay varios otros archivos que tienen el bit SUID activado. Vamos a encontrar.

$ cd $ORACLE_HOME
$ find . -type f $ -perm -2000 -o -perm -4000 $ -exec ls -l {} \;

In Oracle Database 10g Release 1 and later, the above should return only the following executables (Oracle Database 11g should return only a subset of this):
-rwsr-s--x 1 orasoft dba 93300507 Jul 22 11:20 ./bin/oracleO
-r-sr-s--- 1 root dba 0 Jul 1 23:15 ./bin/oradism
-rwsr-s--x 1 orasoft dba 94492 Jul 22 11:22 ./bin/emtgtctl2
-rwsr-s--- 1 root dba 18944 Jul 22 11:22 ./bin/nmb
-rwsr-s--- 1 root dba 20110 Jul 22 11:22 ./bin/nmo
-r-sr-sr-x 1 nobody nobody 58302 Jul 22 11:23 ./bin/extjob

Let’s see what these files are:
Program Description
./bin/oracle This file is a copy of the executable oracle. When you recompile the oracle executable using the relink command, the old copy is saved as oracleO. This is a potential security hazard; most DBAs ignore it, and it can be an avenue for hackers. Therefore you should take action to remove the permissions. The best option is to have no permissions for it to anyone other than orasoft:

$ chmod 600 oracleO
Now, if you check the permissions:

$ ls -l oracleO
-rw------- 1 orasoft oinstall 248823320 Sep 15 13:27 oracleO
./bin/oradism Used for Dynamic Intimate Shared Memory. May be in use on your platform. May not be present in all cases. If present, leave as is.
./bin/emtgtctl2 Used for Oracle Enterprise Manager agent. There is no need for it to be set with SUID. The justification is the same as the oracle executable. Remove the permissions:

$ chmod 0700 emtgtctl2
./bin/nmb Used for Oracle 10g Grid Control agent to collect statistics on the target server. Leave as is.
./bin/nmo Used for Oracle 10g Grid Control agent to collect statistics on the target server. Leave as is.
./bin/extjob This is the executable for the EXTJOB (External Jobs, which allow you to execute OS-based programs from within Oracle Enterprise Manager). This is something you should be careful about. Do you use external jobs a lot? If not, then you should not even have this executable. In such a case, you can leave it in the directory but change the permissions and the ownership. The owner can be the Oracle software owner (orasoft, in our case), and the permissions should be rwx------:

$ chown orasoft:oinstall extjob
$ chmod 0700 extjob

There may be another program present, extjobO, which was a previous compilation of the same program. Change the permissions of that too:
$ chown orasoft:oinstall extjobO
$ chmod 0600 extjobO

In Oracle9i Database Release 2, you will find a different file, ./bin/dbsnmp, which is the Oracle Intelligent Agent executable file. The permissions are set as such:
-rwsr-s--- 1 root dba 2986836 Jan 26 2005 dbsnmp

The problem with this file is that it needs root privileges to work properly, hence the SUID bit must be set to on. However, because this file is owned by root, hackers typically exploit it to gain access as root. The best advice is to eliminate it, or make it owned by the Oracle software owner and set the permissions to 700. You will lose some functionality, but it’s worth it to eliminate the risk.
The other executable to consider is tnslsnr, which is the Oracle Net Listener. There are two executables:

• tnslsnr—the actual listener executable
• lsnrctl—the utility that is used to manage the listener, such as starting, stopping, and so on

If you look at the permissions:
$ ls -l *lsnr*
-rwxr-x--x 1 orasoft oinstall 214720 Oct 25 01:23 lsnrctl
-rwxr-xr-x 1 orasoft oinstall 214720 Oct 1 18:50 lsnrctl0
-rwxr-x--x 1 orasoft oinstall 1118816 Oct 25 01:23 tnslsnr
-rwxr-xr-x 1 orasoft oinstall 1118816 Oct 1 18:50 tnslsnr0

the files have execute privileges for all. Like the executable oracleO, when a new file tnslsnr is created by relinking the Oracle software, the existing file tnslsnr is renamed to tnslsnr0. This is done because if the process needs to be rolled back, the old executable can be copied over the new one. Because it’s the copy of the old executable, the file tnslsnr0 may contain the same functionality as the original tnslsnr. The same goes for lsnrctl0.
Strategy
Now that you understand the purpose of each executable, let’s see how you can secure your database infrastructure. Most of the strategy has been discussed in the above section on background information. So, in essence, your strategic moves are all these actions
1. Remove all permissions to others from the files that are not needed—for example, lsnrctl0.
2. Restrict permissions for executables to Oracle software only.
3. Remove the SUID bit if the Oracle software owner starts the processes.
So, you want to change the permissions of the listener-related files as follows:
$ chmod 700 lsnrctl tnslsnr lsnrctl0 tnslsnr0
$ chmod 600 lsnrctl0 tnslsnr0

Verify the result:
$ ls -l *lsnr*
-rwx------ 1 orasoft oinstall 214720 Oct 25 01:23 lsnrctl
-rw------- 1 orasoft oinstall 214720 Oct 1 18:50 lsnrctl0
-rwx------ 1 orasoft oinstall 1118816 Oct 25 01:23 tnslsnr
-rw------- 1 orasoft oinstall 1118816 Oct 1 18:50 tnslsnr0

Implicaciones

Hay algunas implicaciones en este caso:
• Cambio de la oracleO ejecutable no tiene ningún impacto sobre el funcionamiento de la base de datos. Si alguna vez se enfrentan a un problema que apunta a un oráculo corruptos ejecutable, lo mejor es cambiar el nombre del archivo oracleO de "oráculo". Si lo hace, asegúrese de restablecer los permisos a 700. Lo mismo ocurre con lsnrctl0 y tnslsnr0 ejecutables.
• Cambio de la emtgtctl2 permisos no tendrá ningún impacto si se utiliza el software de Oracle como el identificador de usuario propietario de Oracle Enterprise Manager credenciales del sistema operativo. Si utiliza un identificador de usuario diferente (no orasoft, por ejemplo), el SUID se debe restablecer el valor anterior y los permisos se debe establecer como estaban.
• El dbnsmp ejecutable es utilizado por Oracle Enterprise Manager Intelligent Agent, pero sólo hasta base de datos Oracle9i Release 2. Una vez más, si se utiliza el propietario del software de Oracle como las credenciales del sistema operativo, no hay impacto de cambiar los permisos. Si utiliza un identificador de usuario diferente, debe restablecer los permisos al valor anterior.

Action Plan
1. Change permissions of oracleO, tnslsnr0, and lsnrctl0 to 0600.
2. Change permissions for tnslsnr and lsnrctl to 0700.
3. Do you use external jobs in Oracle Enterprise Manager?

IF no THEN change the permissions of extjob to 0000
ELSE
Change the permissions of extjob to 0700 and change the owner and group to orasoft and oinstall (or whatever the user and group of the Oracle software owner are).
END IF
4.

IF you are on Oracle9i Database THEN
Are you using Oracle Intelligent Agent?
IF no THEN
Change ownership of dbsnmp to orasoft
Change permissions to 0700
ELSE
No change needed
END IF

Note: If you apply a patch or upgrade the database, the permissions will be reset, so you need to re-examine them after the upgrade.

1.5 Change DBSNMP Password

Antecedentes

Como ustedes saben, Oracle inteligente agente se comunica con Oracle Enterprise Manager para transmitir información acerca de los componentes, tales como la base de datos, el oyente, y el propio servidor. Para obtener datos sobre la base de datos, necesita conectarse a la base de datos utilizando algún identificador de usuario. De forma predeterminada, el identificador de usuario utilizado es DBSNMP.
Cuando la base de datos es creada, la contraseña de DBSNMP] también se establece en dbsnmp. Este usuario tiene privilegios de gran alcance, tales como UNLIMITED TABLESPACE, SELECT cualquier diccionario (que permite al usuario elegir entre vistas de rendimiento dinámico y puntos de vista del diccionario de datos), y analizar cualquier diccionario (que permite analizar de los objetos del sistema). Muchos intrusos utilizar este identificador de usuario y contraseña para entrar por la puerta trasera en la base de datos. Huelga decir que se trata de un enorme agujero de seguridad.
Estrategia
Tienes que cambiar la contraseña de este usuario en algo distinto de dbsnmp. Sin embargo, uno no puede cambiar la contraseña en el nivel de base de datos, ya que la contraseña es almacenada en los archivos de configuración del agente. Es necesario actualizar los archivos de configuración para utilizar la nueva contraseña también. Aquí está el procedimiento para Oracle Database 10g:

1. First change the password of the user DBSNMP to something else—for example, TopSecret:
SQL> alter user dbsnmp identified by topsecret;
2. Go to the directory where the Oracle Agent Home is installed (not ORACLE_HOME)—for example, /u01/app/oracle/10.1/gridc.
3. Go to directory /sysman/emd , where is the name of the host or server. For instance, if the name of the server is prolin1, then the directory should be prolin1/sysman/emd.
4. Here you will find a file named targets.xml. Copy it under a new name (for example, targets.xml.old).
5. Open the file targets.xml and search for the word “dbsnmp”; the contents should be similar to:

6.
Note this line (in bold type, above):

This is where you will set the value of the password. Replace the above with

Note that you changed the value of ENCRYPTED to FALSE.
7. If this is a RAC database, this line will occur twice in the file. Make sure you change both occurrences. Search the file for the word “password” to locate these two instances
8. Now stop the agent by issuing this command
/u01/app/oracle/10.1/gridc/bin/emctl stop agent

Restart the agent:
/u01/app/oracle/10.1/gridc/bin/emctl stop agent
10.
When you restart the agent, the cleartext password in the configuration file is encrypted. If you check the above line in the targets.xml file again, you will see something similar to the following:

Note how the cleartext value has been converted to an encrypted value.
11. Now the agent is configured with the new password.
12. If you use the standalone Database Console instead of Oracle 10g Grid Control, then the procedure is similar—except that in Step 2, you would go to ORACLE_HOME, not where the Agent Home is located.

Implications
There are no user implications here.
Action Plan
1. Change the password of the user DBSNMP.
2. Update the agent files to reflect the new password.
1.6 Limit SYSDBA Login
Background
You might have noticed that any *nix user who is the member of the group dba can log in as the SYSDBA user by issuing this command:
sqlplus / as sysdba

This is usually taken as a matter of convenience, because you don’t need to remember or enter the password of the user SYS. However, this also creates a vulnerability: Any user who can log in as a dba group member can log into the database as SYS. The fortified password of SYS is not of much use then. If you have a strong SYS account, you should perhaps protect that as well as the dba group users so that the password of SYS is necessary to log in as SYS. This approach does not eliminate the risk of infiltration but does reduce it considerably.
Strategy

This process is controlled by the parameter SQLNET.AUTHENTICATION_SERVICES in the file SQLNET.ORA. If this parameter is set to NONE, then the auto login of the SYSDBA role is disabled. To disable it, place the following line in the SQLNET.ORA file located in the $ORACLE_HOME/network/admin directory.
SQLNET.AUTHENTICATION_SERVICES=(NONE)

From that point on, if a *nix user belonging to the group dba wants to connect using this familiar login:

$ sqlplus / as sysdba

they will get this:
ERROR:
ORA-01031: insufficient privileges

To connect, you must provide the SYS password:
$ sqlplus /nolog
SQL> connect sys/oracle as sysdba

This protects against someone who still does not know the SYS password from gaining access to the dba accounts.
Implications
As shown above, the biggest implication is the use of SYS passwords. You may need to make some changes to the scripts that connect to SYS.
If you ever lose the SYS password, don’t worry. You can comment the line in the file SQLNET.ORA and then connect the old-fashioned way: / as sysdba.
Action Plan
IF you use SYS connections in scripts THEN
Change / as sysdba to sys/ as sysdba
Place SQLNET.AUTHENTICATION_SERVICES=(NONE) in the file SQLNET.ORA
ELSE
No change needed
END IF

1.7 Create a Listener Password

Background

One of the most popular hacker tricks is to inject a large amount of text into the listener, thereby causing it to abort. The database could still be up, but since the listener is down, no new connections can be established—which in effect is a denial-of-service attack.
To do that, the hacker might attempt to change the attributes of the listener. A popular tactic here is to list the various services handled by the listener via the services command. Note how much information is displayed—possibly enough for the hacker to gain illegitimate access:

LSNRCTL> set displaymode verbose
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=prolin1.proligence.com)(PORT=1521)(IP=FIRST)))
Services Summary...
Service "PROPRD" has 1 instance(s).
Instance "PROPRD1", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=/u01/oracle/products/10.1/db1/bin/ora
cle)(ARGV0=oraclePROPRD11)(ARGS='(LOCAL=NO)')(ENVS='_=/u01/oracle/pro
ducts/10.1/db1/bin/racgmain,_USR_ORA_CONNECT_STR=/ as
sysdba,_CAA_CHECK_INTERVAL=600,SHLIB_PATH=/u01/oracle/products/10.1/d
b1/lib32:/u01/oracrs/10gr1crs/lib32:/opt/nmapi/nmapi2/lib/hpux32:,_CA
A_ACTIVE_PLACEMENT=0,PATH=,_USR_ORA_ALERT_NAME=,_USR_ORA_IF=,_CAA_OPT
IONAL_RESOURCES=,_USR_ORA_START_TIMEOUT=0,ORACLE_BASE=/u01/oracle/pro
ducts/10.1/db2,_USR_ORA_DISCONNECT=false,_CAA_SCRIPT_TIMEOUT=600,_CAA
_UPTIME_THRESHOLD=7d,_USR_ORA_STOP_TIMEOUT=0,_CAA_FAILOVER_DELAY=0,_U
SR_ORA_PRECONNECT=none,_USR_ORA_FLAGS=,_CAA_TYPE=application,_USR_ORA
_INST_NOT_SHUTDOWN=,_CAA_REASON=boot,INIT_STATE=3,_USR_ORA_OPEN_MODE=
,_CAA_STATE=:OFFLINE,,_CAA_RESTART_ATTEMPTS=5,_CAA_ACTION_SCRIPT=/u01
/oracle/products/10.1/db1/bin/racgwrap,_CAA_DESCRIPTION=CRS
application for
Instance,_CAA_HOSTING_MEMBERS=prolin1,ORA_RACG_EXEC_ENV=LD_LIBRARY_PA
TH=/u01/oracle/products/10.1/db1/lib:/u01/oracrs/10gr1crs/lib:/opt/nm
api/nmapi2/lib/hpux64:/usr/lib:,_CAA_CLIENT_LOCALE=,_CAA_NAME=ora.PRO
PRD1.PROPRD11.inst,ORA_CRS_HOME=/u01/oracrs/10gr1crs,_CAA_AUTO_START=
1,_CAA_TARGET=:ONLINE,,_USR_ORA_PFILE=,_USR_ORA_OPI=false,_USR_ORA_CH
ECK_TIMEOUT=0,_CAA_PLACEMENT=restricted,_USR_ORA_LANG=,LD_LIBRARY_PAT
H=/u01/oracle/products/10.1/db1/lib:/u01/oracrs/10gr1crs/lib:/opt/nma
pi/nmapi2/lib/hpux64:/usr/lib:,_CAA_REQUIRED_RESOURCES=ora.prolin1.vi
p,_CAA_FAILURE_THRESHOLD=0,ORACLE_HOME=/u01/oracle/products/10.1/db1,
_USR_ORA_SRV=,PWD=/u01/oracrs/10gr1crs/bin,_USR_ORA_VIP=,_USR_ORA_STO
P_MODE=immediate,_CAA_FAILURE_INTERVAL=0,_USR_ORA_NETMASK=,_USR_ORA_D
EBUG=0,ORACLE_SID=PROPRD1,ORA_NET2_DESC=9,12,ORACLE_SPAWNED_PROCESS=1
')(ENV_POLICY=NONE))

Another hacker trick is to shut down the listener. New connections will be refused, again effectively creating a denial-of-service attack.
In addition, the listener can be remotely administered. Using this technique, the hacker can remotely stop a listener by attacking another vulnerable machine.
How do you protect yourself from these threats?

Strategy
The best option is to remove all permissions from the executable files tnslsnr and lsnrctl except those for the owner. (This method has been described in the previous section.) This way no one except the Oracle software owner can start or stop the listener. The executables look like this:
-rwx------ 1 orasoft oinstall 214720 Oct 25 01:23 lsnrctl
-rwx------ 1 orasoft oinstall 1118816 Oct 25 01:23 tnslsnr

In some cases, you may want to grant the authority to start and stop the listener. In such a case, you have to turn on the permissions:

$ chmod 0711 lsnrctl

However, in such a case, you should prevent unauthorized use by enforcing a password. When you set a password, all commands—except some benign ones such as HELP—are disabled.
Setting the password works the same way in all versions of Oracle Database, but the enforcement mechanism varies:
• In Oracle9i Database Release 2 and earlier, all users need a password.
• In Oracle Database 10g Release 1 and later, the OS user who owns the database software does not need a password. All others need a password.
Here’s how to set the password:
$ lsnrctl

LSNRCTL> change_password
Old password: Not displayed
New password: Not displayed
Reenter new password: Not displayed
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)(IP=FIRST)))
Password changed for LISTENER
The command completed successfully

If you are setting the password for the first time, you can press ENTER when asked for the “Old Password.” After making the change, save it in the parameter file:
LSNRCTL> save_config

This command encrypts the password and places it in the listener parameter file. You can examine it later:
#----ADDED BY TNSLSNR 24-OCT-2005 17:02:28---
PASSWORDS_LISTENER_ODSSDB01 = 75CD180DE6C75466
#--------------------------------------------

When you decide to use a command, you have to supply the right password (in Oracle Database 10g and later, the OS user who owns the software does not need a password):
LSNRCTL> services
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-01169: The listener has not recognized the password

To pass the correct password, issue these commands:,
LSNRCTL> set password mypassword
The command completed successfully
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
...

If you pass the wrong password, you will receive this error:
TNS-01169: The listener has not recognized the password.

If you do not pass the password and try executing a powerful command, you will receive this error:
TNS-01190: The user is not authorized to execute the requested listener command

To confirm that the password is in effect, view the setting in the STATUS display of the listener. To do so, issue this command:
$ lsnrctl status

The output varies between versions. For Oracle9i Database, here is a partial output:
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 9.2.0.6.0 - Production
Start Date 25-OCT-2005 10:26:47
Uptime 0 days 13 hr. 8 min. 36 sec
Trace Level off
Security ON

Note the last line (Security ON), which indicates that the password is set.
In Oracle Database 10g, this process is a little different. In that release, the listener is set to be executable by the Oracle software owner only without a password. If you have set a password, then other users can execute it after supplying the password. Here is the status display:
STATUS of the LISTENER
------------------------
Alias LISTENER_ODSPDB02
Version TNSLSNR for HPUX: Version 10.1.0.4.0 - Production
Start Date 16-OCT-2005 05:58:35
Uptime 9 days 17 hr. 44 min. 41 sec
Trace Level off
Security ON: Local OS Authentication

Note the last parameter, ON: Local OS Authentication which indicates that the password has not been set. When the password is set, the display will show that line as
Security ON: Password or Local OS Authentication

Note the additional clause above—Password— which indicates that a password has been set. In Oracle Database 11g Release 2, the password in listener.ora has been deprecated, so you shouldn’t set a password at all. In other words, you should not issue any listener commands using any userid other than the Oracle Database software (or the Oracle Grid Infrastructure software) owner.
Implications
In Oracle Database 10g and later, there are no implications. OS authentication is used to authenticate the user, and there is no need to provide a password to start or stop the listener. In Oracle9i Database and earlier, you have to provide a password, and hence the script requires a password.
Action Plan
IF Oracle Database 10g or later THEN
Remove the permissions from all except the owner
ELSE
Remove the permissions from all except the owner
Set a password for the listener operations
END IF

1.8 Protect the Listener
Background

Creating a buffer overflow—crashing the listener by sending a large string to be executed—is a common intruder tactic. Another popular one is to see the various components using SET DISPLAYMODE VERBOSE from the lsnrctl utility. In this case, the hacker may manipulate the settings by running lsnrctl on a vulnerable machine to administer the listener on the target server. Here’s an example:
LSNRCTL> set trc_level support
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)))
LISTENER parameter "trc_level" set to support
The command completed successfully
LSNRCTL> set trc_directory /tmp
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prolin1)(PORT=1521)))
LISTENER parameter "trc_directory" set to /tmp
The command completed successfully

Because the trace level is SUPPORT, the listener generates a lot of information that you may not want the hacker to know. Also, because the trace files are written to the directory /tmp, he can easily see them as well. All this can be learned without even being on the server.
Strategy
To protect the listener, the best option is to set a password. In addition, you can employ another approach: restricting the ability to set various parameters of the listener using the lsnrctl utility. In that case, the only way to change parameters is to set them in the listener configuration file and then reload it. To set these restrictions, you can place the following line in the listener.ora file:
ADMIN_RESTRICTIONS_LISTENER = ON

and then restart the listener. Now, you can no longer use the SET command in the lsnrctl prompt to change a value. For example:
LSNRCTL> set trc_directory /hacker_dir
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=PROPRD1))
TNS-12508: TNS:listener could not resolve the COMMAND given

Note the TNS-12508 error. From now on, to change a value of a parameter you must do so in listener.ora and then use the reload command:
LSNRCTL> reload

This is true for all Oracle versions discussed in this series.
Even if you use a password to protect the listener, you should still use this technique to further restrict the hacker’s ability to administer the listener. This is particularly true in Oracle Database 10g, where a listener password is not required for the Oracle software owner.
Implications
The implications are negligible. Few users edit parameters online anyway; rather, they edit listener.ora and then reload the listener. Thus, this change will not affect them at all.
Note, however, that it will no longer be possible to use remote listener control to administer a listener on a different server. Instead, you have to log onto the server to make the change in listener.ora and then reload the listener, which is best practice anyway.
Action Plan
Place the parameter ADMIN_RESTRICTIONS_LISTENER = ON in the file listener.ora.
1. Reload the listener by issuing lsnrctl reload.
1.9 Trim Sweeping Privileges
Background
A typical user needs privileges that are important to perform his or her job—nothing more nor less. As this policy may prove unrealistic, however, you may need to adopt a middle-of-the-road approach: removing the most-powerful privileges the users do not need.
One example of a powerful privilege is CREATE ANY TABLE, which lets the user create a table in any schema, not just its own. Rarely do users need this privilege; you can safely revoke it. On the other hand, a privilege like QUERY REWRITE, which allows the users sessions to rewrite a query to take advantage of a function-based index or materialized view, is relatively innocuous.
Strategy
First, identify all the privileges you consider innocuous (CREATE TYPE, CREATE SESSION, and so on). Here I have included UNLIMITED TABLESPACE as a nonsweeping privilege, but you may disagree:
Version:1.0 StartHTML:0000000167 EndHTML:0000012073 StartFragment:0000000461 EndFragment:0000012057
set pages 50000
break on privilege skip 1

select privilege, grantee, admin_option
from dba_sys_privs
where privilege not in
(
/* list any other privilege here you don't find "sweeping"
*/
'ALTER SESSION',
'QUERY REWRITE',
'CREATE DIMENSION',
'CREATE INDEXTYPE',
'CREATE LIBRARY',
'CREATE OPERATOR',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SNAPSHOT',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE USER',
'CREATE VIEW',
'UNLIMITED TABLESPACE'
)
and grantee not in
('SYS','SYSTEM','WKSYS','XDB',
'MDSYS','ORDPLUGINS','ODM','DBA')
/* Place all the user names you want to exclude */
order by privilege, grantee
/
Here is part of a sample output:
set pages 50000
break on privilege skip 1

select privilege, grantee, admin_option
from dba_sys_privs
where privilege not in
(
/* list any other privilege here you don't find "sweeping"
*/
'ALTER SESSION',
'QUERY REWRITE',
'CREATE DIMENSION',
'CREATE INDEXTYPE',
'CREATE LIBRARY',
'CREATE OPERATOR',
'CREATE PROCEDURE',
'CREATE SEQUENCE',
'CREATE SESSION',
'CREATE SNAPSHOT',
'CREATE SYNONYM',
'CREATE TABLE',
'CREATE TRIGGER',
'CREATE TYPE',
'CREATE USER',
'CREATE VIEW',
'UNLIMITED TABLESPACE'
)
and grantee not in
('SYS','SYSTEM','WKSYS','XDB',
'MDSYS','ORDPLUGINS','ODM','DBA')
/* Place all the user names you want to exclude */
order by privilege, grantee
/

Here is part of a sample output:

PRIVILEGE GRANTEE ADM
--------------------------- ------------------------------ ---
ADMINISTER DATABASE TRIGGER EXFSYS NO
IMP_FULL_DATABASE NO

ADMINISTER RESOURCE MANAGER EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO

ALTER ANY MATERIALIZED VIEW DWETL NO
REPORTMAN NO

ALTER ANY OUTLINE REPORTMAN NO

ALTER ANY PROCEDURE IMP_FULL_DATABASE NO
QCO NO

ALTER ANY RULE CDC_PUB YES

ALTER ANY RULE SET CDC_PUB YES

ALTER ANY TABLE IMP_FULL_DATABASE NO
CNSMP NO
QCO NO

ALTER ANY TRIGGER IMP_FULL_DATABASE NO
QCO NO
VCHANG NO

ALTER ANY TYPE IMP_FULL_DATABASE NO

ALTER SYSTEM ORADBA NO
QCO NO

ALTER TABLESPACE QCO NO

ALTER USER QCO NO
SYSMAN NO

ANALYZE ANY AFFMAN NO
ARAO NO
CONCASTER NO
CREATE ANY SYNONYM ATHOTANG YES
ARUP YES
IMP_FULL_DATABASE NO
DB_MONITOR YES
QCO YES
RCHUNG YES
SPOT YES

CREATE ANY TABLE IMP_FULL_DATABASE NO
CNSMP NO
QCO NO
SYSMAN NO

DROP ANY TABLE ATHOTANG YES
IMP_FULL_DATABASE NO
CNSMP NO
QCO YES
_ and so on _

Note some of the key lines of the output. Some sweeping privileges—DROP ANY TABLE, for instance—probably shouldn’t have been granted to anyone. In this case, let’s see which users have this privilege.
• IMP_FULL_DATABASE is a role used to do a full database import, usually granted to the DBA or to any other user needed in the import. The privilege is probably necessary.
• QCO refers to Quest Central for Oracle, a popular tool for managing databases. The privilege is probably necessary; but you have to be careful to make sure no one except the DBAs have the access to the QCO userid..
• The users CNSMP and ATHOTANG, unless they are DBAs, do not appear to need any sweeping privileges. The privilege should be revoked.
Implications
This is one action you can’t execute immediately. Before revoking any privilege from any user, you have to carefully analyze its impact.
If you have doubts, the best course of action is to interview the people behind the userids. For example, perhaps ATHOTANG does not really need to drop tables but was assumed to do so. (Don’t be surprised—such assumptions are common.)
Action Plan
This task requires some planning, so reserve any action for the next phase. Until then, collect the required information.
1.10 Move Audit Trails to a Different Tablespace
Background
When the audit trail is set to database and for fine-grained auditing trails, the trail records are stored in regular database tables—AUD$ and FGA_LOG$ under the SYS schema. They are located in the SYSTEM and SYSAUX tablespaces, respectively. Because records in these tables simply keep growing as activity increases, the space consumed by these special tablespaces also increases, which may not be desirable. If the system tablespace runs out of space, the database will abort.
Therefore, you should move these tables into a special tablespace—say, AUDIT_TS—as shown below:
create tablespace aud_ts
datafile '+DATA'
size 10M
segment space management auto
/

In Oracle Database 11g Release 2, moving these tables to a different tablespace is supported. Prior to this release, the move is officially not supported. This section pertains to Oracle Database 11g Release 2 only.
Move the AUD$ table to that tablespace:
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUD_TS');
end;
/

If you want to move the FGA_LOG$ table as well, you can do so by executing the above PL/SQL block but replacing the parameter as shown below:
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std

Once executed, the tables will be moved to the AUD_TS tablespace.
Implications
The advantages are obvious—the audit trails are the only items that keep growing in the database based on usage, and hence moving it out of the SYSTEM and SYSAUX tablespaces is a desirable move.
Important: If the tablespace is offline, you will get an error for auditable actions. Here is an example where a user is trying to connect (an event that is audited) but fails because the audit table is not available:
SQL> conn arup/arup
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/d112d1/datafile/aud_ts.293.698028255'
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/d112d1/datafile/aud_ts.293.698028255'

Warning: You are no longer connected to ORACLE.

To resolve it, you should bring the tablespace online (after fixing the underlying issue, of course). But what if the underlying issue is not resolvable immediately? Then you have no choice but to remove the audit settings. For instance, you can turn off auditing on session
SQL> noaudit session;
Noaudit succeeded.

Now if you try connecting, you will be successful:
SQL> conn arup/arup
Connected.

Action
IF Oracle Database is earlier than 11g Release 2 THEN
Don’t do anything
ELSE
Create a new tablespace
Move the audit trail(s) to the new tablespace
Make sure that tablespace is online.
END IF

________________________________________

Phase 2
Duration: One Week
It's Phase 2 of your security and compliance project. Let's see what you can do within 5 business days to lock down your infrastructure.
Even if it only lasted 24 hours, you accomplished much in Phase 1 of Project Lockdown. Now it's time for the second phase, which will take about a week. In this phase, you will follow up on work performed in the earlier phase and make new headway as well. As before, you will take some simple yet highly effective actions for securing the database.
Why does this phase last a week, you may ask?
• Some changes require setting of parameters that lead to recycling of the database, and that can take some planning.
• Some changes are based on Phase 1 actions, and may require some careful planning.
Let's get started!
Covered in This Installment:
• 2.1 Remove utl_file_dir
• 2.2 Limit OS Authentication
• 2.3 Disable Remote OS Authentication
• 2.4 Secure SQL*Plus Using Product Profile
• 2.5 Rein In SQL*Plus
• 2.6 Wrap Sensitive Code
• 2.7 Convert Derived Grants to Direct Grants
• 2.8 Limit Tablespace Quotas
• 2.9 Monitor Listener Logs for Attempted Break-Ins
• 2.10 Audit and Analyze User Access

2.1 Remove utl_file_dir

Antecedentes

Oracle proporciona una forma de manipular los archivos de sistema operativo desde el interior de la base de datos, a través de SQL y / o PL / SQL, sin acceder al sistema operativo anfitrión. Este servicio es proporcionado por el incorporado en PL / SQL UTL_FILE paquete. Por ejemplo, he aquí un fragmento de código que abre un archivo con el nombre miarchivo.txt en el directorio / tmp y escribe la línea de "un poco de texto" a la misma

1 declare
2 l_filename utl_file.file_type;
3 begin
4 l_filename := utl_file.fopen ('/tmp','myfile.txt','W');
5 utl_file.put_line (l_filename, 'Some Text', TRUE);
6 utl_file.fclose(l_filename);
7 end;

If the file myfile.txt is not present, this code segment will create it. However, for it to work, beforehand you must declare /tmp as a directory that can be opened by the utl_file package via the following initialization parameter:
utl_file_dir = '/tmp'
and then restart the database.

The presence of this parameter indicates that the users can create a file in the directory in /tmp, or overwrite any file there, as long as it's owned by the Oracle software owner. So what's wrong with that?
Nothing, except that most systems will not have this parameter set to /tmp. Rather, to allow developers or users to manipulate files without knowing in advance where the files will be created, the setting will most likely be "*", as in the following:
utl_file_dir = '*'

Esto significa que el usuario puede abrir un archivo donde el propietario del software de Oracle tiene permiso para leer o escribir archivos, incluso los archivos de datos de Oracle! Teóricamente, entonces, un adversario puede escribir un programa que intencionalmente daña un archivo de datos. Si este archivo pasa a ser el espacio de tablas SYSTEM, la base de datos se ha ido. (Por supuesto, usted puede restaurar la copia de seguridad, pero el daño está hecho ya.) Peor aún, el adversario puede dañar un archivo de registro archivado, lo que impediría la recuperación más allá de ese registro archivado. El adversario podría incluso eliminar el archivo, utilizando el paquete suministrado utl_file.fremove.
Por lo tanto, el establecimiento de utl_file_dir con "*"es simplemente una mala idea.
Estrategia
En Oracle9 i base de datos y después, no hay necesidad de utl_file_dir programas de uso del objeto de directorio para especificar ubicaciones, no directorios absolutos. Así, en el ejemplo anterior donde la aplicación debe abrir un archivo miarchivo.txt en el directorio / tmp,

it can do so by:
1 declare
2 l_filename utl_file.file_type;
3 begin
4 l_filename := utl_file.fopen ('TMP_DIR','myfile.txt','W');
5 utl_file.put_line (l_filename, 'Some Text', TRUE);
6 utl_file.fclose(l_filename);
7 end;

The directory object TMP_DIR must have been created earlier by:
create directory TMP_DIR as '/tmp';
This command, create directory, is given only once. Subsequently, anyone who has access to the directory can create or read files from here.
You could grant this directory object in a fine-grained manner. For instance, suppose you have a directory object on the directory where the alert log is located and you have created an application that reads and manipulates the alert log. You can grant read access to the directory to SCOTT but write access to ananda, who is a DBA.
So, remove the utl_file_dir from the database parameter and replace the code with directory objects.
As a corollary, you should also remove the create directory system privilege from PUBLIC and any other user.
Implications
The implication depends on how much the utl_file package is used to manipulate external files. If the package is never used, you can remove it right away. If you are using it, you need a plan first.
Action Plan
1. Scan the code to see the following string: utl_file.fopen.
2. Collect all the names in the first parameter. For instance ,when you see utl_file.fopen ('/tmp','myfile.txt','W'), record the value "/tmp". Go through all the files to get all the directory locations. There may be 10 files using "/tmp", 5 using "/ftpdata", and so on.
3. As user SYS, create a directory object for each of these directories, e.g.
4. create directory TMPDIR for '/tmp';
5. Grant these directories to the users accessing them, e.g.
6. grant read on directory TMPDIR to SCOTT;
7.
8. Remove the directory name form the code replacing it with directory object name. For instance, the line:
9. utl_file.fopen ('/tmp','myfile.txt','W')
10. will become
11. utl_file.fopen ('TMPDIR','myfile.txt','W')
12. Recompile the programs.
13. Remove create any directory system privilege from PUBLIC and all users except DBAs.
14. revoke create any directory from public;
________________________________________
2.2 Limit OS Authentication
Background
Oracle users can be authenticated in different ways, most commonly via database authentication. When a user is created as create user ananda identified by abc123, the only way the user can log in to database is by passing its userid and password.
One alternative is operating system authentication, in which the user is created as:
create user ops$ananda identified externally;
If the host operating system has a userid named "ananda", then Oracle does not check its credentials anymore. It simply assumes the host must have done its authentication and lets the user into the database without any further checking.
That's where the problem lies. If the host operating system is strong in authentication, it may be secure; but in some weak OSs, it is possible to login as a user by cracking the password or entering without a password:
sqlplus /
Note the lack of userid and password—the string "/" instructs the database to accept the connection of the userid ananda to the database account ops$ananda.
This type of authentication commonly useful in shell scripts so that you don't have to embed the password in the script, but simply call it as sqlplus /. This is not only convenient but also somewhat secure, since the password is not present. However, consider this scenario: In weak-security OSs, someone can create an account called ananda and then use it to log into account ops$ananda.
Must it be ops$? Not really; you can change it by setting an initialization parameter. In the following example, I have set it to osauthent$.
os_authent_prefix = 'osauthent$'
You can find these users by using the following query:
SQL> select username, password from dba_users
2 where password = 'EXTERNAL'
3 /

USERNAME PASSWORD
------------------------------ ------------------------------
OPS$ANANDA EXTERNAL
OPS$ORACLE EXTERNAL
When the initialization parameter is set like this, the account ops$ananda will not work; instead, you need to create those accounts (OS-authenticated) as osauthent$ananda. In an interesting twist , you can also set it to "" (null). In that case the OS user ananda will map to Oracle user ananda. You can even set the password for this account:
alter user ops$ananda identified by oracle;
In that case, the user can log into the database in either manner:
sqlplus /
sqlplus ops$ananda/oracle
So, what's wrong with that? Well, consider the situation. Suppose the parameter os_authent_prefix is set to "" (null). In a weak OS, someone can create a user called SYSTEM and login as
sqlplus /
This will log the user as the Oracle user SYSTEM! Once logged in, the user can do anything they want—create users, drop data files, look into sensitive data, and a lot of other things. Suddenly, something that seemed like a convenience is a huge liability.
Strategy
As you can see, the issue arises only in certain combination of occurrences. One of them is the OS_AUTHENT_PREFIX being not null, and the other one is setting the password for OS-authenticated accounts. So the first thing to check is the OS authentication prefix.
SQL> select value
2 from v$parameter
3 where name = 'os_authent_prefix';

VALUE
-----------------------
ops$
If the above returns null, then you should make plans to change it. The actual value is not important, but you must include some non-alphanumeric character. That way, the OS-authenticated username will never match an actual user.
Second, you need to make sure the OS-authenticated accounts are authenticated exactly that way—by the OS—and never have a password. For example, if your OS_AUTHENT_PREFIX were set to OPS$, you would use the following query to find out whether or not the password is set:
SQL> select username, password from dba_users
2 where username like 'OPS$%';

USERNAME PASSWORD
------------------------------ ------------------------------
OPS$ORACLE 17C96FEC14DC431F
OPS$ANANDA EXTERNAL
This shows that the user OPS$ORACLE cannot login through the OS authentication route or the password route. This is exactly what you want to avoid; there should be only one way to authenticate. To change the mode of authentication of OPS$ORACLE, you should use:
alter user OPS$ORACLE identified externally;
This changes the PASSWORD column to EXTERNAL.
Implications
The implications of these changes may be extensive depending on the usage of these accounts. If you have any of these types of accounts, scan the programs to find out how easily they can be changed.
Action Plan
Find out which programs are using the OPS$ accounts.
If none then
Check initialization parameter os_authent_prefix
If it's null then
Change it to OPS$ (database restart required)
Check password of OPS$ accounts
If not EXTERNAL then
Change them to EXTERNAL
If some then
Check if they are using it as a password as well (e.g. OPS$ORACLE/mypass).
If a password is used, remove it—e.g. the line sqlplus OPS$ORACLE/mypass should become sqlplus /.
________________________________________
2.3 Disable Remote OS Authentication
Background
Remote OS authentication is a similar process. In OS authentication, you can create users who are authenticated on the local operating system. For instance, if you have a user named ananda on the UNIX server and a user named OPS$ANANDA in the database running on the same server, the user ananda can simply issue the following command to log into the database:
sqlplus /
This works for the local users—users on the same server where the database runs. However, what happens when a user of a remote server tries to connect to the database? For instance, suppose you have two servers, node1 and node2, and a database runs on the node1 server. This database has a user OPS$ANANDA identified externally. There is a user ananda on the server node2, not node1. When user ananda on the server node2 tries to connect to the database on node1 by issuing:
sqlplus /@node1
will he be able to connect?
The answer depends on the settings of the database initialization parameter remote_os_authent. If the parameter is set to TRUE, he connection attempt is successful; if FALSE, the OS-authenticated connections from remote servers are not allowed. The default is FALSE.
Why not? For a very good reason: On your database server you may have a great deal of security, so OS-authenticated users may not be an issue. But you cannot say the same thing for client machines. Depending on the OS, an adversary may be able to create an account called ananda on the remote servers, and when it connects to the database server, it can connect to ops$ananda without a password. Because you can't easily control client machines, this functionality puts a big chink into your armor.
Strategy
The task here is quite simple. Check if the parameter is set to TRUE. If true, change it to FALSE and restart the database.
Implications
In many databases, this parameter may already be set to FALSE. However, if it's set to TRUE and some client machines connect as OS-authenticated users, those accounts will not work. You can find them before making this change:
select username, machine, osuser, terminal
from v$session
where username like 'OPS$%'
/
The output might show something like this:
USERNAME MACHINE OSUSER TERMINAL
------------- -------------------- ------------ -----------
OPS$ANANDA PRONT\ANANDAT42 ananda ANANDAT42
Here we see that the user ananda has logged as remote OS authenticated from the machine PRONT\ANANDAT42. Note here that ananda is a user in the machine PRONT\ANANDAT42, not the server where the database runs.
Action Plan
1. If some users connect using remote authentication, then remove their no-password functionality.
2. In the initialization parameter file, change remote_os_authent to FALSE and restart the database.
________________________________________
2.4 Secure SQL*Plus Using Product Profile
Background

Cuando dentro de SQL * Plus, te habrás dado cuenta de que puede ejecutar comandos como host para ir a la * nix del sistema o de ejecución para ejecutar una secuencia de comandos. Un poco utilizado, pero potencialmente mortal ataque se origina a partir de esta funcionalidad. Puede tener algunos de SQL * Plus script que se llama de la concha similar a lo siguiente:

sqlplus someuser/somepass@remotedb @myscript.sql

Here myscript.sql is a SQL*Plus script file that executes and then exits. During execution, the user may press Control-C (or the break key sequence for the specific OS) and interrupt the flow of the program. In this case the user gets the SQL*Plus prompt, connected as someuser. At this stage, the user can do pretty much everything that is possible from the SQL*Plus prompt—and therefore do some damage.
This is a security hole you should patch up immediately.
Strategy
The fix is easy: All you have to do is restrict these commands to a "special place" that SQL*Plus refers to before running commands. This special place is a table named SQLPLUS_PRODUCT_PROFILE in the SYSTEM schema. If the table is not present, you will get a warning similar to "Product User Profile Not Loaded" every time you start SQL*Plus.
SQL> select * from system.SQLPLUS_PRODUCT_PROFILE
2 /

no rows selected

SQL> insert into system.SQLPLUS_PRODUCT_PROFILE
2 values ('SQL*Plus','%','HOST',null,null,'DISABLED',null,null)
3 /

1 row created.

SQL> commit;

Commit complete.
Now when you start SQL*Plus and enter the host command (or its equivalent "!"), you will get the error:
SQL> host;
SP2-0544: Command "host" disabled in Product User Profile
If you want to disable this for a certain user , simply replace the "%" (for all users) in the above insert command with the username you want to restrict. For instance, if you want this disabled for user SCOTT only, use the following statement:
SQL> insert into system.SQLPLUS_PRODUCT_PROFILE
2 values ('SQL*Plus','SCOTT','HOST',null,null,'DISABLED',null,null)
3 /
You can also use wildcards in the username—such as APP% for all users starting with APP (APPUSER1, APPUSER2).
This approach works for other commands (and their abbreviated or shortcut forms) as well:
accept edit repheader
append execute run
archive log exit save
attribute quit set
break get show
btitle help shutdown
change host spool
clear input start
column list startup
compute password store
connect pause timing
copy print ttitle
define prompt undefine
del recover variable
describe remark whenever oserror
disconnect repfooter whenever sqlerror
This method also applies to SQL commands, not just SQL*Plus. For instance, if you want to disable the lock command, you will need to insert:
insert into system.SQLPLUS_PRODUCT_PROFILE
values ('SQL*Plus','%','LOCK',null,null,'DISABLED',null,null)
/
Note that the SQL command lock is disabled, yet we have used the "SQL*Plus" keyword above.
The following SQL commands can be disabled:
alter drop revoke
analyze explain rollback
associate flashback savepoint
audit grant select
call insert set constraints
comment lock set role
commit merge set transaction
create noaudit truncate
delete purge update
disassociate rename validate
Implications
Before starting, note an important point: This approach applies to the SQL*Plus running on the server itself. It does not affect the SQL*Plus running elsewhere, such as at the client.
This action requires some careful planning. If your shell scripts depend on the host command from SQL*Plus, disabling them would obviously stop these jobs. One very good example is the old host backup command. A typical SQL script file may look like:
alter tablespace xyz begin backup;
host cp /fs1/xyz.dbf /backup/xyz.dbf
alter tablespace xyz end backup;
...and so on for all tablespaces. This script will of course fail—unless it's run from the SYS user, in which case the command disabling won't not work.
Action Plan
Disable all such commands from SQL*Plus when they are least likely to be used.
________________________________________
2.5 Rein In SQL*Plus

Antecedentes

Dependiendo de su uso de secuencias de comandos de SQL * Plus, usted puede o no puede estar expuesto a las amenazas descritas anteriormente. En cualquier caso, es beneficioso para reducir el nivel de amenaza, tomando medidas preventivas. Una opción es desactivar los comandos en el perfil de producto como se describe, sino una más fácil, disponible desde Oracle9 i base de datos, es utilizar SQL * restricciones Plus. En esta opción, usted tiene que llamar a SQL * Plus con un nuevo parámetro, restringir. Hay diferentes niveles de restricción. Nivel 1, se deshabilita el host y comandos de edición única.

sqlplus –restrict 1 scott/tiger
Once inside the shell, if the user calls host command, he gets:

SQL> host
SP2-0738: Restricted command "host" not available

Note the error message is different from the product user profile message.
Using -restrict 2 disables save, store, and spool commands, in addition to host and edit. Using -restrict 3 adds get and start to the existing list.
The most important difference is that all users, even SYS, will be subject to this limitation. The product profile restriction does not apply to SYS.
Strategy

This may be a better option than using the product user profile. In this case, create a shell script named "sqlplus" in $ORACLE_HOME/bin and rename the sqlplus executable to something like "sqlplus_orig". Place the following line in the script "sqlplus".
$ORACLE_HOME/bin/sqlplus_orig –restrict 1
This will have the same effect as the command line parameter. Of course, you can add any parameter to restrict you want.
Implications
The implications are similar to those of the previous task, only a little more extensive since they apply to SYS as well. If your scripts call the restricted commands, you may need to modify them or use a non-restricted version.
Action Plan
Identify the SQL scripts that call the restricted commands.
If some are found, then
Modify them OR
Use an unrestricted version
ELSE
Rename sqlplus to sqlplus_orig
Create the file sqlplus with the restriction on
________________________________________

2.6 Wrap Sensitive Code

Background
You may have placed sensitive information such as password or encryption keys inside stored procedures and functions. When you do so, an adversary can easily select it from within the code. Here is an example form a site I recently audited:
l_password := 'GobbleDGook';
An adversary can select the source code as:

select text
from dba_source
where name = 'MYFUNC';
The best way to eliminate this risk is to use the wrap utility. Once you create the script file to create the procedure or function, wrap it by:
wrap iname=myfunc.sql oname=myfunc.plb
This will create a file myfunc.plb that has the source code stored is an undecipherable manner, protecting your code.
In Oracle Database 10g Release 2, you can create the wrapped code directly without a script by calling the supplied procedure:
begin
dbms_ddl.create_wrapped
('create or replace function myfunc ...')
end;
You can also use this to write your own wrap utility:
1 declare
2 l_input_code dbms_sql.varchar2s;
3 begin
4 l_input_code (1) := 'Array to hold the MYP';
5 l_input_code (2) := 'create or replace procedure myproc as ';
6 l_input_code (3) := ' l_key VARCHAR2(200);';
7 l_input_code (4) := 'begin ';
8 l_input_code (5) := ' ...;';
9 l_input_code (6) := 'end;';
10 l_input_code (7) := 'the end';
11 sys.dbms_ddl.create_wrapped (
12 ddl => l_input_code,
13 lb => 2,
14 ub => 6
15 );
16* end;
and then place all your code in the input varray for wrapping.
One other thing to be aware of: In Oracle9 i , the wrap utility does not wrap variable values. Thus, secret words like password and keys are clearly shown inside the wrapped code.
Let's see an example. Here are the contents of the file a.sql:
create or replace procedure myproc
as
l_v varchar2(200);
begin
l_v := 'SecretKey';
end;
I have used the value of the secret words as SecretKey. Let's wrap the source:
$ wrap iname=a.sql oname=a.plb
Now if you check inside of the wrapped code, you will still see the value in cleartext:
$ grep SecretKey a.plb
1SecretKey:
Again, this issue is not present in Oracle Database 10g Release 1 and later.
To protect your variable values, you should make the cleartext value inside the code less readable. For instance, your code could read:
create or replace procedure myproc
as
l_v1 varchar2(200);
l_v2 varchar2(200);
l_v3 varchar2(200);
l_lv varchar2(200);
begin
l_v1 := 'LotsOfJunktoMakeSureSecurityIsWaterTight';
l_v2 := 'AdditionalValueToInterpretTheSecretWord';
l_v3 := 'WowWeDontStopAtTheKeyDoWe';
l_lv := substr(l_v1,21,3);
l_lv := l_lv||substr(l_v2,24,3);
l_lv := l_lv||substr(l_v3,19,3);
end;
/
Note how we have embedded the parts of the secret word inside the strings and used the positions inside the code. You can't see the first part of the code inside the first string.
Strategy
There is no clear way to identify the source code referencing this sensitive data. Your best bet is to ask around the development group to learn who may be using the sensitive words. You can also do a preliminary check to find out if codes contain words like PASSWORD, SECRET, ENC (for ENCrypted, ENCryption, ENCiphered, ENCoded), DEC, and so on.
select name, text
from dba_source
where upper(text) like '%PASSWORD%'
or upper(text) like '%ENC%'
or upper(text) like '%DEC%'
or upper(text) like '%SECRET%'
or upper(text) like '%PASS%'
/

Esto puede ofrecer alguna pista que le ayudará a identificar los segmentos de código posible examinar. Una vez que identificar todo el código tal, debe envolverla con la utilidad de abrigo y ejecutar el código envuelto.
Implicaciones

No hay consecuencias. Sin embargo, usted debe estar enterado de un tema muy serio: Envolver es una calle de sentido único, que puede envolver el código en texto plano, pero no crear texto sin formato a partir del código envuelto. Por lo tanto, debe conservar el código sin cifrar en un lugar seguro para su posterior edición. Si la pierde, se pierde la capacidad de cambiar el código para siempre.

Action Plan

Identify the code containing the sensitive data.
IF in Oracle9i, THEN
Break the values into multiple parts and embed each one inside a phrase.
Create a variable to extract the parts from the phrases.
Reconstruct the value inside the code.
ELSE
Do nothing.
ENDIF

Create script files from the clear text.
Wrap the script.
Run the wrapped script.
________________________________________
2.7 Convert Derived Grants to Direct Grants

Background

When you grant a privilege, you can optionally use the with grant option clause so that the grantee can grant it further. Here is an example on grants on a table TAB1 owned by user A.

Step1
Connect A/******
Grant select on tab1 to B;

Step 2
Connect B/******
Grant select on a.tab1 to C;

The user receives the error

ORA-01031: insufficient privileges
due to the fact that user B does not have the privileges to grant the privilege it itself received from someone. However, in step 1, if the statement were
Grant select on tab1 to b with grant option;
then user B would have had the privileges to grant it further, and step 2 would have been successful.

Similarly, B can also grant it C with grant option, who in turn can grant it to D and so on.
On the surface it sounds like a good plan. The original owner A does not need to worry about to whom to grant or revoke privileges; the process is self-managed on an as-needed basis. So what's the problem?
Well, consider this scenario:
Connect A/*****
Revoke select on tab1 from B.
Remember, C got its privileges on TAB1 from B, not directly from A; so what happens to its privileges now that B lost them? C loses its privilege as well, since it was a derived one.
Furthermore, suppose A has granted select on TAB1 to C directly. Now C has two grants on TAB1—one from B and one from A. When you revoke a privilege, the other one is still in effect, making you falsely believe that the privilege is not present.
Despite its ostensible elegance, this process actually creates confusion and security holes and introduces difficult-to-track bugs. It makes much more sense to grant the privileges directly without an intermediary.
Strategy
Your goal is to identify which privileges were granted via another user and then do direct grants instead. These are clearly visible from the view DBA_TAB_PRIVS, where the column grantor shows the user that granted the privilege.
SQL> col grantee format a15
SQL> col privilege format a15
SQL> col owner format a20
SQL> col table_name format a20
SQL> select grantee, privilege, owner, table_name
2 from dba_tab_privs
3* where grantor != owner
4 /
A sample output is shown below.
PUBLIC EXECUTE XDB.DBMS_XMLSCHEMA SYS
PUBLIC EXECUTE XDB.XDB_PRIVILEGES SYS
PUBLIC EXECUTE XDB.DBMS_XMLSCHEMA_INT SYS
APP1 SELECT ANANDA.MP RUSER
The first three lines can be ignored, where the grant is made to the role PUBLIC by the user SYS. The privilege is on the package DBMS_XMLSCHEMA owned by the schema XDB. Being a special supplied package by Oracle, this may be allowed; but the fourth line clearly needs attention. The table MP, owned by ANANDA, has been granted by RUSER and should be corrected. The fix is really simple: Grant select on the object to APP1 directly, even if RUSER has a with grant option privilege.
There are two ways to do that:
1. Owner of the object grants it directly
2. A superuser such as SYS grants it
The second option is easier to implement. The SYS user does not really inherit the grants; it grants the privilege by using the system privilege grant any object privilege. When SYS grants the privilege as:
grant select on a.tab1 to c;
The GRANTOR column shows A, not SYS; which is exactly what you want.
set lines 300
set pages 0
spool grant_direct.sql
select 'grant '||privilege||' on '||owner||
'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantor != owner
/
spool off
Now run the file grant_direct.sql to grant the privileges directly.
After this is successful, you have to revoke the privileges you have granted indirectly. This is not possible in a single statement since you must be connected as the grantor as well.
break on conn skip 2
select 'connect '||grantor conn,
'revoke '||privilege||' on '||owner||
'.'||table_name||' from '||grantee||';' line
from dba_tab_privs
where GRANTOR != 'SYS'
and grantor != owner
order by 1,2
/
Spool this script to a file, edit it to supply the password for each user, and execute it to revoke the grants.
Implications
There are two potential implications. First, since you are revoking privileges and re-granting them, you may introduce errors by failing to re-grant the privileges. So, it's important to get a snapshot of the privileges before and after this change to confirm success. Use this script to find out the privileges:
SQL> select grantee, privilege, owner, table_name
2 from dba_tab_privs
3* where grantor != owner
4 /
Run this before and after the change, save the outputs, and compare them to make sure the privileges are intact.
The second implication is more pronounced. The grant-and-revoke cycle will make the cursors on these objects in the library cache invalid and will force the cursors to be reparsed, which will momentarily degrade performance.
In addition, some dependent objects will be invalidated. Since the privileges are re-granted, the objects will compile fine when they are referenced; but you may want to take some proactive action and recompile them beforehand.
Action Plan
1. Find out the privileges granted by others, using the grantable option.
2. Revoke the privileges.
3. Re-grant without the grant option.
4. Check for invalid objects and recompile them.
________________________________________
2.8 Limit Tablespace Quotas

Background

¿Cuánto espacio dentro de un espacio de tablas se puede utilizar un usuario, y cómo muchos de tablas se puede escribir? La respuesta depende de la cuota disponible para ser de uso en el espacio de tablas. Usted puede especificar la cuota de la siguiente manera:

alterar usuario 12M cuota ananda en los usuarios;
Esto limita la ananda usuario crear objetos almacenados, tales como tablas, índices y vistas materializadas cuyo tamaño total no exceda de 12 MB. Para confirmar o averiguar hasta qué punto el usuario ha utilizado, tema de la consulta

SQL> col used format 999,999.999 head "Used (MB)"
SQL> col quota format 999,999.999 head "Quota (MB)"
SQL> col tablespace_name format a15
SQL> select username, tablespace_name,
2 bytes/1024/1024 used,
3 max_bytes/1024/1024 quota
4 from dba_ts_quotas
5 order by username
6 /

Sample output is shown below.

USERNAME TABLESPACE_NAME Used (MB) Quota (MB)
------------------- --------------- ------------ ------------
USER1 USERS .000 100.000
USER1 APP1_INDEX 504.875 -.000
USER2 USERS .125 5.000

Esto necesita una explicación. El resultado muestra que el usuario Usuario1 tiene una cuota de 100 MB a los usuarios de tablas (como se muestra en la columna de cuota). Por esto, el usuario ha utilizado nada (como se muestra en la columna usados). La segunda línea es interesante ver cómo las columnas de cuotas muestra "-0". Indica que el usuario tiene privilegios ilimitados de tablas en ese espacio de tablas-APP1_INDEX. El USUARIO2 usuario tiene una cuota de 5 MB de espacio de tabla USUARIOS, de los cuales sólo 0.125 MB se ha utilizado.
Es el espacio de tablas sin límite que debe tener en cuenta. Un usuario se puede dar el cupo ilimitado por:
alterar cuota de usuario ananda ilimitada a los usuarios;
Sin embargo, este acto puede tener una implicación de seguridad, si un usuario regular tiene un cupo ilimitado de tablas en un espacio de tablas crítica para el negocio, el usuario podría llenar el espacio de tablas por completo, lo cual es similar a un ataque de denegación de servicio.
Un riesgo más serio es el sistema de privilegios UNLIMITED TABLESPACE, lo que permite que el usuario tenga cuota ilimitada en todos los espacios de tabla sin beca cuota específica sobre ellos. Repito: El usuario tiene cupo ilimitado en todos los espacios de tabla, incluido el sistema-por lo que el usuario puede crear objetos de allí. No es bueno.
En primer lugar, comprobar que no existen cuotas explícita de tablas de SISTEMA:

select
username,
bytes/1024/1024 used,
max_bytes/1024/1024 quota
from dba_ts_quotas
where tablespace_name = 'SYSTEM'
order by username;
If this query brings up something, you should evaluate it and if necessary revoke the quota.
The next step is to identify the users with unlimited tablespace system privileges.
select grantee
from dba_sys_privs
where privilege = 'UNLIMITED TABLESPACE';
This list should be carefully evaluated as this system privilege also includes that for SYSTEM tablespace.
Strategy
Now that you have identified all users and their quotas on tablespaces, your next task is to mitigate their risk. There are two tasks here, one more disruptive than the other.
First, try to remove the unlimited quota from the SYSTEM tablespace. This can be done without much disruption to applications. But before that, you need to make sure the SYSTEM tablespace does not contain objects from outside the SYS schema. The following query accomplishes that.
select owner, segment_type, segment_name
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');
The output is
OWNER SEGMENT_TYPE SEGMENT_NAME
--------------- --------------- --------------
OUTLN INDEX OL$HNT_NUM
OUTLN INDEX OL$SIGNATURE
OUTLN INDEX OL$NAME
OUTLN TABLE OL$NODES
OUTLN TABLE OL$HINTS
OUTLN TABLE OL$
In this case, only the OUTLN objects are in SYSTEM tablespace, which is acceptable. If you see any other object, then you should move them.
The root cause of the problem could be the
select username
from dba_users
where default_tablespace = 'SYSTEM';
It should return only the following.
USERNAME
----------
SYSTEM
SYS
OUTLN

Si se muestra otros nombres de usuario, modificar el usuario a un espacio de tablas diferentes. Por ejemplo, para cambiar el espacio de tablas por defecto de SCOTT usuario user_data, cuestión
alterar scott de usuario por defecto user_data tablas;
A continuación, mueva todos los objetos fuera del espacio de tablas del sistema.
ALTER TABLE scott.tab1 user_data mover tablas;
Ahora, su siguiente tarea consiste en garantizar la cuota es 0 para todos los usuarios en el tablespace SYSTEM. Hay dos razones subyacentes de la cuota a ser ilimitada, una de las cuales es la concesión directa de tablas ilimitada. La otra es la concesión de la RECURSOS papel, que ha UNLIMITED TABLESPACE como un privilegio del sistema en Oracle9 i base de datos y versiones anteriores. En cambio, Oracle Database 10g no tiene el privilegio del sistema que concede el papel RECURSOS.
Para la base de datos i Oracle9
Confirme que UNLIMITED TABLESPACE es de hecho el papel concedido a RECURSOS.

SQL> select *
2 from dba_sys_privs
3 where grantee = 'RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TYPE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE UNLIMITED TABLESPACE NO
If UNLIMITED TABLESPACE is not listed, you don't need to do anything at this stage. Jump ahead to "Common Tasks."
For Oracle Database 10g
Confirm that UNLIMITED TABLESPACE is not granted to the RESOURCE role.
SQL> select *
2 from dba_sys_privs
3 where grantee = 'RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TYPE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE PROCEDURE NO
Common Tasks
Identify the users with the UNLIMITED TABLESPACE privilege and alter their quota to unlimited on all tablespaces.
set lines 300
set pages 0
spool quota.sql
select 'alter user '||grantee||' quota unlimited on '||
tablespace_name||';'
from dba_sys_privs p, dba_tablespaces t
where p.grantee in (
select username
from dba_users
)
and p.privilege = 'UNLIMITED TABLESPACE'
and t.tablespace_name not in ('SYSTEM','SYSAUX')
order by grantee, tablespace_name
/
spool off
This creates a file with contents similar to
alter user ORAAGENT quota unlimited on INDEX01;
alter user ORAAGENT quota unlimited on INDEX02;
alter user ORADBA quota unlimited on INDEX02;
Next, you can execute this script file to have the unlimited quotas of these users. Finally, remove UNLIMITED TABLESPACE.
set lines 300
set pages 0
spool revoke_ut.sql
select 'revoke unlimited tablespace from '||grantee||';'
from dba_sys_privs
where privilege = 'UNLIMITED TABLESPACE'
/
spool off
Then execute this script file to revoke the privilege.
Implications
There are no implications for removing these privileges and reducing the quota to 0 on the SYSTEM tablespace. However, if you have segments in the SYSTEM tablespace and you move them to a different tablespace, there will be two consequences:
• The rowid will change because of the physical movement. If you have a rowid-based application, be aware of it.
• The indexes on the tables will become unusable—you have to rebuild them.
The change may also invalidate some dependent procedures.
Action Plan
Find out the default tablespace of users other than SYS, SYSTEM, and OUTLN.
If it's SYSTEM, change it to a non-SYSTEM tablespace.
Find out the segments in the SYSTEM tablespace belonging to users other than SYS, SYSTEM, and OUTLN.
IF found, THEN
Move them out to their tablespaces
Rebuild indexes, materialized view, etc.
ENDIF
Find out the users with UNLIMITED TABLESPACE system privilege
IF found, THEN
Grant them unlimited quota on all tablespaces except SYSTEM and SYSAUX
Revoke the system privilege UNLIMITED TABLESPACE
ENDIF
________________________________________
2.9 Monitor Listener Logs for Attempted Break-Ins
Background
In Phase 1.7, you learned how to secure the Oracle Listener by restricting the ability to change parameters online. That's fine and good, but how will you know if and when anyone tries to break in? Prevention is just part of the story; tracking the effectiveness of your defenses is just as important.
Well, you can explore attempted unsuccessful logins from the listener log file. When a user supplies the wrong password and attempts to modify the listener, the following message writes to the listener log:
12-NOV-2005 23:23:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=prolin01)(USER=ananda
))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER_PROLIN01)(VERSION=168821760)) * stop * 1190
TNS-01190: The user is not authorized to execute the requested listener command
This shows that on Nov 12 at 11:23 p.m. the user "ananda" was trying to stop the listener (note the presence of (COMMAND=stop) ) but supplied a wrong password. Could that indicate an attempted break-in? Possibly. Or perhaps ananda is a legitimate user but made a typo while entering the password, generating this error. But if you see this error many times, it may indeed signify an attempted break-in. You should visit user ananda and verify that he indeed was trying to stop the listener.
Similarly, when the admin options are restricted in the listener, your user can't just set the parameters from the command line. If the user tries a command like:
$ lsnrctl
LSNRCTL> set trc_level support
He will immediately get an error.
TNS-12508: TNS:listener could not resolve the COMMAND given
And the following entry will appear in the listener log file.
12-NOV-2005 23:26:34 * trc_level * 12508
TNS-12508: TNS:listener could not resolve the COMMAND given
This entry should tell you that someone was trying to set the trc_level directly on the LSNRCTL prompt. Again, this could be an honest mistake, but repeated attempts may indicate an attack.
Strategy
The best approach is to check the listener log files periodically for these patterns. You can do that in several ways.
First, you could write a shell script using the following code:
$ grep "(COMMAND=stop)" listener.log | cut -f4 -d"*"
The following output would indicate that the listener command STOP was issued three times:
0
0
0
You can also make this script much more informative using sophisticated tools such as awk or scripting languages like PERL. However, if SQL is most familiar to you (which is likely), using SQL to extract information from this log file will be much more attractive.
The trick here is to use the listener log as an external table. First, create a directory object on the directory where the listener log is located.
create directory listener_log_dir
as
'/u01/app/oracle/10.1/db1/network/log'
/
Next, create the external table on the log file. Note the contents of the log file carefully; it generally contains six pieces of information separated by the "*" character. These pieces will become columns of the external table.
create table listener_log
(
log_date date,
connect_data varchar2(300),
protocol_data varchar2(300),
command varchar2(15),
service_name varchar2(15),
return_code number(10)
)
organization external (
type oracle_loader
default directory LISTENER_LOG_DIR
access parameters
(
records delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by "*" lrtrim
missing field values are null
(
log_date char(30) date_format
date mask "DD-MON-YYYY HH24:MI:SS",
connect_data,
protocol_data,
command,
service_name,
return_code
)
)
location ('listener_prolin01.log')
)
reject limit unlimited
/
After the table is created, you can select from it to confirm the definition is correct.
The lines are fairly descriptive but embedded commands such as (COMMAND=stop) can make it difficult to decipher. In that case, write another function to extract the values from the strings:
create or replace function extract_value
(
p_in varchar2,
p_param in varchar2
)
return varchar2
as
l_begin number(3);
l_end number(3);
l_val varchar2(2000);
begin
l_begin := instr (upper(p_in), '('||p_param||'=');
l_begin := instr (upper(p_in), '=', l_begin);
l_end := instr (upper(p_in), ')', l_begin);
l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
return l_val;
end;
The monitoring becomes extremely easy then. All you have to do to uncover failed login attempts is issue
col l_user format a10with the embedde
col service format a20
col logdate format a20
col host format a10
col RC format a5
select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate,
extract_value (connect_data,'HOST') host,
extract_value (connect_data,'USER') l_user,
extract_value (connect_data,'SERVICE') service,
action RC
from listener_log
where extract_value (connect_data, 'COMMAND') in
(
'password',
'rawmode',
'displaymode',
'trc_file',
'trc_directory',
'trc_level',
'log_file',
'log_directory',
'log_status',
'current_listener',
'inbound_connect_timeout',
'startup_waittime',
'save_config_on_stop',
'start',
'stop',
'status',
'services',
'version',
'reload',
'save_config',
'trace',
'spawn',
'change_password',
'quit',
'exit'
)
This returns output similar to
LOGDATE COMMAND HOST L_USER SERVICE RC
-------------------- --------------- ---------- ---------- -------------------- -----
10/02/05 02:57:36 stop prlddb01 oraprld LISTENER_PRLDDB01 0
10/02/05 04:47:03 stop prlddb01 oraprld listener_prlddb01 0
10/03/05 15:14:53 stop prlddb01 oraprld LISTENER_PRLDDB01 0
11/18/05 23:48:26 reload prlddb01 oraprld LISTENER_PRLDDB01 0

Como puede ver, el resultado muestra la fecha y hora de la orden, junto con los códigos de retorno. También puede modificar la consulta para mostrar sólo aquellos valores donde el código de retorno no es 0. También puede agregar un predicado para mostrar los registros después de cierta fecha sólo, de modo que los intentos por hoy sólo se muestran. Si ejecuta esta secuencia de comandos de todos los días, usted puede ver los intentos de ese día solamente.
Los datos muestran por encima de las contraseñas no válidos solamente. Para los oyentes de administración de restricción, la cadena de error muestra sólo tres campos y por lo tanto las columnas de la tabla LISTENER_LOG tienen significados diferentes: La segunda columna muestra el comando emitido por el usuario y la tercera columna se muestra el código de retorno.

select
log_date,
connect_data command,
protocol_data return_code
from listener_log
where connect_data in
(
'password',
'rawmode',
'displaymode',
'trc_file',
'trc_directory',
'trc_level',
'log_file',
'log_directory',
'log_status',
'current_listener',
'inbound_connect_timeout',
'startup_waittime',
'save_config_on_stop',
'start',
'stop',
'status',
'services',
'version',
'reload',
'save_config',
'trace',
'spawn',
'change_password',
'quit',
'exit'
)
/
This returns:
LOG_DATE COMMAND RETURN_CODE
--------- -------------------- ---------------
06-NOV-05 change_password 0
06-NOV-05 save_config 0
06-NOV-05 log_file 0
06-NOV-05 trc_level 12508
06-NOV-05 save_config_on_stop 12508
06-NOV-05 log_directory 12508
06-NOV-05 log_directory 12508
06-NOV-05 stop 1169
06-NOV-05 stop 1169
06-NOV-05 services 1169
06-NOV-05 status 1169
06-NOV-05 reload 1169
06-NOV-05 status 1169
06-NOV-05 stop 1169
06-NOV-05 status 1169
06-NOV-05 stop 1169
Implications
None; this activity is merely diagnostic.
Action Plan
1. Create the Listener Log external table.
2. Select records where administrative commands have been issued without a password, identifiable by a non-zero return code.
3. Select records where commands were issued on the listener control prompt.
4. If records are found that can't be explained by activities by any of the DBAs, you may have identified foul play.
________________________________________
2.10 Audit and Analyze User Access
Background
How well do you know your users? Do you know which machines they connect from, what they do when they connect, and so on? Probably not. But keep in mind that a successful security plan involves understanding these details, or at least the important ones. This is where the auditing facility in Oracle Database becomes very useful.
The auditing functionality in Oracle Database is quite comprehensive. Here you need to enable just a portion of that functionality to create a "profile" of the database. All you will attempt to do is see the users connecting, the userid they use to connect, and the type of authentication used. You will also uncover invalid login attempts—for instance, when the userid/password combination was wrong. As discussed previously, finding patterns to these unusual events may provide clues to uncovering a potential attack.
Strategy
To enable auditing, set the following parameter in the database initialization parameter file.
audit_trail = db
This is a static parameter; you must recycle the database for it to take effect. After this is done, issue the following for the auditing to kick in.
AUDIT SESSION;
This command will create a record whenever a user logs in or logs out. An audit trail will be created even if the login attempt was unsuccessful. After the database runs for a while, you can look for patterns in the audit trail. The column RETURNCODE records the Oracle Error Code the user received while making the operation.
SQL> select returncode, count(1)
2 from dba_audit_trail
3 group by returncode
4 /

RETURNCODE COUNT(1)
---------- ----------
0 1710880
604 3
955 17
987 2
1013 2
1017 1428
1045 1
1555 4
1652 4
1950 1
2002 1
2004 4
28000 4
28009 3
This clearly shows a pattern of errors; most of the operations were successful (where the return code is 0). For the rest of the codes, you can get descriptions by issuing
oerr ora
from the *nix prompt. For instance, to find out what error code 1017 means, issue
oerr ora 1017
this returns
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
This, the most common error, is the target for your analysis as it will reveal attack patterns most effectively. A high incidence of invalid/password combinations may indicate an attempted break-in.
Now you should see where these sessions were coming from. Invalid passwords on a particular userid might indicate an attack on that userid. You can see the userids by:
select username, count(1)
from dba_audit_trail
where returncode = 1017
group by username
order by 2 desc;
The output shows something similar to the following:
USERNAME COUNT(1)
------------------------------ ----------
ARAO 569
DBSNMP 381
DW_DQS 181
Here we see that a user ARAO, apparently a human one, has attempted to use an invalid password 569 times. The next userid, DBSNMP (381 invalid password attempts), is not a human user; it's the userid of Enterprise Manager. This should immediately raise alarm signals—DBSNMP is a favorite hacker target.
To examine it more closely, let's see where these attacks are coming from:
select userhost, terminal, os_username, count(1)
from dba_audit_trail
where returncode = 1017
and username = 'DBSNMP'
group by userhost, terminal, os_username;
the output is:
USERHOST TERMINAL OS_USERNAM COUNT(1)
------------------------- --------------- ---------- ----------
prlpdb01 oraprlp 199
prlpdb01 pts/2 oraprlp 4
prlpdb01 pts/7 oraprlp 9
prlpdb02 oraprlp 130
PRONT\PRANANDAT42 PRANANDAT42 ananda 3
progcpdb unknown oracle 34
Notice that the server where this database is running is prlpdb01. Since this is a RAC database, the second node is also present, and the server name is prlpdb02. Most of the bad connection attempts have come from these servers, and using the OS user (oraprlp), which is the Oracle software owner. If this was indeed an attack, the user has access to the Oracle software owner and could have logged in as SYSDBA. There was no need to login as DBSNMP, and clearly the password was wrong. So, it does not look like an attack.
You can also see that the invalid logins are coming from two other machines: PRONT\PRANANDAT42 and progcpdb. They may seem suspicious, we can confirm the identities of these machines—the first one belongs to a DBA named "ananda" and the other one is the Grid Control server, which is expected to connect using this userid.
Next, analyze the pattern of these failures. If they are clustered around a specific time, it could indicate an attack.
SQL> select to_char (timestamp,'mm/dd/yy') ts, count(1)
2 from dba_audit_trail
3 where returncode = 1017
4 and username = 'DBSNMP'
5 group by to_char (timestamp,'mm/dd/yy')
6 /

TS COUNT(1)
-------------------- ----------
10/14/05 9
10/16/05 222
10/27/05 15
10/28/05 125
11/09/05 4
11/11/05 2
11/12/05 2
11/14/05 2
As you can see, there are two distinct clusters: on 10/16 and on 10/28. You should mount a full investigation.
Implications
Auditing may have minimal impact on performance; but it still has some impact. Furthermore, remember that the audit trails are written into the tablespace SYSTEM, which may get filled up. So you have to be vigilant for free space inside SYSTEM tablespace.
Action Plan
1. Turn on auditing by placing AUDIT_TRAIL initialization parameter.
2. Enable auditing for sessions.
3. Look for invalid or unsuccessful attempts to log in.
4. Examine the unsuccessful attempts for patterns (clusters of dates).
________________________________________
The content provided here is for instructional purposes only and is not validated by Oracle; use it at your own risk! Under no circumstances should you consider it to be part of a consulting or services offering.
Go on to Phase 3
Read the Project Lockdown TOC and Security Primer

Series: Project Lockdown
A phased approach to securing your database infrastructure

Phase 3
Duration: One Month

It’s Phase 3 of your security and compliance project. Let’s see what you can do within about a month to lock down your database infrastructure..

Updated August 2010

Download: Phase 3 Checklist (PDF)

Covered in this Installment:
• 3.1 Remove Passwords from Scripts
• 3.2 Remove Password from Oracle RMAN
• 3.3 Move DBA Scripts to Scheduler
• 3.4 Lock Down Objects
• 3.5 Create Profiles of Database Users
• 3.6 Create and Analyze Object Access Profiles
• 3.7 Enable Auditing for Future Objects
• 3.8 Restrict Access from Specific Nodes Only
• 3.9 Institute Periodic Cleanup of Audit Trails

3.1 Remove Passwords from Scripts

Background
Some of your most serious potential threats arise from the use of hard-coded passwords in applications, programs, and scripts. In many cases, eliminating those passwords is a trivial exercise that will have an immediate impact.
For example, in many installations I have audited, a STATSPACK job runs as a shell script such as this:
export ORACLE_HOME=/u01/app/oracle/10.1/db
export ORACLE_SID=MYSID
cd $ORACLE_HOME/bin
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat @statspack.sql

The shell script is then scheduled through cron or Windows Scheduler. There are two major risks to this approach:
• An intruder may find the file statspack.sh and see the password of the user PERFSTAT
• When the shell script is running, someone on the *nix server can issue a ps -aef command and see the command running—with the password clearly visible.
When I ask the reason for this approach, the answer is almost universally the same: because the previous DBA did it that way. Whatever the reason, the threat is clear and present and must be eliminated.
Strategy
You have several options for eliminating the exposure of passwords. Don’t be fooled into believing that the following will hide one:
sqlplus -s scott/$SCOTTPASSWORD @script.sql

where SCOTTPASSWORD is an environmental variable defined prior to the execution of the script. When a user issues the command /usr/ucb/ps uxgaeww, he can see all the environmental variables used in the process that will expose the password. Therefore, you have to literally hide the password in some manner. Let’s explore the options.
Option 1. One simple option is to use the nolog parameter in SQL*Plus. In this option, the previous script would change to the following:
export ORACLE_HOME=/u01/app/oracle/10.1/db
export ORACLE_SID=MYSID
cd $ORACLE_HOME/bin
$ORACLE_HOME/bin/sqlplus /nolog @statspack.sql

This script allows a SQL*Plus session to be established but not yet connected to the database. Inside the script statspack.sql, you should place the userid and password:
connect perfstat/perfstat
... the rest of the script comes here ...

Thus, if someone issues ps -aef, he will not see the userid or the password. (By the way, this is also best practice for initiating SQL*Plus sessions interactively.)
Option 2. This small variation of the above approach is useful in cases where SQL statements are not in a SQL script but rather embedded in the shell script directly, as in this example:
$ORACLE_HOME/bin/sqlplus user/pass << EOF
... SQL statements come here ...
EOF

You can change the shell script to the following:
$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect user/pass
... SQL statements come here ...
EOF

Again, this is a good practice for building quick-and-dirty shell scripts that process SQL.
Option 3. In this option, you can create a password file containing userids and passwords and read them at runtime. First, create a file named .passwords (note the period before the filename) containing all userids and passwords. Because the filename starts with a period, it will be invisible when someone uses the ls -l command (but it will be visible with ls -la). Then change the permissions to 0600 so that only the owner can read it.
The file should contain the userids and passwords (one per line) separated by a space:
scott tiger
jane tarzan
... and so on ...

Now create another file, a shell script named .getpass.sh (note the period again), with the following lines:
fgrep $1 $HOME/.passwords | cut -d " " -f2

Make the permissions of this script 0700 so that no one else can see and execute it. Subsequently, when you need to run a script as SCOTT all you have to do is code the lines like this:
.getpass.sh scott | sqlplus -s scott @script.sql

This will get the password of scott from the file and pass it to the sqlplus command. If someone issues the ps command, he will not be able to see the password.
As a fringe benefit, you have now enabled a flexible password management system. Whenever scott’s password changes (and it should change periodically as a good practice), all you have to do is edit the password file.
Option 4. This is where the OS-authenticated users come into the picture. (You learned about them in Phase 2.) As a recap, if you have a *nix user named ananda you can create an Oracle user as
create user ops$ananda identified externally;

Here the user can log in to the database as
sqlplus /

Note that there is no userid and password. The database does not actually authenticate the user; it assumes that user ananda has been properly authenticated at the OS level. Although this is not a great practice, it can be an attractive one—for shell scripts, for example. Your script could look like this:
sqlplus -s / @script.sql

Because neither the username nor the password is passed, there is no risk of their exposure via the ps command.
Implications
None. Replacing hard-coded passwords with a password management system does not affect the functionality of scripts, just how the password is supplied. You do however have to make sure to back up the password file or keep copies of it, as well as keep them up to date.
Action Items
1. Identify scripts with hard-coded passwords.
2. Pick an option for implementation:
a. Use the Connect command inside the SQL script
b. Use the Connect command inside the shell script (no SQL script).
c. Use a password file.
d. Use OS-authenticated accounts.
3. Modify the scripts to remove the passwords.

3.2 Remove Password from Oracle RMAN

Background
Hard-coded passwords are not limited to scripts. Oracle Recovery Manager (RMAN) scripts are susceptible to the same bad habits.
Here is a typical Oracle RMAN command inside a script for making a backup:
rman target=/ rcvcat=catowner/catpass@catalog_connect_string

Here the catalog database is referenced in the connect string catalog_connect_string and the userid and password of the catalog are catowner and catpass, respectively. The userid and password are clearly visible if someone issues a ps command, just as previously described.
You have a couple of options for removing these hard-coded passwords:
Option 1. In this option, use the connect string inside the RMAN script like this:
connect target /
connect catalog catowner/catpass@catalog_connect_string
run {
allocate channel ...
... and so on ...

This is clearly the preferred method—not only because it hides the password from the process listing, but also because it makes it easier to examine mistakes in the catalog connect strings.
Option 2. The other option is to use OS authentication for the catalog connection. You need to make a few additional changes, though. First, the catalog is probably on a different database than the one being backed up. To enable OS authentication in that case, you need to enable remote OS authentication on the catalog database.
On the catalog database, add the following initialization parameter and then restart:
remote_os_authent=TRUE

Now create a userid OPS$ORACLE as follows (on the catalog database):
create user ops$oracle identified externally;

Now your RMAN connection will look like this:
rman target=/ rcvcat=/@catalog_connect_string

This will not reveal the catalog user or password.
As you now connect as OPS$ORACLE and not catowner, you must rebuild the repository. After connecting, use the command
RMAN> register database;

to rebuild the catalog. You are now ready to use this script in RMAN backups.
Implications
There are a few implications here. First, the change in the catalog database exposes it to access from an outside server by anyone with login “oracle.” This is a serious security hole in itself; because you can’t control the clients, someone could infiltrate a client server—possibly via an inherently insecure operating system—create an id called “oracle,” and log into the catalog database.
You can prevent such an infiltration in several ways. The simplest method is to enable node filtering (discussed in section 3.8) to prevent any random server from connecting to this node.
Also be aware of the possibility of a different name for the Oracle software owner on the source database server. If you use “orasoft” on database server A and “oracle” on database server B, the users you need to create on the catalog database are OPS$ORASOFT and OPS$ORACLE, respectively—thus there will be two repositories, not one. This is not necessarily a bad thing, but if you want to report on backups from the catalog, you will have to know all the repositories in the database.
Action Plan
1. Pick a method for hiding the RMAN catalog owner password:
a. Connect command inside the script
b. Connect as OS-authenticated user
2. IF b., then
. Enable remote OS authentication on the catalog database (requires restart).
a. Enable node validation in the catalog database to reject all nodes except the known few.
b. Create users in the name OPS$ORACLE.
c. Modify RMAN catalog connect string to use OPS$ORACLE.
3. Rebuild catalog.
3.3 Move DBA Scripts to Scheduler

Background
What about those rather common DBA scripts that require a database login—for statistics collection, index rebuilding, and so on? Traditionally, DBAs use the cron (or AT, in Windows) job approach to run jobs, but there are two risks here:
1. If this script needs to log in to the database—and most DBA scripts do—the userid and passwords must be placed in the script or somehow passed to the script. So, anyone with access to this script will be able to learn the password.
2. Worse, anyone with access to the server can issue a ps -aef command and see the password from the process name.
You need to protect the password from being exposed.
Strategy
You can follow the same strategy as previously described, by passing the password in the SQL script or letting the shell script read it from a text file. That approach will prevent someone from learning the password in the ps -aef output; however, it will not address the problem of someone accessing the scripts.
In Oracle Database 10g Release 1 and later, you have an elegant way to manage this process via database jobs. Note that prior to Oracle Database 10g, database jobs were managed via the dbms_job supplied package, which could execute a PL/SQL package, code segment, or just plain SQL but not OS executables. In Oracle Database 10g, the new job management system is the supplied package dbms_scheduler (“Scheduler,” for short). In addition to offering a significantly improved user interface, this tool has a great advantage: It can execute even OS executables—all from within Oracle Database. This capability makes it super-easy to implement a one-stop job management system.
Here’s an example. Suppose you have a cron job that collects optimizer statistics, as shown below:
00 22 * * 6 /proprd/oracle/dbawork/utils/bin/DbAnalyze
-d PROPRD1 -f DbAnalyze_PROPRD1_1.cfg 2>&1 1> /tmp/DbAnalyze1.log

As you can see, this job:
• Runs a program named /proprd/oracle/dbawork/utils/bin/DbAnalyze -d PROPRD1 -f DbAnalyze_PROPRD1_1.cfg
• Runs at 22 minutes past midnight, every Saturday
• Writes output to the file /tmp/DbAnalyze1.log

Now, to convert this to a Scheduler job, you would use the following code segment:
1 BEGIN
2 DBMS_SCHEDULER.create_job
3 (job_name => 'Dbanalyze',
4 repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT BYHOUR=0 BYMINUTE=22',
5 job_type => 'EXECUTABLE',
6 job_action => '/proprd/oracle/dbawork/utils/bin/DbAnalyze -d
PROPRD1 -f DbAnalyze_PROPRD1_1.cfg',
7 enabled => TRUE,
8 comments => 'Analyze'
9 );
10 END;

The arguments of the procedure are self-explanatory; the calendaring syntax is almost English-like. (For more details about Scheduler, read this OTN article or my book Oracle PL/SQL for DBAs (O’Reilly Media, 2005)
Why bother with Scheduler when good old cron is available? For several reasons, actually:
• Scheduler runs jobs only when the database is available, which is a great feature for supporting database-centric jobs. You don’t need to worry about checking if the database is up.
• Scheduler syntax is consistent across OSs. If migrating, all you need to do is move your code to the new server and a very simple export-import will enable your scheduled jobs.
• Like objects, Scheduler jobs are owned by users, which allows you to enable finer levels of privilege such as execution of a job by a separate user—unlike cron, which is generally used by a single Oracle software owner account.
• Best of all, because you don’t have to place a password anywhere, there is no risk of accidentally exposing one. Even the SYS user will not be able to know the password of the user, because it’s not stored anywhere. This ability makes Scheduler an attractive tool for managing DBA (or even regular users’) jobs very securely.
• As a fringe benefit of the above, you don’t need to worry about changes, such as when user passwords change.
Implications
There are none, as long as the jobs are database dependent. Of course, certain jobs must run even when the database is unavailable—such as jobs to move the alert log to a different location or to check if the database is up and running. These jobs should stay outside the database and within cron.
Action Plan
1. Identify database jobs in cron.
2. Decide which jobs should run even when the database is down (example: jobs that move the listener logs to a different location every day).
3. For the rest of the jobs, create Scheduler jobs that are initially disabled by stating ENABLED=FALSE in the CREATE_JOB procedure.
4. Test the execution of the jobs using the dbms_scheduler.run_job() procedure.
5. If successful, turn off the cron job and enable the Scheduler job using the dbms_scheduler.enable() procedure.

3.4 Lock Down Objects

Antecedentes

Objetos de programación, tales como paquetes, procedimientos, funciones, órganos, tipo y objeto de incorporar los métodos de la lógica de negocio de su organización. Cualquier cambio que les puede afectar a la lógica de procesamiento general, y dependiendo de cómo esté configurado, el impacto en un sistema de producción puede ser catastrófico.
Muchas organizaciones de abordar esta cuestión mediante la aplicación de un proceso seguro de cambio de control cuando el cambio es discutido y aprobado, idealmente, entre al menos dos personas-antes de su aplicación. El desafío es hacer que el sistema funcione de forma automática-que en realidad es un requisito en muchas jurisdicciones y sectores.

Estrategia

This secure change-control process could work like this:
• An application super-owner (this could be the DBA, if needed) “unlocks” the program to be altered.
• The application owner alters the program body.
• The super-owner locks the program.

Considerando que la base de datos Oracle no contiene un mecanismo de bloqueo nativo para el lenguaje de definición de datos (DDL), ¿cómo implementar este proceso?
Una opción es crear revocar privilegios de sesión de sistema desde el propietario del esquema para que el propietario del esquema no puede acceder a hacer un cambio. En cambio, los cambios se hacen de particular a particular aplicación en seres humanos con privilegios para modificar el objeto del esquema especificado. Este es un método muy bueno para conseguir objetos críticos de base de datos, haciendo posible la creación de pistas de auditoría de los cambios realizados a los objetos, donde los senderos se remonta a los usuarios humanos reales, no los nombres de esquema genérico.
Por ejemplo, supongamos que el esquema es el banco y el nombre de la tabla es CUENTAS. Al revocar privilegios de crear período de sesiones del banco, que le impiden hacer login en la base de datos. En su lugar, permitir que Scott, quien ha crear privilegio período de sesiones, para modificar las cuentas. El SCOTT usuario de Oracle es en realidad propiedad del usuario humano real Scott, y nadie más tiene acceso a este identificador de usuario. Los cambios realizados en las cuentas por SCOTT se pueden atribuir directamente al usuario Scott, por lo que la rendición de cuentas un componente clave de su infraestructura de seguridad posible.

En general, para bloquear el programa en este enfoque, se debe revocar el privilegio de Scott. Cuando surge la necesidad de modificar el programa, se puede conceder de nuevo, permitiendo a Scott a cambiar el programa, y luego revocar la concesión.
Huelga decir que esto no es una elegante manera de manejar la seguridad. Pronto se encontrará con problemas en la gestión de privilegio, no es tan simple como "un usuario por objeto." En una infraestructura de base de datos típica, cientos de usuarios se concederán varios tipos de privilegios que tal vez miles de objetos. La revocación de los privilegios, se borrarán las dependencias complejas y causar grandes dolores de cabeza gestión.
Una solución más manejable es el uso de los desencadenadores DDL. Con este enfoque, se puede establecer la subvención, según los cambios necesarios, pero el control a través de los desencadenadores DDL.
Por ejemplo, supongamos que desea obtener un paquete llamado SECURE_PKG en el esquema de ARUP. Se podría crear un esquema desencadenador DDL de la siguiente manera:

1 create or replace trigger no_pkg_alter
2 before ddl
3 on arup.schema
4 begin
5 if (
6 ora_dict_obj_name = 'SECURE_PKG'
7 and
8 ora_sysevent = 'CREATE'
9 )
10 then
11 raise_application_error (-20001,'Can''t Alter SECURE_PKG');
12 end if;
13 end;
14 /

In lines 6 and 8, you are checking if a change is made to the package. Remember, changes to packages are made by the create or replace package statement; hence the event checked for is create. If you want to secure a table from alterations, you can use alter in this value. In line 11, an error is raised when the package is altered.
Once this trigger is in place, when a user with privileges tries to alter this package, or even the owner of the object (ARUP) tries to recreate the package by running the package creation script
create or replace package secure_pkg
he will get this error:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Can't Alter SECURE_PKG
ORA-06512: at line 8

If you definitely want to modify this package, you can ask the DBA to unlock it by disabling the trigger:
alter trigger no_pkg_alter disable
/

Now the package creation script will go through. When done, lock it by asking the DBA to enable the trigger. The underlying privileges remain intact. Even when you allow the schema owner to log in and modify objects they own, this method will protect the objects as well. This strategy enables a two-person approach to change management.
Implications
There are none, provided that everyone is aware that the DBA must unlock the object when it’s ready for a change. If you make this step part of the formal change-control process, it will affect reliability in the most positive manner.
Action Plan
1. Make a list of all objects that should be locked down. Note that not all objects need to be under such strict control (temporary tables created by the application owner to hold intermediate values, for example).
2. Create the trigger with all these object names in the list. Make the trigger initially disabled. Do not add this functionality to an existing trigger. You should be able to control this trigger independently.
3. Identify a person who should unlock objects. It could also be you.
4. Document when objects should locked and unlocked, workflow, and so on.
5. Enable the trigger.

3.5 Create Profiles of Database Users

Background
The design of any security system should start with a thorough and accurate understanding of the users accessing it as well as their modes of access—otherwise, you have no comparative baseline. As a DBA, you should have knowledge about your users and their applications and mechanism of access (such as the origin of the access, the DDL involved, and so on) anyway.
Strategy
This is where Oracle auditing comes in extremely handy. You don’t need to write extensive logon/logoff triggers or complex pieces of PL/SQL code. Rather, simply enable auditing by placing the following parameter in the database initialization parameter file:
audit_trail = db

and recycle the database. Once set, issue the following command as a DBA user:
audit session;

This statement enables auditing for all session-level activities such logons and logoffs. At the bare minimum, it shows who logged in, when, from which terminal, IP address, host machine, and so on.
After turning auditing on, you can monitor activity by issuing the following SQL statement
select to_char(timestamp,'mm/dd/yy hh24:mi:ss') logon_ts,
username,
os_username,
userhost,
terminal,
to_char(logoff_time,'mm/dd/yy hh24:mi:ss') logoff_ts
from dba_audit_trail
where logoff_time is not null;

Here is sample output:
LOGON_TS USERNAME OS_USERNAM USERHOST TERMINAL LOGOFF_TS
------------------------- --------------- ---------- --------------- --------------- ------------------
01/11/06 20:47:06 DELPHI sgoper stcdelpas01 unknown 01/11/06 20:48:46
01/11/06 20:48:21 DELPHI sgoper stcdelpas01 unknown 01/11/06 20:48:38
01/11/06 20:48:41 DELPHI sgoper stcdelpas01 unknown 01/11/06 20:49:19
01/11/06 20:36:03 STMT crmapps prodwpdb pts/3 01/11/06 20:36:03
01/11/06 20:36:04 STMT crmapps prodwpdb pts/3 01/11/06 20:37:40

Here you can clearly see the userids that connect to the database, their OS userids (OS_USERNAME), and the time they logged off. If they connected from the same server as the database server, their terminal id is shown in the TERMINAL column (pts/3). If they connect from a different server, it shows up in the USERHOST column (stcdelpas01) .
Once you understand how the information is presented, you can construct queries to get more useful information. For instance, one typical question is, “Which machines do users usually connect from?” The following SQL statement gets the answer quickly:
select userhost, count(1)
from dba_audit_trail
group by userhost
order by 2
/

Sidebar: Auditing Best Practices

Here you have learned how to use auditing to enforce some rudimentary levels of accountability. This approach has one serious limitation, however: You have to enable auditing by setting the initialization parameter AUDIT_TRAIL. This parameter is not dynamic; so to enable it, you must recycle the database. But if scheduling the required outage is difficult—and for many DBAs, it is—what are your options?

When I create a database, I always set the parameter to DB (for Oracle9i and earlier), DB_EXTENDED (for Oracle Database 10g and 11g Release 1) and DB, EXTENDED (for Oracle Database 11g Release 2). But wait—doesn’t that enable auditing and fill up the AUD$ table in the SYSTEM tablespace, eventually causing the database to halt?

No. Setting the AUDIT_TRAIL to a value does not enable auditing. It merely prepares the database for auditing—by specifying a location where the trails are written, such as to the OS or the database, the amount and type of auditing done, and whether the format is XML (introduced in Oracle Database 10g Release 2).

To enable auditing, you have to use the AUDIT command on an object. For example, use the following command to start auditing on the table credit_cards:
audit select, insert, update, delete on ccmaster.credit_cards;

Setting the parameter AUDIT_TRAIL while creating the database also allows you to capture auditing for reasons other than security—such as collecting information on CPU and IO used by sessions that will be an input to the Resource Manager. So, next time you bounce the database, place the AUDIT_TRAIL parameter first.
Oracle Database 11g Release 2 introduced the audit trail purge, which allows you to maintain a sizeable audit trail.

A sample output may look like this (toward the end of the output):
USERHOST COUNT(1)
--------------- ----------
stccampas01 736
prodwpdb 1235
stcdelpas01 2498

This is revealing—as you can see, most of the connections come from the client machine stcdelpas01, but the next maximum number of connections comes from prodwpdb, which is the name of the database server itself. This could be a surprise, because you may have assumed that the connections originate externally.

Well, the next question could be, “Who is accessing the database from the server directly?” Another piece of SQL gets you that information:
select os_username, username, count(1)

from dba_audit_trail

where userhost = 'prodwpdb'

group by os_username, username

order by 3

/

Here is sample output:
OS_USERNAME USERNAME COUNT(1)

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

oracle SYS 100

oracle DBSNMP 123

oracle PERFSTAT 234

infrap DW_ETL 1986

This output clearly reveals that OS user infrap runs something on the server itself and connects as DW_ETL. Is this normal? Ask the user. If you don’t get a reasonable response, it’s time to dig around some more. The point is to know everything about your users: who they are, where they are coming from, and what they do.
Another crucial piece of information you can obtain from audit trails is evidence of potential past attacks. When an adversary mounts an attack, he may not get the password right every time—so he may resort to a brute-force approach wherein he tries to repeatedly guess the password and then log in.
You can detect such an attack by identifying patterns of username/password combinations from the audit trail. The clue is the column RETURNCODE, which stores the Oracle error code the user raised when the connection attempt was made. If a wrong password were supplied, the user would raise the following:
ORA-1017: invalid username/password; logon denied

So, you should look for ORA-1017, as in the following SQL statement:
select username, os_username, terminal, userhost,

to_char(timestamp,'mm/dd/yy hh24:mi:ss') logon_ts

from dba_audit_trail

where returncode = 1017;

Here is sample output:
USERNAME OS_USERNAM TERMINAL USERHOST LOGON_TS

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

WXYZ_APP pwatson STPWATSONT40 STPWATSONT40 01/11/06 10:42:19

WXYZ_APP pwatson STPWATSONT40 STPWATSONT40 01/11/06 10:42:28

WXYZ_APP pwatson STPWATSONT40 STPWATSONT40 01/11/06 10:43:11

PERFSTAT oracle pts/5 prodwpdb 01/11/06 12:05:26

ARUP pwatson STANANDAT42 STPWATSONT40 01/11/06 14:09:20

ARUP pwatson STANANDAT42 STPWATSONT40 01/11/06 14:23:41

Here you can clearly see who has attempted to connect with a wrong password. Many of the attempts could be honest mistakes, but others may require investigation. For instance, the OS user pwatson has repeatedly tried (and failed) to log in as WXYZ_APP from the same client machine in a short span of time. Immediately thereafter, pwatson attempted to log in as the user ARUP. Now, this is suspicious. Remember, most attacks come from legitimate users within the organization, so no pattern is worth glossing over.
Along the same lines, you can monitor attempted logins with presumably “guessed” userids.
select username from dba_audit_trail where returncode = 1017

minus

select username from dba_users;

The output:

USERNAME

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

A

SCOTT

HR

Here someone has attempted to log in as a user that does not exist (SCOTT, which you have judiciously dropped from the database). Who could that be? Is it an innocent user expecting erroneously to connect to the development database, or is it a malicious user probing to see if SCOTT exists? Again, look for a pattern and identify the human user who made this attempt. Don’t be satisfied until you get a satisfactory explanation.
Implications
Turning on auditing definitely affects performance. But the rudimentary level of auditing you have enabled in this step will have negligible performance impact, with benefits far outweighing the costs.
The other impact you should consider carefully is the storage of audit trails. The audit trail entries are stored in the SYSTEM tablespace, which grows in size as the audit trails lengthen. If the SYSTEM tablespace fills up and there is no more space for audit records, all database interactions fail. Therefore, you have to be vigilant about free space.
Action Plan
1. Set the initialization parameter audit_trail to DB in the database and recycle it.
2. Enable auditing for sessions.
3. Extract information from audit trails and analyze it for patterns of attacks.
3.6 Create and Analyze Object Access Profiles

Background
Merely knowing usernames and associated properties such as OS usernames, the terminals they connect from, and so on is not sufficient, however. To properly lock down the database, you also have to know what users are accessing. This information allows you to create an object “access profile”—any deviation from which may indicate attack or intrusion.
Strategy
Again, the power of auditing is useful here. In the previous step, you enabled session-level auditing, which allows you to see session details. Now you have to enable object access auditing.
You may choose, for example, to audit access to objects that are very sensitive—such as tables where credit card numbers are stored, or procedures that return clear text credit-card numbers from encrypted values.
Suppose you want to audit anyone accessing the table credit_cards, owned by ccmaster. You could issue
audit select on ccmaster.credit_cards by access;

Subsequently, anyone who selects from that table will leave an audit trail.
You can record the information in two ways. In the first approach, demonstrated above in the keyword by “access,” a record goes into the audit trail whenever someone selects from the table. If the same user selects from the table twice in the same session, two records go into the trail.
If that volume of information is too much to handle, the other option is to record only once per session:
audit select on ccmaster.credit_cards by session;

In this case, when a user selects from the table more than once in a session, only one record goes into the audit trail.
Once auditing is enabled, you can analyze the trail for access patterns.
The trail looks different in each of the approaches. If auditing is enabled by session, you will see one record per session per object. The column action_name in this case will show SESSION REC, and the actions will be recorded in the column ses_actions.
select username, timestamp, ses_actions

from dba_audit_trail

where obj_name = 'CREDIT_CARDS'

and action_name = 'SESSION REC'

/

The output:
USERNAME TIMESTAMP SES_ACTIONS

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

ARUP 16-JAN-06 ---------S------

Of course, you can also use other columns, such as os_username, terminal, and so on.
Note how the column ses_actions shows a series of hyphens and a letter S. This indicates that the user ARUP has performed certain actions that are recorded in the single record of the audit trail. The value follows a pattern, where each position indicates a specific action:

Position Action
1 Alter
2 Audit
3 Comment
4 Delete
5 Grant
6 Index
7 Insert
8 Lock
9 Rename
10 Select
11 Update
12 References
13 Execute
14 Not used
15 Not used
16 Not used

In the above example, ses_actions shows the following:
---------S------

The S is in the 10th position, meaning that the user ARUP selected from the table credit_cards. However, it does not show how often the user selected from this table in this session, because you enabled for session only, not for access. If the user had also inserted and updated in the query, the column value would be this:
------S--SS-----

Note there are Ss in the 7th (Insert), 10th (Select), and 11th (Update) positions.
Why the letter S? It means that action by the user was successful. When you enable auditing, you can specify if the trail is to be recorded when the access was successful or unsuccessful. For instance, to record an audit trail only when the access failed for some reason (such as insufficient privileges), you can enable auditing as follows:
audit select on ccmaster.credit_cards by session whenever not successful;
Subsequently, when user ARUP successfully selects from the table, there will be no records in the audit trail. If the access is unsuccessful, there will be a record. The letter in the column ses_actions in that case would be F (for failure).
Similarly, if you want to audit only when the access is successful, you would substitute the clause “whenever not successful” to “whenever successful.” By default, both successful and unsuccessful accesses are logged if you do not specify the clause. So what happens if in a single session some accesses were successful but others were not? The letter in that case would be B (for both).
For example, here’s how the value would change along a time line for user ARUP, who does not have select privileges on the table credit_cards:
1. ARUP issues select * from CCMASTER.CREDIT_CARDS.
2.
It fails, raising ORA-00942: table or view does not exist.
3. A record goes into audit trail with the ses_actions value as ---------F------. Note the F in the 10th position, indicating a failure.
4. ARUP is not disconnected from the session. The owner of the table credit_cards, CCMASTER, grants the select privilege to ARUP.
5. ARUP now successfully selects from the table.
6. The ses_action column value will now change to ---------B------. Note that the 10th position has changed from F to B (both success and failure).
After you have audited that object for a while and developed its access profile, you can turn off auditing for successful attempts and enable it for failed ones only. It’s usually the failed ones that reveal potential attacks. Auditing for failed ones only will also reduce the overall length of the audit trails.
In Oracle Database 11g Release 2, the behavior is different. Instead of just one record per session, several records are generated once per action. In the above example where the user performed three actions—insert, select, and update—there will be three records in the audit trail with action_name = SESSION REC.
Had you enabled auditing by access, you would have used a different query, because there would be one record per access. The column ses_actions would not be populated, and the column action_name would show the actual action (such as select or insert) instead of the value SESSION REC. So you would use the following:
col ts format a15

col username format a15

col userhost format a15

col action_name format a10

select to_char(timestamp,’mm/dd/yy hh24:mi:ss’) ts,

username, userhost, action_name

from dba_audit_trail

where owner = 'CCMASTER'

and obj_name = 'CREDIT_CARDS';

TS USERNAME USERHOST ACTION_NAM

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

01/16/06 00:27:44 ARUP prodwpdb SELECT

01/16/06 11:03:24 ARUP prodwpdb UPDATE

01/16/06 12:34:00 ARUP prodwpdb SELECT

Note that there is one record per access (select, update, and so on), which gives you much finer granularity for establishing a pattern of access—as opposed to session-level auditing, which merely shows that ARUP selected and updated from the table credit_cards but not how many times or when.
So, which type of auditing should you use—session level or access level? Remember, access-level auditing writes one record per access, so the amount of audit information written is significantly larger than when only one record per session is written. If you are just trying to establish who accesses the tables and how, you should turn on session-level auditing first. That should give you an idea about how each object is accessed by users. To track down abuse from a specific user, you can turn on access-level auditing.
Implications
As described previously, auditing does affect performance. However, the benefits of auditing may far outweigh the cost, especially if you enable it strategically. Moreover, you don’t need to keep it turned on forever; you can turn auditing on or off on objects dynamically if necessary.
Action Plan
1. Identify key objects (tables, packages, views, etc.) that you want to audit (if not all of them).
2. Turn on session-level auditing for those objects.
3. After some time that you believe is representative of a typical work cycle, analyze the audit trails. The time you wait for the records to be gathered usually depends on your particular case. For instance, in the retail industry, you may want to wait for a month, which will generally capture all the processes—such as month-end processing, tallying of books, and so on.
4. Once you develop the profile, track down unsuccessful attempts. Note the users and the objects they were attempting to access.
5. Turn off session-level auditing and turn on access-level auditing for those objects only.
6. Analyze the access pattern by highlighting the failed attempts, the time, the terminal it came from, and so on, and determine the reason behind the failed attempts. If there is no justifiable excuse, pursue the matter as a potential security breach.
3.7 Enable Auditing for Future Objects

Background
By now you’ve learned how to use auditing on specific, sensitive objects. You may also have decided to enable auditing for all objects, not a subset of them—perhaps you don’t know which ones are sensitive, or perhaps all of them are. In that case, there is a problem: Objects are created in the database continuously, and when they materialize, you have to remember to enable auditing on them.
Strategy
Default auditing is very useful here. To enable auditing on any object not yet created, you issue the following:
audit select on default by session;

Afterward, when you create a table in any schema, the auditing options are automatically enabled for select on that table. To check for the default auditing options set in the database currently, use the following:
SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

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

-/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/-

Note under the column SEL that you have a value S/S. The left-hand value indicates the auditing option when the action is successful. Here it’s S, indicating “session level.” The right-hand part indicates when it’s not successful, which also shows S—indicating “session level” as well. As you didn’t specify when the auditing should be done, the option was set for both success and failure—hence the value S/S.
Suppose you want to enable default auditing on select at session level when successful and at access level when not successful? You would issue the following:
SQL> audit select on default by session whenever successful;

Audit succeeded.

SQL> audit select on default by access whenever not successful

Audit succeeded.

Now if you see the default option, you will see this:
SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA

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

-/- -/- -/- -/- -/- -/- -/- -/- -/- S/A -/- -/- -/- -/- -/-

Here note the column SEL, which shows S/A—indicating session level (S) in success and access level (A) in failure.

Este arreglo es común cuando se quiere limitar el número de entradas de auditoría en el acceso con éxito, por lo tanto, la auditoría a nivel de sesión para el acceso exitoso. Usted querría, sin embargo, para realizar un seguimiento cada vez que aparezca el acceso no; auditoría por lo tanto, se ha habilitado el acceso a nivel de fracasos.
Para desactivar la auditoría por defecto, que se ejecuta lo siguiente:
noaudit seleccionar en forma predeterminada;

Más tarde, usted debe comprobar en all_def_audit_opts fin de asegurarse de que las opciones de auditoría por defecto son realmente apagado.
Implicaciones
Con cualquier tipo de control, siempre hay una preocupación de rendimiento, pero el precio puede ser pequeño para la información obtenida de él.
Sin embargo, existe un peligro potencial. A medida que la auditoría está habilitada por defecto para todos los objetos creados posteriormente, sin tener en cuenta que los crea y que selecciona a partir de ellos, como el DBA no tienen control sobre las opciones de auditoría sobre una mesa. Esta situación puede ser aprovechada por un adversario, que puede crear objetos de forma indiscriminada, insertar en ellos, seleccione una de ellas y, finalmente, caer, todo bien dentro de la cuota impuesta a nivel de tablas.
Sin embargo, la auditoría está activada para cada objeto creado, por lo que los registros de seguir construyendo en la pista de auditoría, inflando la mesa de dólares australianos. Debido a que la tabla está en el tablespace SYSTEM (a menos que lo ha movido a un espacio de tablas diferentes en 11gR2), con el tiempo se llenan y matar a la base de datos, esencialmente la creación de un ataque de denegación de servicio!
Escuchar

Esta situación puede ser raro, pero es definitivamente posible. Afortunadamente, la prevención es sencilla: Sólo mantener una estrecha vigilancia sobre el espacio de tablas de sistema. Si el espacio se agota rápido, explorar por qué los registros de auditoría se están creando con tanta rapidez. Si usted ve un gran número de objetos creados o seleccionados que no son parte del perfil que construyó con la auditoría, debería investigar.
Como medida inmediata, se puede desactivar la auditoría por defecto y luego apague la auditoría en los objetos que llenan las entradas de pista de auditoría (que se puede hacer en línea). Entonces usted debe eliminar los registros de la pista de auditoría después de almacenarlos en una tabla en un espacio de tablas diferentes para el análisis futuro.

Action Plan
1. Decide what actions on which you want to enable default auditing.
2. Decide what level—session or access—you want the default auditing to be.
3. Enable default auditing.
3.8 Restrict Access from Specific Nodes Only

Background
In many cases, only a specified set of client machines will connect to your database servers. Here is a typical architecture:

En este caso, los servidores de base de datos se findb01 y hrdb01, con bases de datos con nombre FINDB (base de datos financieros) y HRDB (base de datos de recursos humanos). Los clientes en el departamento de recursos humanos conectarse a HRDB solamente; si necesitan algunos datos de FINDB, se conectan a las aplicaciones que se ejecutan en los servidores de departamento de finanzas y obtener los datos. Del mismo modo, los servidores de aplicaciones en el departamento de finanzas nunca se conectan a HRDB directamente.
¿Qué sucede si un cliente en el departamento de finanzas, finas01, intenta conectarse a HRDB? Será capaz de conectar con éxito, siempre y cuando conoce a un identificador de usuario y una contraseña válidos. Como siempre, debe proteger las contraseñas de usuario, pero a veces hay usuarios en general con contraseñas conocidas. Algunos ejemplos son los usuarios de aplicaciones tales como HRAPP con una contraseña ridículamente inseguro como hrapp, contraseña, e incluso abc123. Incluso con las políticas de gestión de contraseñas (que se describe en la Fase 4) en su lugar, la contraseña puede ser conocida.
Por lo tanto, usted tiene que construir un muro alrededor de sus servidores para que ningún equipo cliente fuera de la lista autorizada de máquinas pueden conectarse a ellos.
Estrategia
¿Cómo se puede garantizar que sólo las conexiones cliente desde el departamento de recursos humanos pueden entrar en la base de datos HRDB? Usted tiene dos opciones: identificación y validación desencadena nodo oyente.
Nombre de los factores desencadenantes. En esta opción, se crea un disparador que se activa al iniciar la sesión, comprueba la dirección IP, y no si la dirección IP no está en la lista de los aprobados. Aquí está el TRIGGER:

create or replace trigger valid_ip

2 after logon on database

3 begin

4 if sys_context('USERENV','IP_ADDRESS') not in (

5 '100.14.32.9'

6 ) then

7 raise_application_error (-20001,'Login not allowed from this IP');

8 end if;

9* end;

In line 5, you can place all the IP addresses that are valid client machines enclosed in quotes and separated by commas. After this trigger is in effect, when SCOTT tries to connect from an IP address not in the list in the trigger, here’s what occurs:
$ sqlplus scott/tiger@hrdb

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: Login not allowed from this IP

ORA-06512: at line 5

Note the error “ORA-20001: Login not allowed from this IP,” which was placed in the trigger. You can make this message as descriptive as you like. You can also make the trigger more robust to collect useful information such as recording such attempts in a table.
Note a very important point, however: Because login triggers do not fire for a DBA user, they do not prevent someone from logging in as a user with the DBA role enabled. This risk is not as bad as it sounds; you may, in fact, want to let DBAs log in from any client machine.
Listener node validation. The other option is to disable the login attempt at the listener itself. The connection attempt to the database server is disallowed by the listener, so there is no need for a trigger. To enable node validation, simply place the following lines in the file $ORACLE_HOME/network/admin/sqlnet.ora on server hrdb01:
tcp.validnode_checking = yes

tcp.invited_nodes = (hrdb01, hras01, hras02)

Here you have specified the client machines (hras01 and hras02) that are allowed to connect to the listener. You can also specify the hostnames as IP addresses. Place all the node names on a single unbroken line (very important) separated by commas. Don’t forget to add the database server name (hrdb01).
After restart, if a client attempts to log in from a machine other than hras01 or hras02, he gets this error:
$ sqlplus scott/tiger@hrdb

ERROR:

ORA-12537: TNS:connection closed

This rather nonintuitive error is raised because of the filtering that occurs at the listener level. As the listener itself terminates the connection attempt, you get a “connection closed” error. This error occurs even if the user has the DBA role, because the attempt has not yet reached the database. Node validation is a very powerful feature. (For more information, read my blog article “Building a Simple Firewall Using Oracle Net.”)
So, which option should you choose to prevent unwanted clients from connecting? Let’s examine the pros and cons:
• Node validation works at the listener level, so all users are stopped from connecting—even the ones with DBA roles. This may or may not be desirable. For instance, if you have a DBA tool installed on your laptop and your laptop has DHCP enabled—which assigns a new IP each time it connects to the network—you can’t place the IP address in the list of valid nodes; hence, you will not be able to connect.
• Node validation requires the listener to be restarted. For the brief moment that the listener is down, clients will not be able to connect. Although this may not be an issue, you should be aware of it. Every time you change the list of valid nodes, you will have to restart the listener.
• If you want to disable node validation temporarily, you should put tcp.valid_node_checking=no in the file sqlnet.ora and restart the listener. In the case of a login trigger, all you have to do is disable the trigger. You can re-enable it later when required.
• In node validation, you can place all the allowed clients on one line, but on one unbroken line only. If the list is too long to fit on one line, then you can’t use this approach. Conversely, the trigger approach has virtually no limitations. Or, you can use another feature called Connection Manager to limit IP addresses more than one line long.
• You have to use specific IP addresses or hostnames in node validation—no wild cards such as “10.20.%.%”, indicating all clients in the subnet 10.20. To accomplish this, you can use either wild cards in the trigger approach or Connection Manager.
• The trigger approach lets you build a sophisticated tracking system wherein you can write to some tracking table for all attempts, successful or denied. In node validation, there is no such facility.
• In the trigger approach, you can control access based on parameters other than IP—such as the time of day, the user connecting, and so on. Node validation reads only the IP address.
• Remember, node validation stops attempts at the listener level; the database connections are as yet not attempted. Hence, if you have enabled auditing on failed login attempts, they would not be registered.
• Because node validation works at the listener level, the potential adversary does not even get into the database, making denial-of-service attacks more difficult. This is a huge advantage for node validation over the trigger approach.
You should decide on your approach after considering the above differences. In general, if the only objective is to stop connections from IP addresses without any other functionality such as tracking those unsuccessful attempts, node validation is a quick and simple method. If you want more-sophisticated functionality, then you should look into login triggers.
How do you know which IP addresses to block and which ones to allow? Perhaps this assumption is too simplistic; in reality, the list of client machines will be long and difficult to track. Here is where your previous research comes in handy. Remember, in Phase 2, that you captured the IP addresses from which the users connect by mining the listener log. In that step, you must have generated a list of valid client IP addresses or hostnames. That list is what you need now.
Implications
The implications can be quite serious; if you haven’t done your homework, you may block a legitimate client.
Action Plan
1. From Phase 2 Step 1, get the list of valid IP addresses or hostnames of client machines.
2. Decide on the approach to use—trigger based or node validation.
3. Implement the plan.
4. Monitor closely for several days, preferably a full cycle such as a week or a month, as appropriate in your organization.
5. Fine-tune the approach by adding or removing nodes from being filtered.

3.9 Institute Periodic Cleanup of Audit Trails

Background

Durante un período de tiempo, la pista de auditoría crece rápidamente en tamaño dependiendo de la extensión de la auditoría. Como todas las cosas que crecen rápidamente, se necesita con frecuencia de corte, especialmente después de haber examinado los registros y no tienen necesidad de ellos.
Hay varios tipos de pistas de auditoría, la pista de auditoría de base de datos (en la tabla AUD $), la auditoría de grano fino (FGA) sendero (en la tabla FGA_LOG $), la pista de auditoría del sistema operativo (que son archivos regulares en el sistema de archivos ), y la pista de auditoría XML (que son archivos XML sólo en el sistema de archivos)

Estrategia

Puede eliminar los rastros de auditoría sobre la base de la fuente. La base de datos y pistas de auditoría de grano fino se puede eliminar mediante la conexión como SYS y expedir el siguiente:

SQL> delete aud$;

SQL> delete fga_log$;

Durante un período de tiempo, la pista de auditoría crece rápidamente en tamaño dependiendo de la extensión de la auditoría. Como todas las cosas que crecen rápidamente, se necesita con frecuencia de corte, especialmente después de haber examinado los registros y no tienen necesidad de ellos.
Hay varios tipos de pistas de auditoría, la pista de auditoría de base de datos (en la tabla AUD $), la auditoría de grano fino (FGA) sendero (en la tabla FGA_LOG $), la pista de auditoría del sistema operativo (que son archivos regulares en el sistema de archivos ), y la pista de auditoría XML (que son archivos XML sólo en el sistema de archivos)
Estrategia
Puede eliminar los rastros de auditoría sobre la base de la fuente. La base de datos y pistas de auditoría de grano fino se puede eliminar mediante la conexión como SYS y expedir el siguiente:

SQL> truncate table AUD$;

Recuerde, es truncar una operación de DDL, no se puede deshacer. Después de eso, restablecer el parámetro AUDIT_TRAIL a DB y reinicie la base de datos para un funcionamiento normal.
Para las pistas de auditoría de grano fino, puede truncar la tabla FGA_LOG $ en el momento mismo de dólares australianos, si lo desea. Para Ti, sin embargo, hay una manera un poco más flexible. Usted puede simplemente desactivar las políticas de FGA y truncar el camino, sin necesidad de apagar la base de datos. Tenga cuidado con el hecho de que, si bien las políticas son discapacitados, la auditoría de grano fino se desactiva también, y las acciones durante esa etapa no auditados.
Para quitar los archivos del sistema operativo y archivos XML, puede simplemente usar el comando rm. Para quitar los archivos de más de siete días, podría escribir un script como este:

DAYS=7

AUDIT_FILE_LOC=/u01/oracle/admin/PRODB/audit

find ${AUDIT_FILE_LOC} -name "*.log" -ctime ${DAYS} -exec rm {} \;

Establecer la ubicación de los archivos de registro de auditoría y días después de que para eliminarlos en las variables.
Técnicamente, puede eliminar los registros de ruta con el comando trunca contra AUD $ y $ FGA_LOG tablas, incluso cuando la base de datos está bajo la operación normal, pero se arriesga a perder los registros que no han sido examinados. Si bien la base de datos no se está accediendo a los contenidos rastro permanecen estáticas, por lo que el comando trunca realiza una operación consistente.
Oracle Database 11g Release 2 ofrece una nueva forma de gestionar la limpieza del registro de auditoría, a través de un dbms_audit_mgmt paquete. En primer lugar, usted tiene que iniciar la operación de limpieza como se muestra a continuación:

begin

dbms_audit_mgmt.init_cleanup(

audit_trail_type => dbms_audit_mgmt.audit_trail_all,

default_cleanup_interval => 7*24 );

end;

/

This is done only once. This tells that the cleanup will occur in 7*24 hours—in other words, at weekly intervals. The trail type is set to ALL types—in other words, database audit logs, FGA logs, OS files and XML files
Now you can create a Scheduler Job that will purge the trail in weekly intervals
begin

dbms_audit_mgmt.create_purge_job (

audit_trail_type => dbms_audit_mgmt.audit_trail_all,

audit_trail_purge_interval => 7*24,

audit_trail_purge_name => 'all_audit_trails_job'

);

end;

/

If the audit trails are too large, you can set a property called delete batch size, which sets the number of records the job will delete in one shot. Here is how you set the batch size to 10,000.
begin

dbms_audit_mgmt.set_audit_trail_property(

audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,

audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,

audit_trail_property_value => 10000);

end;

/

If you have enabled FGA you can set the delete size of those logs as well:
begin

dbms_audit_mgmt.set_audit_trail_property(

audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,

audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,

audit_trail_property_value => 10000);

end;

/

In the beginning, you can choose to manually delete the audit trails as a one-time effort. This will leave less amount of trail for the purge job. Here is how you can purge the available trails manually:
begin

dbms_audit_mgmt.clean_audit_trail(

audit_trail_type => dbms_audit_mgmt.audit_trail_all

);

end;

/

Implicaciones

Porque debajo de las mantas se trata de una normal operación de eliminación, rehacer y deshacer se genera, lo que podría ser sustancial sobre la base de la cantidad de registros borrados. Ni que decir tiene, que debe realizar esta operación de purga en un período de tiempo cuando la presión sobre la base de datos es baja.
Una vez que la purga se ha completado, la auditoría se ha ido. Por lo tanto, usted debe haber creado todos los informes o archivados estos a un lugar diferente.

Action Plan

1. Archive the audit trails to a different place if needed.
2. Perform a one-time manual delete.
3. Institute a purge process, which varies depending on the version of the database.
4. Make sure you handle both database audit records (regular and FGA) and OS files (regular and XML)
5. Set the delete batch size for the purge process.
________________________________________

The content provided here is for instructional purposes only and is not validated by Oracle; use it at your own risk! Under no circumstances should you consider it to be part of a consulting or services offering.
Go on to Phase 4 | Return to Project Lockdown TOC and Security Primer

Series: Project Lockdown
A phased approach to securing your database infrastructure

Phase 4
Duration: One Quarter

Usted finalmente ha llegado a la fase final y más larga de su seguridad y el cumplimiento del proyecto. Aquí se analizarán algunas de las configuraciones más complejas y la planificación a largo plazo para eliminar las amenazas potenciales.

Covered in this Installment:

• 4.1 Enable Fine-Grained Auditing
• 4.2 Activate a Virtual Private Database
• 4.3 Mask Sensitive Columns
• 4.4 Encrypt Sensitive Data
• 4.5 Secure Backups
• 4.6 Mine History from Archived Logs
• 4.7 Conclusion

4.1 Enable Fine Grained Auditing

Antecedentes

Hasta el momento te habrás dado cuenta que desencadena no se han mencionado como un mecanismo de auditoría. ¿Por qué no? Dado que el uso de disparadores lleva a una sobrecarga de la ejecución secuencial de SQL secundaria, que se suma al tiempo de ejecución global. Esta sobrecarga es raramente aceptable para los sistemas de producción.
De grano fino de Auditoría (FGA) es una excelente solución aquí debido a su sobrecarga de rendimiento mínimo. Si ya está familiarizado con FGA, vaya a la "estrategia" subsección.
FGA se introdujo en base de datos Oracle9i para grabar una pista de auditoría cuando un usuario selecciona de una tabla, no sólo cambia. Esta fue una característica histórica, porque no había otra manera podría ser una actividad SELECCIONAR registrado. Auditoría regular habilitado en las instrucciones SELECT sólo registran que emitió una declaración sobre un objeto, pero no lo que se hizo.
Además de los detalles tales como nombre de usuario, terminal, y el tiempo de la consulta, FGA registros de la instrucción SQL que se ejecuta, así como el número SCN de esa instancia de tiempo. Esto le permite ver no sólo el estado real emitida por el usuario, pero los valores reales de la sierra del usuario, mediante la reconstrucción de los datos mediante consultas flashback. En Oracle Database 10g Release 1, este servicio se extendió a otras lenguaje de manipulación de datos (DML) como INSERT, UPDATE y DELETE.
He aquí un pequeño ejemplo. Supongamos que hay una tabla denominada CUENTAS en el esquema denominado

BANK:
Name Null? Type
---------------- -------- ------------
ACCT_NO NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
BALANCE NUMBER(15,2)

To enable auditing on it, you will need to define an FGA policy on it using the supplied package DBMS_FGA:

begin
dbms_fga.add_policy (
object_schema=>'BANK',
object_name =>'ACCOUNTS',
policy_name =>'ACCOUNTS_ACCESS'
);
end;

After the above code is executed, the FGA is active on the table ACCOUNTS. That’s it—there’s no need to change any initialization parameter or bounce the database.
The above code takes a shared lock on the table while executing, so you could do it online. Now, if a user named ARUP selects from the table by issuing the following
select * from bank.accounts;

the action is immediately recorded in the audit trail known as FGA Audit Trail. You can check it with the following:

select timestamp,
db_user,
os_user,
object_schema,
object_name,
sql_text
from dba_fga_audit_trail;

TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT
--------- ------- ------- ------- -------- ---------------------------
08-FEB-06 ARUP arup BANK ACCOUNTS select * from bank.accounts

The view shows many other columns as well.
You can customize FGA in a variety of ways. For instance, if you want to record when a user selects the column BALANCE and only when the balance is more than 20,000, you could add additional parameters while defining the FGA policy as

begin
dbms_fga.add_policy (
object_schema =>'BANK',
object_name =>'ACCOUNTS',
policy_name =>'ACCOUNTS_ACCESS',
audit_column => 'BALANCE',
audit_condition => 'BALANCE >= 20000'
);
end;

Sidebar: Separating Roles

Uno de los aspectos más conflictivos en materia de seguridad de base de datos es el de la separación de funciones. Para administrar una base de datos, el DBA debe tener ciertos privilegios, que a menudo se rodó en un papel como SYSDBA o DBA. Sin embargo, esta función también tiene poderes como la concesión de privilegios a los demás y la selección de todos los datos de cualquier tabla. Este privilegio de super-usuario va en contra de la mayoría de las regulaciones como Sarbanes-Oxley y la Ley Gramm-Leach-Bliley (GLBA), ya que permite que una sola persona para convertirse en demasiado poderosos.

El papel de DBA es una cuestión puramente técnica que se relaciona con la base de datos física y que generalmente se le asigna a un grupo de personas. Si las mismas personas también tienen la capacidad de ver y modificar los datos del usuario, esto crea un grave riesgo potencial para la seguridad de la vulnerabilidad de datos. La responsabilidad es cuestionable, ya que el DBA puede eliminar cualquier dato, incluso la pista de auditoría. Sin embargo, la mayoría de los requisitos de cumplimiento de la demanda una clara separación de funciones y habilidades. Por lo tanto, los privilegios necesarios para administrar la base de datos física no debe incluir aquellos a consultar o modificar datos del usuario.

Tradicionalmente, la base de datos Oracle no ha apoyado esa separación. Por lo tanto, en 2006, Oracle anunció dos herramientas revolucionarias (tanto en versión beta en el momento de escribir estas líneas). La primera, Oracle Database Vault (una opción de Oracle Database 10g Release 2 Enterprise Edition y versiones posteriores), permite la separación de privilegios mediante la creación de "reinos" de la autorización. Por ejemplo, el rol DBA ya no tendrán carta blanca para consultar y modificar cualquier tabla. En cambio, el papel se limitará a un dominio específico de los privilegios, que se implementa como un reino. De este modo, Oracle Database Vault elimina el concepto de un Dios omnipotente super-usuario que puede borrar todos los rastros. Del mismo modo, Oracle Audit Vault, un producto independiente, proporciona una facilidad de auditoría seguro que puede estar en un reino independiente fuera del control de la DEA o la del gestor de aplicaciones, la pista de auditoría puede ser controlado sólo por un administrador de seguridad. El uso de estas dos herramientas, las tres funciones relevantes aquí, la administración de la base de datos física, gestión de privilegios de objeto, y la gestión de la pista de auditoría, pueden estar completamente separados, el estado final exigido por casi todas las disposiciones de cumplimiento.

Otra característica de la FGA es que se puede llamar a un procedimiento almacenado, además de la grabación en la pista de auditoría, cuando se cumplen las condiciones. Esto ofrece una ventaja enorme en algunos casos de procesamiento adicional, como el envío de mensajes de correo electrónico. SP se llaman módulos de controlador de la política de FGA. Recuerde, la FGA se puede activar por comandos SELECT, que se puede activar estos módulos de controlador. En cierto modo, que hace que los módulos de controlador de "gatillo de seleccionar" declaraciones.

Oracle Database 10g presenta más mejoras FGA, así, como estas dos características notables:

Obligar a variables. Uso de FGA, que puede capturar las variables se unen en los estados con un
parameter in the DBMS_FGA.ADD_POLICY procedure:
audit_trail => DB_EXTENDED.

Puedes poner esto en el archivo de base de datos de parámetros de inicialización para que FGA graba todo el tiempo.
Pertinentes columnas. Considere las siguientes dos declaraciones:
equilibrio de las cuentas de seleccionar donde account_no = 9,995;
select sum (saldo) de las cuentas;

La primera pide claramente una pieza específica de información confidencial de identificación a un cliente, algo que es posible que desee auditar. La segunda es más benigna, por el que el usuario no encontrará todos los datos específicos sensibles acerca de un cliente. En su política de seguridad, es posible que desee registrar la primera declaración, pero no el segundo. Esto ayudará a limitar el tamaño de la pista

You can do that by using another parameter, audit_column_opts => DBMS_FGA.ALL_COLUMNS, while defining the policy:
begin
dbms_fga.add_policy (
object_schema => 'ANANDA',
object_name => 'ACCOUNTS',
policy_name => 'ACCOUNTS_SEL',
audit_column => 'ACCOUNT_NO, BALANCE',
audit_condition => 'BALANCE >= 20000',
statement_types => 'SELECT',
audit_column_opts => DBMS_FGA.ALL_COLUMNS
);
end;

The default is DBMS_FGA.ANY_COLUMNS, which triggers an audit trail whenever any of the columns is selected.
Here we have merely scratched the surface of FGA. You will also find extensive discussions on FGA in my book Oracle PL/SQL for DBAs (O’Reilly Media, 2005).

Estrategia

La mayor ventaja de FGA sobre la auditoría regular es que no requiere ningún parámetro de inicialización específica y por lo tanto no necesita un rebote base de datos. Usted puede activar o desactivar a voluntad políticas de FGA en los objetos.
En concreto, usted debe buscar en las columnas sensibles, y, opcionalmente, los valores sensibles en las columnas. En primer lugar, es necesario formular una política para la auditoría. He aquí un ejemplo típico de esta política:

En la tabla SUELDOS:

• Auditoría cuando alguien selecciona sólo el sueldo columnas y BONO. No auditoría cuando otras columnas están seleccionadas.
• Auditoría, incluso cuando el usuario selecciona una sola columna como el salario solo, sin ningún tipo de información que lo identifique como EMPNO.
• Auditar cada vez que alguien elige una columna de la tabla de EMPNOS por debajo de 1.000, que están reservados para la gestión ejecutiva.
• No auditoría cuando el usuario selecciona SAP_PAYROLL_APP. Este es el identificador de usuario cuenta utilizada por la aplicación de procesamiento de nómina, y la auditoría esto va a generar grandes volúmenes de datos y llenar la pista.
• gerentes de recursos humanos y Sally Jane comprobar y ajustar periódicamente el salario de los empleados en los grados bajo cuyo salario corresponde a 1000. Sus declaraciones SELECT para salarios en 1000 no deben ser auditados.
• Además de la auditoría, enqueue_message ejecutar un procedimiento almacenado que envía un correo electrónico a un oficial de seguridad Scott.
Con esto en mente, usted debe construir tres diferentes políticas FGA.
En primer lugar, la política universal para EMPNO> = 1000 y por el salario columnas y BONUS sólo:

begin
dbms_fga.add_policy (
object_schema => 'ACCMAN',
object_name => 'SALARY',
policy_name => 'SAL_SEL_UNIV',
audit_column => 'SALARY, BONUS',
statement_types => 'SELECT',
audit_option => 'EMPNO >= 1000 and USER NOT IN(''SAP_PAYROLL_APP'', ''JANE'', ''SALLY'')',
handler_module => 'ENQUEUE_MESSAGE'
);
end;

Second, build the all-columns policy for EMPNO < 1000:
begin
dbms_fga.add_policy (
object_schema => 'ACCMAN',
object_name => 'SALARY',
policy_name => 'SAL_SEL_EXEC',
statement_types => 'SELECT',
audit_option => 'EMPNO < 1000',
handler_module => 'ENQUEUE_MESSAGE'
);
end;

Third, add the special policy for Jane and Sally:
begin
dbms_fga.add_policy (
object_schema => 'ACCMAN',
object_name => 'SALARY',
policy_name => 'SAL_SEL_SPEC',
audit_column => 'SALARY, BONUS',
statement_types => 'SELECT',
audit_option => 'EMPNO >= 1000 AND SAL <= 1000 and USER IN
(''JANE'', ''SALLY'') AND USER != ''SAP_PAYROLL_APP''',
handler_module => 'ENQUEUE_MESSAGE'
);
end;

As you can see, the conditions in the audit_option are mutually exclusive, so only one policy will be in effect and only one record will be written when any user attempts the SELECT statements.

Using this strategy, you can build a set of FGA policies. You can then enable and disable policies at will without impacting operation.

Implications

Como puede ver, las condiciones en el audit_option son mutuamente excluyentes, por lo que sólo una política estará en vigor y sólo un registro se escribe cuando un usuario intenta las instrucciones SELECT.

Con esta estrategia, puede crear un conjunto de políticas FGA. A continuación, puede activar y desactivar a voluntad políticas sin afectar la operación.

Implicaciones

Hay cuatro graves consecuencias:

Si el módulo controlador, si se define, arroja errores, mientras que la selección se hace, provocará un comportamiento diferente en diferentes versiones de Oracle Database:
• En la base de datos Oracle9i, que en silencio se detiene la recuperación de esa fila, sin informar de un error. Por lo tanto, si hay 100 filas y 4 de ellos cumplen la condición de auditoría, el módulo controlador dispararon cuatro veces, y cada vez que se producirá un error. La consulta devolverá sólo 96 filas sin informar de cualquier error, y nunca sabrá que sucedió. Obviamente, esto conduce a resultados inexactos.
• En Oracle Database 10g Release 1, se ignoran los errores en el módulo de controlador y recuperar todas las 100 filas como se esperaba.
• En Oracle Database 10g Release 2, se informará sobre el error en la sesión del usuario de realizar la consulta sin devolver ninguna fila, ni siquiera las 96 filas que no cumplía el requisito de auditoría y no se ha ejecutado la función de controlador.

Por lo tanto, probar el módulo controlador de FGA a fondo antes de su aplicación.
La pista de auditoría de mesa FGA_LOG $-está en el tablespace SYSTEM. A medida que más entradas FGA se generan, el espacio de tablas se llena, lo que puede causar la base de datos de alto. Tenga en cuenta que en Oracle Database 11g Release 2, es posible mover el FGA_LOG $ tabla a un espacio de tablas diferentes (que se describe en detalle en la Fase 1 de esta serie de artículos).
La pista de auditoría se escriben en una tabla, aunque de forma asincrónica. Esto induce una transacción, así como de E / S, que se suma a la global de E / S en la base de datos. Si la base de datos es I / O, usted verá un impacto en el rendimiento sobre la base de datos como resultado de la FGA.

La auditoría se escriben de forma asincrónica mediante transacciones autónomas. Por lo tanto, incluso si un usuario revierte la transacción, la entrada de pista no se elimina, dando lugar a falsos positivos. Si usted está pensando sobre el uso de FGA como un mecanismo infalible para identificar a los usuarios, usted debe estar enterado de estos falsos positivos.

Action Plan
1. Identify sensitive tables and columns.
2. Identify degree of sensitivity to access—for example, salary below 500 is OK.
3. Put all possible combinations on a piece of paper and then combine them into pieces of WHERE conditions (predicates) in such a way that any given condition will be satisfied by a single predicate, not more.
4. Build the FGA policy from those predicates.
5. Enable FGA policies.
6. After some time, analyze the FGA audit trail files.
7. Build a purge schedule and purge the FGA trail table.
4.2 Activate a Virtual Private Database

Antecedentes

Si ya está familiarizado con los contextos de aplicación y Oracle Virtual Private Database (VPD, también conocida como Fila nivel de seguridad o de grano fino de control de acceso), puede saltarse esta subsección y saltar directamente a la "estrategia" subsección.
(DPV es un tema grande, por lo que sólo cubrirá los conceptos básicos aquí. Al igual que con FGA, más información se puede encontrar en mi libro de Oracle PL / SQL para el DBA).
Suponga que tiene una tabla denominada CUENTAS con los siguientes datos:

SQL> select * from accounts;

ACCNO ACC_NAME ACC_BAL
---------- -------------------- ----------
1 John 1000
2 Jill 1500
3 Joe 1200
4 Jack 1300

Usted quiere asegurarse de que sólo las personas con la debida autorización debe ver los saldos de cuentas permite a sus niveles. Es decir, el nivel 1 no debería ver los saldos de más de 1.000, Nivel 2 no debería ver los saldos de más de 1.200, y Nivel 3 debe ver todo. Usted tiene otra tabla para mostrar a los usuarios y sus niveles:

SQL> select * from userlevels;

USERNAME USERLEVEL
------------------------------ ----------
CLERK1 1
CLERK2 2
CLERK3 3

To hold the user’s level when they first log in, you will need to create an application context:
create context user_level_ctx using set_user_level_ctx;

and its associated trusted procedure:
create or replace procedure set_user_level_ctx
(
p_level in number
)
as
begin
dbms_session.set_context (
'USER_LEVEL_CTX',
'LEVEL',
p_level
);
end;

Then you will need to create a login trigger to set the proper application context:
create or replace trigger tr_set_user_level
after logon
on database
declare
l_level number;
begin
select userlevel
into l_level
from arup.userlevels
where username = user;
set_user_level_ctx (l_level);
exception
when NO_DATA_FOUND then
null;
when OTHERS then
raise;
end;

This sets the stage for setting the user levels in the application context attributes. Let’s test to make sure:
SQL> conn clerk1/clerk1
Connected.
SQL> select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

SYS_CONTEXT('USER_LEVEL_CTX','LEVEL')
--------------------------------------
1

SQL> conn clerk2/clerk2
Connected.
SQL> select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

SYS_CONTEXT('USER_LEVEL_CTX','LEVEL')
--------------------------------------
2

SQL> conn clerk3/clerk3
Connected.
SQL> select sys_context('USER_LEVEL_CTX','LEVEL') from dual;

SYS_CONTEXT('USER_LEVEL_CTX','LEVEL')
--------------------------------------
3

Como puedes ver, cada identificador de usuario adecuadamente establece los niveles. Ahora, usted puede construir la DPV en la mesa. Toda la infraestructura DPV puede ser controlado mediante el cable de PL / SQL DBMS_RLS paquete, las normas que rigen las filas que deben consignarse son controlados por un concepto llamado una política. Una política se aplica un "predicado" (una condición WHERE) para todas las consultas sobre la mesa, efectivamente restringir el acceso a las filas. La condición en que se genera mediante una función denominada función política. Así, en primer lugar tenemos que crear la función política que devuelve una condición WHERE que se aplicarán a las preguntas

create or replace function get_acc_max_bal
(
p_schema in varchar2,
p_obj in varchar2
)
return varchar2
as
l_ret varchar2(2000);
begin
select
case userlevel
when 1 then 'acc_bal <= 1000'
when 2 then 'acc_bal <= 1200'
when 3 then null
else
'1=2'
end
into l_ret
from userlevels
where username = USER;
return l_ret;
end;
then add the policy:
begin
dbms_rls.add_policy (
object_name => 'ACCOUNTS',
policy_name => 'ACC_MAX_BAL',
policy_function => 'GET_ACC_MAX_BAL',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
);
end;

At this time, the table is protected. When CLERK1logs in and selects from the table:
SQL> select * from arup.accounts;

ACCNO ACC_NAME ACC_BAL
---------- -------------------- ----------
1 John 1000

CLERK1 sees only ACCNO 1, with balance 1,000. Because he is not authorized to see anything above that account balance, the other accounts are invisible to him. But when CLERK2 logs in:
SQL> conn clerk2/clerk2
Connected.

SQL> select * from arup.accounts;

ACCNO ACC_NAME ACC_BAL
---------- -------------------- ----------
1 John 1000
2 Joe 1200

she can see ACCNO 2 as well. The balance of ACCNO 2 is 1,200, within the authorized limit for CLERK2.
Using this technique, you can build a sort of restricted view into the table. This will be a very handy tool in Project Lockdown.

Estrategia

La clave está en saber qué información ha de ser protegida de todas las partes y en las columnas. Esto suena más fácil de lo que realmente es. La recopilación de esta información requiere el conocimiento del negocio, o al menos la colaboración con alguien más familiarizado con los procesos. Una vez que identifique las tablas y columnas, usted debería ser capaz de aplicar la política VPD como se muestra en los ejemplos sobre el "fondo " subsección.

¿Qué pasa si desea que algunos usuarios tienen acceso ilimitado a las tablas a pesar de que la política DPV está en vigor? El papel EXENTO DE ACCESO DE POLÍTICA hace exactamente eso:
concesión de la política de acceso exento de Ananda;

A partir de ese momento, ANANDA pasará por alto todas las políticas de acceso definidas en todas las mesas.
Probablemente es inaceptable para permitir a un usuario para anular todas las restricciones de acceso, sin embargo, una solución mejor es que el código en la política de la función en sí. Un buen ejemplo es el propietario del esquema de la tabla, usted definitivamente quiere que vea todas las filas de la tabla de su propiedad sin restricción alguna. Puede que el código en la función política de la siguiente manera:

create or replace function get_acc_max_bal
(
p_schema in varchar2,
p_obj in varchar2
)
return varchar2
as
l_ret varchar2(2000);
begin
if (p_schema = USER) then
l_ret := NULL;
else
select
case userlevel
when 1 then 'acc_bal <= 1000'
when 2 then 'acc_bal <= 1200'
when 3 then null
else
'1=2'
end
into l_ret
from userlevels
where username = USER;
end if;
return l_ret;
end;

This version of the function returns NULL when the owner of the table logs in (p_schema = USER) and therefore provides unrestricted access to the table. You can, of course, make any changes in the function to allow more users to bypass the VPD policy.
The biggest challenge in VPD is putting the restriction on the child tables. Your limiting condition may be on a column called, say, ACC_BAL; but all other child tables may not have the column. So how can you restrict those tables?
For example, here is a table called ADDRESSES that contains the customer addresses. This table does not have a column called ACC_BAL, so how can you put the same restriction on this table as on ACCOUNTS? There are two ways:
The first way is to add a column ACC_BAL on the table ADDRESSES. This column may be updated through a trigger on the main table ACCOUNTS. Now you can define a policy on the table using the same policy function used on ACCOUNTS.
The second way is to use a different policy function for ADDRESSES. In this function, the return value should be
ACCNO in (SELECT ACCNO FROM ACCOUNTS)

This is the predicate used in the policy restriction. So, the address will be displayed only if the account exists on ACCOUNTS, and because the table ACCOUNTS is restricted anyway, the table ADDRESSES will be automatically restricted.
You have to choose between these two approaches based on your situation.
Implications
There are several potentially damaging implications.
VPD works by rewriting queries to add the additional predicate. The user queries may have been well written and perhaps well tuned, but the introduction of the additional predicate does throw a wrench into the works, because the optimization plan may change. You should carefully consider the potential impact and mitigate the risk by building indexes.
Materialized views work by selecting the rows from the underlying tables. If the schema owner of the view does not have unrestricted access to the table, only those rows that satisfy the VPD policy will be refreshed, rendering the view inaccurate.
If you have set up replication, the users doing the propagation and reception should have unrestricted access to the table. Otherwise, they will replicate only part of the table.
If you load the table using Direct Path Insert (INSERT with the APPEND hint), then you cannot have a VPD policy on the table. Either you should disable the policy temporarily or do the insert using a user that has unrestricted access to the table.
Direct Path Exports bypass the SQL Layer; hence, the VPD policy will not be applied. Therefore, when you export a table using the DIRECT=Y option, Oracle Database ignores it and exports it using the conventional path. This may add to the overall execution time.

Action Plan
This is a complex and fluid plan:

1. Identify the tables to be protected by VPD.
2. Identify the columns of those tables that need to be protected.
3. Identify the restricting condition—for example, Salaries > 1000.
4. Identify how to establish privileges—for example,

do users have authority levels or roles? You may want to divide users into three groups with certain levels of authority associated with them. Or, perhaps you want to assign access restrictions on groups of users based on role—managers can access all rows, clerks can access SALARY > 2000, and so on.
5. Decide how you will pass the privileges to the policy function—through a package variable, through an application context, or through a static table.
6. Identify if further indexes need to be created.
7. Create additional indexes.
8. Identify child tables and decide on a scheme to enable the restriction on them—via a new column or the IN condition.
9. Re-identify the need for indexes based on your decision above.
10. Create indexes.
11. Build the policy function.
12. Create the policy (but as disabled).
13. On a light-activity time of day, enable the policy and test accessing the table from a regular user account to make sure that the policy works. If it does not work, check trace files to find the error.
14. If it works, enable the policy.
15. Monitor performance.
16. Identify further needs to build indexes, use outlines, and so on.
17. Iterate for all tables.
4.3 Mask Sensitive Columns

Antecedentes

Imagine que usted es un pirata informático de base de datos de aficionados. La base de datos, en este caso contiene registros médicos y la información que está buscando es los códigos de diagnóstico. ¿Qué columna que usted busca? Probablemente uno llamado Diagnóstico, enfermedad, o algo similar.

Como puede ver, las columnas sensibles con nombres obvios son una cuestión de seguridad fundamental.
Estrategia

Cuando los adversarios no tienen conocimiento previo del contenido de la base de datos, no van a descifrar el significado de las columnas si tienen nombres no intuitiva. Esta es una estrategia conocida como "la seguridad por oscuridad." Incluso los adversarios experimentados que deliberadamente la fuerza en la base de datos aún tendrá que localizar a los nombres de columna antes de que se puede hacer nada más. Debido a que pueden tener un tiempo limitado-que casi siempre lo hacen-por lo general se pasa a la siguiente oportunidad. Por supuesto, con sus nombres oscura columna hace que el desarrollo más difícil también.
Hay una alternativa para hacer de esta compensación, sin embargo: El enmascaramiento columna, en el que ocultar el contenido de la columna y exponer sólo a los usuarios legítimos.
Existen dos métodos de enmascaramiento de columna: mediante el uso de una vista y utilizando DPV.
El uso de un punto de vista. Este método es aplicable a cualquier versión de Oracle Database, pero suele ser la única opción cuando su versión de base de datos Oracle 9i o anterior.
Supongamos que la tabla es la siguiente:

SQL> desc patient_diagnosis

Name Null? Type
------------------- ------ -------------
PATIENT_ID NUMBER
DIAGNOSIS_ID NUMBER
DIAGNOSIS_CODE VARCHAR2(2)
DOCTOR_ID NUMBER(2)
BILLING_CODE NUMBER(10)

The rows look like this:
SQL> select * from patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE
---------- ------------ -- ---------- ------------
1 1 01 1 1003
1 2 02 1 1003
1 3 11 1 1003
2 1 11 1 1005
2 2 41 2 1005
In this case, you want to hide the values of the column DIAGNOSIS_CODE
create view vw_patient_disgnosis
as
select
patient_id,
diagnosis_id,
doctor_id,
billing_code
from patient_diagnosis
/

A continuación, puede crear un sinónimo de la PATIENT_DIAGNOSIS VW_PATIENT_DIAGNOSIS vista y donación de selección de la vista en lugar de la tabla. El ocultar la columna DIAGNOSIS_CODE.

Esta es una solución bastante simplista, así que en vez de enmascarar la columna para todos los usuarios, es posible que desee crear la oscuridad basada en roles. Cuando el usuario es un administrador, se muestran las columnas protegidas, de lo contrario, no lo hacen. Usted puede hacerlo pasando un contexto de aplicación o una variable global para designar la función del usuario. Si el atributo de contexto de aplicación se IS_MANAGER, puede utilizar

create or replace view vw_patient_disgnosis
as
select
patient_id,
diagnosis_id,
decode(
sys_context('USER_ROLE_CTX','IS_MANAGER'),
'Y', DIAGNOSIS_CODE, null
) diagnosis_code,
doctor_id,
billing_code
from patient_diagnosis;

This is a more flexible view that can be granted to all users, and the contents of the view will be dynamic based on the user’s role.
Using VPD. Oracle Database 10g introduced a feature that makes VPD even more useful: There is no need to create a view. Rather, the VPD policy can suppress the display of sensitive column. In this case, the VPD policy function will look like this:
1 create or replace function pd_pol
2 (
3 p_schema in varchar2,
4 p_obj in varchar2
5 )
6 return varchar2
7 is
8 l_ret varchar2(2000);
9 begin
10 if (p_schema = USER) then
11 l_ret := NULL;
12 else
13 l_ret := '1=2';
14 end if;
15 return l_ret;
16 end;

Now create the policy function:
1 begin
2 dbms_rls.add_policy (
3 object_schema => 'ARUP',
4 object_name => 'PATIENT_DIAGNOSIS',
5 policy_name => 'PD_POL',
6 policy_function => 'PD_POL',
7 statement_types => 'SELECT',
8 update_check => TRUE,
9 sec_relevant_cols => 'DIAGNOSIS_CODE',
10 sec_relevant_cols_opt => dbms_rls.all_rows
11 );
12 end;

Note Lines 9 and 10. In Line 9, we mention the column DIAGNOSIS_CODE as a sensitive column. In Line 10, we specify that if the column is selected, all rows are displayed; but the column value is shown as NULL. This effectively masks the column. From the policy function, note that the predicate applied is NULL when the owner of the table selects from it—so, the VPD restrictions are not applied and the column is shown.
Remember, there is no way to “replace” a policy. If an old policy exists, you have to drop it first:
begin
dbms_rls.drop_policy (
object_schema => 'ARUP',
object_name => 'PATIENT_DIAGNOSIS',
policy_name => 'PD_POL'
);
end;

Now you can test the effect of this change
SQL> conn arup/arup
Connected.
SQL> select * from patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE
---------- ------------ --- ---------- ------------
1 1 01 1 1003
1 2 02 1 1003
1 3 11 1 1003
2 1 11 1 1005
2 2 41 2 1005

Note that the DIAGNOSIS_CODE column values are shown, because ARUP is the owner of the table and should see the values. Now, connect as another user who has select privileges on the table, and issue the same query:
SQL> set null ?
SQL> conn ananda/ananda
SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID D DOCTOR_ID BILLING_CODE
---------- ------------ -- ---------- ------------
1 1 ? 1 1003
1 2 ? 1 1003
1 3 ? 1 1003
2 1 ? 1 1005
2 2 ? 2 1005

Note how the column DIAGNOSIS_CODE shows all NULL values.
This method is much more elegant, even apart from the fact that there is no view to be created on the table, no synonym to be created to point to the view, and no additional grants to be maintained. If you need to have different policies to show this value to different people, you can easily modify the policy function (Line 11) to add further checks. For instance, your policy may say that less sensitive diagnosis codes such as those for the common cold can be exposed to all users. So, your policy function will look like the following, assuming that the DIAGNOSIS_CODE for common cold is “01”:
1 create or replace function pd_pol
2 (
3 p_schema in varchar2,
4 p_obj in varchar2
5 )
6 return varchar2
7 is
8 l_ret varchar2(2000);
9 begin
10 if (p_schema = USER) then
11 l_ret := NULL;
12 else
13 l_ret := 'diagnosis_code=''01''';
14 end if;
15 return l_ret;
16* end;

Note Line 13, where we added the predicate to show only when the diagnosis code is “01” and nothing else. Now, test the effect:
SQL> conn arup/arup
Connected.
SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE
---------- ------------ -- ---------- ------------
1 1 01 1 1003
1 2 02 1 1003
1 3 11 1 1003
2 1 11 1 1005
2 2 41 2 1005

SQL> conn ananda/ananda
Connected.
SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE
---------- ------------ -- ---------- ------------
1 1 01 1 1003
1 2 ? 1 1003
1 3 ? 1 1003
2 1 ? 1 1005
2 2 ? 2 1005

Note that the diagnosis code is “01” for patient id 1 and diagnosis id 1, which is the only allowed diagnosis code; so it is shown clearly. All others have been shown as NULL and are effectively masked.
Implications
If you want to mask sensitive columns and the programs do not even mention them, there will be no implications.
If you use the view approach, where the sensitive column is simply removed from the view, it might pose a problem where the programs use a construct like SELECT * FROM TABLE .... Because the columns have not been explicitly named, the absence of one column will affect the program execution. But this is not the issue with the modified view approach where the column is still present but NULLed.
There is one very important implication you should be aware of. Suppose you have a column called CONTRACT_AMOUNT, which is shown if less than a certain value—say, $500. If more than $500, then the column shows NULL. The table has three rows with values 300, 300, and 600 in the column. Prior to column masking, if a user issues the query
select avg (contract_amount) from contracts;
he will get 400 (the average of 300, 300, and 600). After column masking, the value of the column will be NULL for the record where the value is $600, so the user will see the values as 300, 300, and NULL. Now the same query would show 200 (the average of 300, 300, and NULL). Note the important difference: The value shown is 200, not 400.
Be aware of this important difference that column masking can introduce.
Action Plan
• List all sensitive columns on all sensitive tables.
• Decide on sensitive columns to be masked.
• Decide on the privilege scheme.
• If you are on Oracle Database 10g Release 1 or later, choose the VPD approach.
ELSE
Choose the view-based approach.
• If you choose the VPD approach:
o Create policy functions for each table.
o Create policies for each table. This will help you control masking on specific tables.
• If you choose the view-based approach:
o Create views on each table, typically named VW_.
o Create a synonym (the same name as the table) pointing to the view.
o Revoke privileges made to the users on the table.
o Re-grant the privileges to the view.
o Recompile all dependent invalid objects.

4.4 Encrypt Sensitive Data

Antecedentes

Como ya he mencionado anteriormente en esta serie, la seguridad es como protegerse en un frío día de invierno con varias capas de ropa, frente a usar la chaqueta más voluminosa de invierno. Pero la construcción de defensas en capas no pueden disuadir a los adversarios más decididos, y ciertamente no siempre será evitar que un usuario legítimo de robar los activos corporativos. La última línea de defensa en este caso es el cifrado, por el cual los datos son accesibles para el usuario (o del adversario), pero sólo con una llave. Sin la llave, los datos no sirve para nada. Si se protege la clave, para proteger los datos.
Recuerde, el cifrado no es un sustituto de otras capas de seguridad. Debe tener las defensas en su lugar sin tener en cuenta.

La encriptación es un tema muy amplio, pero voy a tratar de dar una visión general recurribles aquí.
Oracle ofrece tres tipos de cifrado:

El primer tipo es el API de cifrado, como el dbms_obfuscation_toolkit paquetes y dbms_crypto (en Oracle Database 10g Release 1 o posterior). El uso de estos paquetes, usted puede construir su propia infraestructura para cifrar los datos. Este es el enfoque más flexible, pero bastante complejo de construir y gestionar.
A nivel de columna Cifrado de datos transparente, una característica de Oracle Database 10g Release 2 y versiones posteriores, evita la gestión manual de claves. La base de datos de gestión de claves, pero como su nombre indica, el cifrado de datos es transparente, la columna se almacena en forma encriptada. Cuando se selecciona, se encontrará en texto claro.

Tablespace-level Transparent Data Encryption, introduced in Oracle Database 11g Release 1, makes the process much easier and much more effective. A whole tablespace is encrypted, and anything on that tablespace—tables, indexes, materialized views—is stored in encrypted format. However, when they are selected and placed in the buffer cache, the data is in clear text. Because data is compared in the buffer cache, it is done in clear text as well, and consequently the comparison becomes faster.
I recommend that you review the Oracle documentation (Chapter 17 of the Oracle Database Security Guide and Chapter 3 of the Oracle Database Advanced Security Administrator’s Guide) to learn more about these features. At a minimum, you should review two articles on Oracle Technology Network: “Transparent Data Encryption” (oracle.com/technology/oramag/oracle/05-sep/o55security.html) and “Encrypting Tablespaces” (oracle.com/technology/oramag/oracle/09-jan/o19tte.html) before proceeding to the “Strategy” subsection.
Strategy
The choice between regular encryption and Transparent Data Encryption is a vital one. (In releases prior to Oracle Database 10g Release 2, however, only the former is available.)
In either case, you will have to identify the tables, and more specifically the columns, to be encrypted. It’s probably not a good idea to encrypt all columns, because encryption routines do burn CPU cycles.
Next, pick an encryption algorithm. A typical choice is Triple Data Encryption Standard (DES3 with 156-bit encryption. However, starting with Oracle Database 10g Release 1, you have access to the newer, faster, and more secure Advanced Encryption Standard (AES) algorithm that operates with a 128-bit long key.
Oracle9i Database provided dbms_obfuscation_toolkit; with Oracle Database 10g Release 1, you have access to dbms_crypto, a much better utility. The older package is still available, but avoid it if you are building encryption infrastructure for the first time.

At this point, you have to decide between TDE and your own routine (if applicable). The table below may help you decide.
Transparent Data Encryption User-built Encryption
Flexibility Minimal—For instance, if the column SALARY in the SALARIES table is encrypted, then any user with access to the table will be able to see the data clearly. You can’t place selective control on that column based on user roles and levels. The data in the database is encrypted but is decrypted when accessed. Note that you can use VPD with column masking that displays NULL for an encrypted column, as shown in section 4.3. Robust—For instance, you may define the column to be shown in clear text only if the user is a manager, and encrypted otherwise. This will ensure that the same application sees the data differently based on who is using it. This flexibility can also be expanded to other variables, such as time of day or the client machine accessing the database.
Setup Minimal—This facility is truly transparent. There is nothing to do but issue this command (provided all other one-time jobs have been executed, such as building the wallet):
ALTER TABLE SALARIES MODIFY (SALARY ENCRYPT) Extensive—To provide a seamless interface to the users, you have to create a view that does a decryption of the column. This view should then be granted. This introduces several layers of complexity in management.
Key Management Automated—Key management is handled by the database, using a wallet.
In Oracle Database 11 onward, key management can also be hardware based, making it even more secure and effective. Manual—Because you have to manage the keys, you have to decide how you can balance between the two conflicting requirements:
1. Make the key secure so that it’s not accessible to an adversary.
2. Make it accessible to applications.
Restrictions on columns Some—In column-level TDE, certain columns cannot be encrypted, such as those with partition keys, of datatypes BLOB, and so on. There is no restriction, however, on Tablespace-level TDE, introduced in Oracle Database 11g Release 1. One—The only restriction is LONG.
Support for indexes It depends on the TDE flavor used In tablespace-level TDE, indexes help as much as the regular clear-text data. In column-level TDE, indexes may not help in queries because the data is stored in an encrypted manner. Yes—Because you control the encryption, you can create surrogate columns to build indexes on.
Scope It depends on the TDE flavor used. In column-level TDE, you can decide to encrypt a specific column while the rest can be clear text, preventing unneeded encryption/decryption. In tablespace-level TDE, all the columns of the table, sensitive or not, will be encrypted. Controlled—Specific columns can be encrypted.

If you decide to use TDE—regardless of the flavor—take the following steps:
1. Configure the wallet. It’s generally in the folder $ORACLE_BASE/admin/$ORACLE_SID/wallet, but you can use any location by putting the following lines in the file SQLNET.ORA:
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/orawall) ) )

Here, the wallet location is set to /orawall.
2. Open the wallet and assign a password. Issue this command:
alter system set encryption key authenticated by "53cr3t";
Choose a password that is difficult to guess but easy to remember. (For security purposes, you may want the wallet password to remain unknown to the DBA, so that a second person is needed to open the wallet.) From now on, every time the database opens, you have to open the wallet with
alter system set encryption wallet open authenticated by "53cr3t";

After this, you can create tables with encrypted columns (as in column-level TDE):
create table accounts
(
acc_no number not null,
first_name varchar2(30) not null,
last_name varchar2(30) not null,
SSN varchar2(9) ENCRYPT USING 'AES128',
acc_type varchar2(1) not null,
folio_id number ENCRYPT USING 'AES128',
sub_acc_type varchar2(30),
acc_open_dt date not null,
acc_mod_dt date,
acc_mgr_id number
)

When users insert data into the table, the data is automatically converted into encrypted value and put on the disk. Similarly, when the data is retrieved by a select statement, it’s automatically converted into decrypted value and shown to the user.
If you decide to use tablespace-level TDE, you can’t convert an existing tablespace into an encrypted one. You must create a new tablespace as encrypted and move the tables or indexes into that tablespace. Here is how you create an encrypted tablespace:
create tablespace enc_ts
datafile '+DATA'
size 100M
encryption using 'AES128'
default storage (encrypt)
/

After that, you should move a table—for example, CREDIT_CARDS—into that tablespace:
alter table credit_cards
move tablespace enc_ts
/

That’s it; the table is now completely encrypted on disk. Of course, any new table you create on this tablespace will be encrypted from the beginning.
Implications
As with any major change, there are some serious implications.
Encryption is a CPU-intensive operation. If the system is already CPU bound, this will make it worse. Consider Tablespace Encryption in Oracle Database 11g Release 1 to reduce this performance issue.
Indexes will not work well with encrypted columns, especially predicates like WHERE LIKE 'XYZ%', which should have used index range scan in unencrypted columns but will use full table scans. Again, Tablespace Encryption is not subject to this restriction.
Key management is a very important issue. If you lose the keys, the data becomes inaccessible.
Action Plan
The action plan is described in the “Strategy” subsection.

4.5 Secure Backups

Background
In many cases, DBAs forget the most vulnerable of spots: the backup of the database. Once the data leaves the secured perimeters of the server, it’s not under your control anymore. If an adversary can steal the tapes, he can mount them on a different server, restore the database, and browse the data at his leisure.
Fortunately, you can eliminate even that risk via backup encryption.
Strategy
In Oracle Database 10g Release 2 and later, you can encrypt backups in Oracle Recovery Manager (Oracle RMAN) in three different modes: transparent, password-based, and dual.
Transparent mode. In this (the most common) approach, Oracle RMAN gets the encryption key from the encryption wallet (discussed in the previous section) and uses it to encrypt the backup set. Needless to say, the wallet must be open during the backup and restore. If the wallet is not open, you will get the errors
ORA-19914: unable to encrypt backup
ORA-28365: wallet is not open

Open the wallet using this command:
alter system set encryption wallet open authenticated by "53cr3t";

Of course, the password may be something other than “53cr3t.” Remember, this is case sensitive so it must be enclosed in double quotes.
Make sure the encryption is enabled for the entire database. You can enable it by issuing
RMAN> configure encryption for database on;

Alternately, if you want to encrypt only a few tablespaces, you can issue the following for all tablespaces:
RMAN> configure encryption for tablespace users on;

After that you can use the regular backup commands without any other user intervention:
RMAN> backup tablespace users;

When you restore the backup, the wallet must be open. If the wallet is not open, then you get the error while restoring:
RMAN> restore tablespace users;

Starting restore at 09-FEB-06
using channel ORA_DISK_1

... messages ...
ORA-19870: error reading backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AR
EA\ANANDA\BACKUPSET\2006_02_09\O1_MF_NNNDF_TAG20060209T221325_1YR16QLT_.BKP
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

The error message is pretty clear; the wallet must be opened. If you ever need to restore the tablespace in other servers, the wallet must be copied there and opened with the correct password. If an adversary steals the tapes, he will not be able to restore the backups.
Password-based mode. In this case, there is no need to use the wallet for key management. The backups are encrypted by a key that itself is encrypted by a password. Here is how a command looks:
RMAN> set encryption on identified by "53cr3t" only;
RMAN> backup tablespace users;

The backup set produced above will be encrypted by the password. To decrypt it during restore, you have to use the password as follows:
RMAN> set decryption identified by "53cr3t";
RMAN> restore tablespace users;

This eliminates the use of wallets. So to restore the backup on any server, all you need is the password, which does away with the backup of the wallet. However, your password must be visible in the scripts, and hence any adversary with access to the database server will be able to read it. Refer to Phase 2, where you learned how to hide passwords.
Dual mode. As the name suggests, this mode combines the other two approaches. You take the backup in the following manner:
RMAN> set encryption on identified by "53cr3t";
RMAN> backup tablespace users;

Note that there is no “only” clause in the command set encryption. This allows the backup to be restored in either of the two ways: by password “tiger” or by an open wallet. For instance, note the following command. No password has been given, yet the restore is successful.
RMAN> sql 'alter tablespace users offline';
RMAN> restore tablespace users;

Starting restore at 09-FEB-06
allocated channel: ORA_DISK_1
... restore command output comes here ...

This is useful when you generally use a script to handle backups and you often have to recover the database to a different server, such as a QA server. But in general, this method does not have much practical use.
If you do use the transparent mode of backup encryption, you can back up the wallet as well. Even if an adversary gets the wallet, he will not be able to open it without a password.
Implications
Encryption, as I mentioned before, is a fairly CPU-intensive process, and it is thus a highly CPU-intensive operation to encrypt the entire Oracle RMAN backup set. You could reduce the CPU cycles by partially encrypting backups—just select tablespaces that contain sensitive data, not all of them. A better option is to use TDE for column-level encryption.
The wallet (in transparent mode) and password (in password-based mode are very important. If you lose them, you will never be able to decrypt the backups. So have a plan for backing these up.
If you use password-based encryption, the password must be in the scripts. It could be vulnerable to theft.
Action Plan
1. Decide if you really want to encrypt entire backup sets as opposed to specific columns in specific tables.
2. If Oracle RMAN backup sets are to be encrypted, decide between the entire database and specific tablespaces.
3. Choose between transparent, password-based, and dual-mode approaches.
4. If transparent or dual-mode approach, then
a. Configure the wallet.
b. Open the wallet.
c. In RMAN, configure the tablespace(s) or the entire database to be encrypted in backup.
5. If password-based or dual-mode approach, then
. Choose a password and put it in the Oracle RMAN scripts. Use any of the password hiding techniques described in Phase 2.
a. Take a backup using those scripts only.
4.6 Mine History from Archived Logs

Background
One very important part of the lockdown process is ensuring that no unauthorized data definition language (DDL) activity occurs on the database. An adversary, after getting a legitimate route to connect to the database, will find it extremely easy to drop a few segments to create a simple denial-of-service attack.
In Phase 3, you learned how to lock down key objects so that they cannot be altered without the DBA’s consent. But what about the illegitimate alterations done with consent and an attack that was discovered only later? This is where the fourth pillar of security—accountability—comes into play.
One option is to turn on DDL auditing to track DDL activities. All in all, it’s the easiest option—it’s easy to set up, even easier to browse, possible to archive, and so on. However, auditing puts pressure on performance, something we are trying to avoid. The question is how to retrace the DDL statements without setting up auditing.
Strategy
This is where a tool (or a feature, if you will called Log Miner comes in very handy. Introduced in Oracle8i Database, Log Miner lets you search online redo logs or archived logs.
Here’s how you can set up log mining to unearth DDL details. First, get the online log file or archive log file to mine, and add that to the Log Miner session:
sqlplus / as sysdba

begin
dbms_logmnr.add_logfile (
'C:\ORACLE\DATABASE\ANANDA\REDO03.LOG');
dbms_logmnr.add_logfile (
'C:\ORACLE\DATABASE\ANANDA\REDO02.LOG');
dbms_logmnr.add_logfile (
'C:\ORACLE\DATABASE\ANANDA\REDO01.LOG');
end;
/

After adding the files, start the Log Miner session. You have to pass the source of the data dictionary; here we have specified the online catalog as the source:
begin
dbms_logmnr.start_logmnr (
options => dbms_logmnr.dict_from_online_catalog
);
end;

The mined contents are placed in a view named V$LOGMNR_CONTENTS, but this view is transient—it is visible only to the session that started the Log Miner. So, if you want to perform an analysis later, you need to preserve it in a permanent table:
create table lm_contents
nologging
as
select * from v$logmnr_contents;
Now for the analysis. To find the DDL commands, you would issue the following query:
select sql_redo
from lm_contents
where operation = 'DDL'
and seg_owner not in ('SYS','SYSTEM','PUBLIC');

Here is a sample output. Note that there is no DROP TABLE statement—in Oracle Database 10g, dropped tables are not actually dropped but rather renamed. The SQL_REDO will reflect that. In the cases where the user actually drops the table using the PURGE clause, the column SQL_REDO will reflect the correct command. (To save space, I have trimmed the output so that the DDLs for Functions and Procedures appear partially. I have also used RECSEPCHAR '.' to show devising lines between multiline records.)
SQL_REDO
-------------------------------------------------------------------------------

ALTER PACKAGE "XDB"."DBMS_XSLPROCESSOR" COMPILE SPECIFICATION REUSE SETTINGS;
ALTER PACKAGE "XDB"."DBMS_XMLDOM" COMPILE SPECIFICATION REUSE SETTINGS;
ALTER PACKAGE "XDB"."DBMS_XMLPARSER" COMPILE SPECIFICATION REUSE SETTINGS;
ALTER PACKAGE "EXFSYS"."DBMS_RLMGR_DR" COMPILE BODY REUSE SETTINGS;
truncate
table developers;
...............................................................................
create table patient_diagnosis
(
patient_id number,
...............................................................................
create view vw_patient_diagnosis
as
...............................................................................
create or replace view vw_patient_diagnosis
as
...............................................................................
create or replace function pd_pol
(
p_schema in varchar2,
...............................................................................
create or replace function pd_pol
(
p_schema in varchar2,
...............................................................................
create or replace function pd_pol
(
p_schema in varchar2,
...............................................................................
grant connect, resource to ananda identified by VALUES '1DB10D95DE84E304' ;
create or replace function pd_pol
(
...............................................................................
create or replace function pd_pol
(
p_schema in varchar2,
...............................................................................
ALTER TABLE "ARUP"."TEST1" RENAME TO "BIN$JQHaX2mpSxOyrhkxAteHmg==$0" ;
drop table test1 AS "BIN$JQHaX2mpSxOyrhkxAteHmg==$0" ;

Of course, this output itself is probably meaningless; you have to see more information such as time stamp, system change number (SCN) and so on in the Log Miner entries to make a valid connection between user actions and actual events:
select timestamp, scn, seg_owner, seg_name
from lm_contents
where operation = 'DDL'
and seg_owner not in ('SYS','SYSTEM','PUBLIC')
/

Here is a sample output:
TIMESTAMP SCN SEG_OWNER SEG_NAME
--------- ---------- ---------- - -----------------------------
06-FEB-06 1024674 XDB DBMS_XSLPROCESSOR
06-FEB-06 1026884 XDB DBMS_XMLDOM
06-FEB-06 1026896 XDB DBMS_XMLPARSER
06-FEB-06 1026918 EXFSYS DBMS_RLMGR_DR
06-FEB-06 1029244 ARUP DEVELOPERS
08-FEB-06 1096847 ARUP PATIENT_DIAGNOSIS
08-FEB-06 1097057 ARUP VW_PATIENT_DIAGNOSIS
08-FEB-06 1097920 ARUP VW_PATIENT_DIAGNOSIS
08-FEB-06 1100059 ARUP PD_POL
08-FEB-06 1100157 ARUP PD_POL
08-FEB-06 1100386 ARUP PD_POL
08-FEB-06 1100413 ANANDA
08-FEB-06 1101544 ANANDA PD_POL
08-FEB-06 1101564 ARUP PD_POL
09-FEB-06 1123950 ARUP TEST1
09-FEB-06 1123953 ARUP TEST1

Note that I have used SEG_OWNER and not the USERNAME. Due to a bug that is unresolved as of Oracle Database 10.2.0.1, the USERNAME is not populated.
This is just one example of how to pull DDL statements from the archived logs. You can use any statement to mine from the logs—DMLs, as well. Mining for DML statements is a great substitute for auditing, because it exerts no performance pressures on the database.
Implications
Log Miner is not intrusive, but it does take a lot of CPU cycles and PGA memory on the server. So, run it carefully, preferably under conditions of reduced load. (Another option is to move the archived logs to a different server and mine them there. This option should be exploited whenever possible.)
Action Plan
Identify what statements you want to mine from logs and how often. Some examples could be DDL statements related to dropping of objects; but you may need to be further selective. In data warehouse environments, applications temporarily create many tables and drop them later, so you may want to exclude those schemas.
Use the above technique to extract information from the archived logs on a regular basis and analyze them for possible abuse or pattern for abuse.
4.7 Conclusion
This final phase of your security and compliance project had a small number of tasks, but each of these tasks takes a considerable amount of time to execute. In addition, these tasks had no clear details as a part of the objective. These minor details vary so much that it is impossible to build a generalized, one-size-fits-all description.
The most important factor in your quest for security is to understand that you can’t chase it in a vacuum. To effectively build a secured database infrastructure, your actions must be tempered with the understanding of the unique nature of your organization, and your analysis must include your business processes to isolate sensitive data from the rest. For instance, credit-card numbers are to be protected at any organization, but what about sales numbers? In some organizations, such as retailers, the sheer volume of data makes it infeasible to protect it by encryption. The same can’t be said for a hedge-fund trading firm, where sales numbers are zealously guarded. A tiered approach to see sensitive data may work in some cases; but in most cases, it’s probably best provided on an as-needed basis.
At the end of this journey, I hope you have gained some valuable tools and techniques that you can use right now to protect your database infrastructure. I will highly appreciate it if you could take some time to give me your feedback and suggestions on enhancing this four-part series with other relevant issues and material.
The content provided here is for instructional purposes only and is not validated by Oracle. Use it at your own risk. Under no circumstances should you consider it to be part of a consulting or services offering.
________________________________________

The content provided here is for instructional purposes only and is not validated by Oracle; use it at your own risk! Under no circumstances should you consider it to be part of a consulting or services offering.
Return to Project Lockdown TOC and Security Primer

El blog de Nelson Javier Martinez Acosta

MERCADOS FINANCIEROS

viernes, 14 de enero de 2011

MOVER AUD$ FGA_LOG$

Oracle LockDown

Vistas de página en total

Buscar este blog

Seguidores

Datos personales

Archivo del blog

INSTRUCTOR WDP

Entradas populares

Oracle Seguridad

Etiquetas

Archivo del blog

OCP 11G

OCA11G

SQL

WEBLOGIC

OCA 10G

OCP 10G

Formulario de contacto