Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> PCI - Auditing 'connect / as sysdba' - connections, PCI-Requirement - which user connected as '/ as sysdba'
holi61
post Feb 15 2012, 05:03 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 10-January 12
Member No.: 46,557



Hi,

I have a problem with a PCI DSS - requirement in Oracle 11.2. (PCI DSS = Payment Card Industry Data Security Standard)

Problem:

we connect via ' ssh -2 -X -l oracle hostname ' to the databaseserver and become os-user 'oracle'. we have also two offshore locations with dba's and each dba comes with his personalized user to the jumphost and then with the above ssh command to the database server.

the problem is that each dba becomes the oracle-os-account and can now connect with '/ as sysdba' to the database.

in pci-dss this is not allowed !

now my question:

how can I audit these '/ as sysdba'-connections and prove which user connected at which time with the '/ as sysdba' command ?

database is in audit mode. we log to syslog on linus redhat 5.

Maybe some of you has the same problem with PCIDSS - compliant oracle databases and a solution or an idea for this problem.

I know one solution could be setting "SQLNET.AUTHENTICATION_SERVICES" parameter to "NONE" in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba). but we have to many applications and jobs and this is not really the solution in this case.

I think I can only solve this problem with personalized OS-user DBA-accounts in the dba-goup on os-site and os-user oracle should not be used for the future ?? I also need personalized dba-user-accounts in the database. using sys and system is not allowed. this users has to be locked and only for special administration work could it be unlocked.

Kind Regards
Horst

Go to the top of the page
 
+Quote Post
burleson
post Feb 15 2012, 10:50 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,472
Joined: 26-January 04
Member No.: 13



Hi Horst,

Please note that you can block out the external authentication:



http://www.dba-oracle.com/security/local_o...hentication.htm

You could creatre a login trigger to write these login attempts to a table of flat file:

http://www.dba-oracle.com/art_builder_sec_audit.htm


See these notes on auditing system (SYS) connections:

http://www.dba-oracle.com/security/auditin...connections.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
holi61
post Feb 16 2012, 10:26 AM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 10-January 12
Member No.: 46,557



Hi,
we tried to create the above trigger on 11.2.0.2 oracle db.

CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER
LOGON ON DATABASE
DECLARE
sess number(10);
prog varchar2(70);
BEGIN
IF sys_context('USERENV', 'BG_JOB_ID') is null and user = 'myuser' THEN sess := sys_context('USERENV','SESSIONID');
SELECT program INTO prog FROM v$session WHERE audsid = sess and rownum<=1;
INSERT INTO stats$user_log VALUES ( user,sys_context('USERENV','SESSIONID'),sys_context('USERENV','HOST'),prog,sysdate,sys_context('USERENV','OS_USER'));
END IF;
END;
/


Get an error like this:

Warning: Trigger created with compilation errors.

Errors for TRIGGER "LOGON_AUDIT_TRIGGER":

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/4 PL/SQL: SQL Statement ignored
7/16 PL/SQL: ORA-00947: not enough values

??

Go to the top of the page
 
+Quote Post
burleson
post Feb 17 2012, 10:20 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,472
Joined: 26-January 04
Member No.: 13



>> ORA-00947: not enough values

http://www.dba-oracle.com/sf_ora_00947_not_enough_values.htm

Your insert is missing some values that the table requires . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 16th September 2014 - 12:27 PM