APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 38,065 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    November 2017
    M T W T F S S
    « Dec    
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  
  • Archives

  • Authors

Archive for the ‘Security’ Category

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

Advertisements

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 »

Custom Top Setup

Posted by appsinfo on February 24, 2009

create TABLESPACE custom DATAFILE ’E:\oracle\devdata\custom01.dbf’ size 1024mb;

create user XXABC identified by XXABC default tablespace custom temporary tablespace TEMP quota unlimited on custom quota unlimited on TEMP;

grant connect,resource to xxabc

system administrator->security-oracle->register

Database
Username Password Privilege Install Group Description
————————————————————————–
XXCUS CUST Enabled 0 Custom Application

system admiministrator->applications->register

Application ShorName Base Path Description
———– ——– ——— ———–

Bog custom application xxabc xxabc_TOP custom applications

Add the custom schema to a data group. Log into Applications as the System
Administrator and navigate to: Security –> ORACLE –> DataGroup.

For example:

Data Group: Standard
Description: Standard Data Group

Application Oracle ID Description
———————- ———— ———————–
ABC Custom Application APPS Custom Application

It is recommend that you use the STANDARD datagroup and pair the custom schema
with APPS or you can add a new data group. This depends upon your own
requirements.

Posted in Security | Leave a Comment »

Form Server Version

Posted by appsinfo on November 15, 2008

How to view form server information:

 Form server information is required to check the version of Forms Server when upgrading forms server i.e. applying developer 6i patch set.

 The Form server can be found now when FND:Diagnostics = ‘Yes’

 Reason to keep this profile option to ‘No’ is for security reasons.

Before applying developer 6i Patch set

Forms Server

—————————————-

Oracle Forms Version : 6.0.8.25.2

Application Object Library : 11.5.0

Machine : DEV

Forms User CPU (secs) : 0.218750

Forms System CPU (secs) : 0.046875

Forms Process ID : 4412:4936

 

After applying developer 6i patch set

 

Forms Server

—————————————-

Oracle Forms Version : 6.0.8.28.0

Application Object Library : 11.5.0

Machine : ERPDEVSRV

Forms User CPU (secs) : 0.265625

Forms System CPU (secs) : 0.046875

Forms Process ID : 1628:1632

 

Posted in Security | Leave a Comment »