Infrastructure Technology Group – APPSINFODB

Finding solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s Data Integrity

  • Blog Stats

    • 39,804 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    April 2018
    M T W T F S S
    « Dec    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    30  
  • Archives

  • Authors

Archive for the ‘Security’ Category

How to Implement TDE

Posted by appsinfo on April 3, 2018

Now that we have got the documentation to implement TDE. At high level we can follow below steps and mark these as check list during deployment or cut-over to production. Below steps are performed on EBS R12.2 on Solaris operating system.

Folks make sure you take full backup before importing the database including the binaries. A snapshot of entire stack is best option.

  1. Perform Pre TDE steps as per the Doc ID: 1926686.1
  2. Export complete database using data pump
  3. Create new empty database
  4. ENCRYPT the application tablespace
  5. Create and open the wallet
  6. Import the complete database using data pump to the encrypted tablespace
  7. Perform post TDE steps
Advertisements

Posted in Backup and Recovery, Cloud, E-Business Suite R12, Infrastructure, Leadership, Oracle Database, Oracle ExaData & ExaLogic, Patches, Security | Tagged: , , , , | Leave a Comment »

TDE- Transparent Data Encryption

Posted by appsinfo on April 3, 2018

TDE is designed to provide customers the ability to transparently apply encryption within the database without impacting existing applications. Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen, because it stores the encryption keys in a security module (that is, a keystore) external to the database. Protecting data from this type of theft is required for most compliance regulations. The benefit to using transparent data encryption is that it requires little coding and is quick and easy to implement.

More Details

Posted in E-Business Suite R12, Oracle Database, Oracle EBS 12.2, Security | Tagged: , , , , | Leave a Comment »

Database Password File: ORAPWD

Posted by appsinfo on April 2, 2013

Create PASSWORD File

1) SQL> select * from v$pwfile_users;

no rows selected

2) orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password> entries=10
3) Now grant sysdba privilege to the users that you need. Check V$PWFILE_USERS about the entry.

SQL>GRANT SYSDBA to SYSTEM;

 SQL> select * from v$PWFILE_USERS;

USERNAME                       SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS                            TRUE  TRUE  FALSE
SYSTEM                         TRUE  FALSE FALSE
RMANUSER                       TRUE  FALSE FALSE

 SQL> select decode(count(*), 1, ‘spfile’, ‘pfile’ ) from v$spparameter where rownum=1  and isspecified=’TRUE’;

DECODE
——
spfile

Posted in Security | Tagged: | Leave a Comment »

Database Audit

Posted by appsinfo on September 1, 2009

Oracle has provided built-in audit capabilities for many years and in the most recent versions, this auditing is very sophisticated. The Fine Grained Auditing (FGA) that you can use now can provide audits for all DML against a database, a schema or specific objects. FGA can also audit who was selecting data and not just who updated data. Oracle can also track who changed database objects (DDL, like alter table or compile procedure), what they did and when they did it.

The first thing to do is to activate audit which is disabled by default. To do so, use the command:

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

Then, you have to restart the database, using SHUTDOWN, and STARTUP to start it up again.

First of all, we create a new user:

CONNECT sys/pass AS SYSDBA

CREATE USER audit_user IDENTIFIED BY pass
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRAND connect TO audit_user;
GRAND create table, create procedure TO audit_user;

Then, let’s audit all operations of our user:

AUDIT ALL BY audit_user BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_user BY ACCESS;

This will audit all DDL and DML queries, and some system events, like logon/logoff.

Now, we connect with the user and make some operations, in order to be audited.

CONNECT audit_user/pass

CREATE TABLE tabTest ( id NUMBER );
INSERT INTO tabTest (id) VALUES (1);
UPDATE tabTest SET id = id;
SELECT * FROM tabTest;
DELETE FROM tabTest;
DROP TABLE tabTest;

Now that we have some interesting stuff to look at in the audit trail, let’s go!

COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username, extended_timestamp, owner, obj_name, action_name
FROM dba_audit_trail
WHERE owner = ‘AUDIT_USER’
ORDER BY timestamp;

And the result is:
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
———- ———————————– ———- ——– —————————-
AUDIT_USER 25-APR-2007 19:22:06.992930 +01:00 AUDIT_USER TABTEST CREATE TABLE
AUDIT_USER 25-APR-2007 19:22:19.296248 +01:00 AUDIT_USER TABTEST INSERT
AUDIT_USER 25-APR-2007 19:22:34.234981 +01:00 AUDIT_USER TABTEST UPDATE
AUDIT_USER 25-APR-2007 19:22:46.776770 +01:00 AUDIT_USER TABTEST SELECT
AUDIT_USER 25-APR-2007 19:22:57.049840 +01:00 AUDIT_USER TABTEST DELETE
AUDIT_USER 25-APR-2007 19:23:03.705906 +01:00 AUDIT_USER TABTEST DROP TABLE

6 rows selected.

We used the view dba_audit_trail, but there are other views where you can find some more information about audit.

For further information, please refer to Oracle 10g documentation or metalink.

Posted in Security | Leave a Comment »

Oracle Application Auditing

Posted by appsinfo on April 29, 2009

Oracle Application Auditing

 

We have 2 types of audit in Oracle Applications:


1) Audit users who sign in to Oracle Applications
2) Audit changed data in database

 

Login Tables used in Audit

FND_LOGINS – holds information about users login to system, when and how long. This table holds one row for each login.
FND_LOGIN_RESPONSIBILITIES – holds information about changes of responsibilities, when and how long being at each responsibility.
For each change this table holds one row with values that identify the user’s login session, the user’s current responsibility, and when the user is in the responsibility
FND_LOGIN_RESP_FORMS – holds information about using forms, when and how long.
This table holds one row for each form used in the same session with values that identify the user’s login session, current responsibility, when and how long using each form.

In order to see the user audit information, there are 5 reports (concurrent) that you can use:


Signon Audit Concurrent Request: Show concurrent requests audit information – the user who run this request, when, from which responsibility and form, for all concurrent requests run in the system.
Signon Audit Forms: Show audit information about which user enter to which form, when and for how long.
Signon Audit Responsibilities: Show audit information about which user choose which responsibility, when and how long he stayed in each responsibility.
Signon Audit Unsuccessful Logins: Show audit information about unsuccessful logins to Oracle Applications.
Signon Audit Users: Show audit information about who sign on, when and for how long.


Sign-On: Audit Level profile:


This profile can be set to one of the four possible values at site level,

What you need to do in order to enable it is:


NONE – no audit enabled (Default value)
USER – audit user login to system, the logon time and the logoff time.
RESPONSIBILITY – audit all the above + which responsibilities the user chose and how long he stayed in each responsibility.
FORM – audit all the above + which forms the user used and how long he stayed in each form.

At each level audit information is populated in the tables:


USER – populates the FND_LOGINS table only.
RESPONSIBILITY – populates FND_LOGINS and FND_LOGIN_RESPONSIBILITIES tables.
FORM – populates FND_LOGINS, FND_LOGIN_RESPONSIBILITIES and FND_LOGIN_RESP_FORMS tables.

You can see online data using monitor screen for the users connected to system, which responsibility and form they are using and how long they are connected.
You can use this monitor from:
System Administrator responsibility -> security -> User -> Monitor.

To inform users about unsuccessful logins to their account, you can set the “Sign-On:Notification” profile to Yes.

For more information about audit you can read “Oracle Applications System Administrator’s Guide – Security Release 11i” – Chapter 5 – User and Data Auditing

Posted in Security | Leave a Comment »