Re: How to capture the name of a user that logon on the database as sysdba?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Thu, 06 Apr 2006 00:09:06 -0400
Message-Id: <1144296546l.3880l.0l@medo.noip.com>

On 04/05/2006 11:34:35 PM, xiaoyan wrote:

> SQL>conn system/manager as sydba;
>
> SQL>create user wxy identified by wxy;
>
> SQL>grant sysdba to wxy;
>
> SQL>conn wxy/wxy as sysdba
>
>
>
> How can I capture the name of the user ¡®wxy¡¯?
>
> Thanks in advance!
>
>

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED

SQL> SQL> audit create session whenever successful;

Audit succeeded.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> connect scott/tiger
Connected.
SQL> connect /
Connected.

SQL> column os_username format a15
SQL> alter session set nls_date_Format='MM/DD/YY HH24:MI:SS';
SQL> select os_username,username,timestamp from dba_audit_trail; 

OS_USERNAME     USERNAME             TIMESTAMP
--------------- -------------------- -----------------
mgogala         OPS$MGOGALA          04/05/06 23:57:22
mgogala         SCOTT                04/05/06 23:58:04
mgogala         OPS$MGOGALA          04/05/06 23:58:07

SQL> Behavior of sys auditing is defined by other parameters (audit_file_dest,audit_sys_operations) and is not a subject of this post. This is, I believe, an answer to your question. Simple recording of who logged in, when was it done and from where was it done is called auditing and is available as of version 10.2. It might be available even in the earlier versions. Consult the DBA guide, Concepts and SQL Reference, it's probably there.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 05 2006 - 23:09:06 CDT