Ramin Hossaini (blog)

22May/100

Oracle: Basic user-logon auditing

Oracle 8i introduced logon-triggers which could be used for auditing.

To start, create a table which will store your audit-logs (I usually do this as SYSTEM):

1
2
3
4
5
6
7
8
9
10

CREATE TABLE AUDIT$user_logs
 (
    user_id           VARCHAR2(30),
    session_id        NUMBER(8),
    host              VARCHAR2(30),
    logon_day         DATE,
    logon_time        VARCHAR2(10)
 );
 
TABLE created.

Next, create the trigger to capture the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE TRIGGER
 logon_audit_trigger
 AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO AUDIT$user_logs VALUES(
    USER,
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYSDATE,
    TO_CHAR(SYSDATE, 'hh24:mi:ss')
 );
 END;
 /
 
TRIGGER created.

Display Audit-data:

1
2
3
4
5
6
7

SQL> SELECT * FROM AUDIT$user_logs;
 
USER_ID         SESSION_ID HOST               LOGON_DAY LOGON_TIME
--------------- ---------- ------------------ --------- ----------
DBSNMP              123716 HOST               01-OCT-08 10:21:32
SYSTEM              123717 DOMAIN\PCNUMBER    01-OCT-08 10:21:53
SYSMAN                   0 HOST               01-OCT-08 10:21:58

Disable and Enable Logon-auditing:

1
2
3
4

ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER DISABLE
/
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER ENABLE
/

To purge audit-data:

1

TRUNCATE TABLE AUDIT$user_logs
Tagged as: , No Comments
18May/100

Oracle: List all database-links

To list all db-links (must be a user with permission to select from sys.dba_db_links) in a database:

SELECT * FROM DBA_DB_LINKS;

Sample output:

1
2
3
4
5
6

SQL> SELECT * FROM DBA_DB_LINKS;
 
OWNER   DB_LINK    USERNAME   HOST    CREATED
------- ---------- ---------- ------- ----------
SYSTEM  TEST_LINK  SCOTT      TST11   26-SEP-08
SCOTT   HR_LINK    HR         TST11   26-SEP-08
Tagged as: No Comments
16May/100

Oracle: Recycle listener.log

Oracle stores a log-file (listener.log) under $ORACLE_HOME/network/log that keeps track of all connections made to the database.

Oracle keeps this file as an open file-stream, so even if you rename the file, it still keeps on growing as new connections are made.

This method will let you recycle/truncate/purge listener.log without resorting to taking the listener down which would cause a disruption to users.

The idea is to turn log_status off, create a new file, then turn log_status on again

1
2
3
4

$ lsnrctl set log_status off
$ mv listener.log listener.log.old
$ touch listener.log
$ lsnrctl set log_status on
Tagged as: , No Comments
15May/100

Basic Auditing in Oracle

To check if auditing is enabled:

SHOW parameter AUDIT;

To enable auditing, modify/add the following in your PFILE and restart the database:

audit_trail = db;

To audit SELECTs on an object:

AUDIT SELECT ON object;

To list all Audit data on a database:

SELECT * FROM sys.aud$;

Purge/delete entries from the audit table:

DELETE FROM sys.aud$;

To view audit data:

SELECT * FROM sys.aud$;

To disable auditing:

NOAUDIT SELECT ANY TABLE;
Tagged as: , No Comments
5May/100

Google Calendar SMS notifications available in Namibia

It really is the little things in life that matter most.

Namibia is moving up in the World.

Tagged as: , , , , No Comments
27Apr/102

jQuery.collapsible plugin

Introduction

I wrote my first jQuery plugin today and figured other people might find it useful too.

The plugin adds expand/collapse functionality to divs and also saves the state using the jQuery cookie plugin

Changelog

  • 15 Jun 2010
    • You can now specify that a module's default state is collapsed by adding 'collapsed' to the class. For more information, look at the example given below.
  • 04 May 2010
    • Changed expand/collapse icons to sprite
    • Can now have multiple groups of collapsible boxes by giving each
      a descriptive identifier
  • 27 Apr 2010
    • First release

Demo

Click here for a demo

Download

Download Latest Version (jquery.collapsible.latest.zip)

Setup

Add includes to the head-section:

1
2
3
4

<script type='text/javascript' src="./js/jquery-1.4.2.min.js"></script>
<script type='text/javascript' src="./js/jquery.cookie.js"></script>
<script type='text/javascript' src="./js/jquery.collapsible.js"></script>
<link rel="stylesheet" type="text/css" media="screen" href="./css/demo.css" />

Add the HTML:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<div class="module"> <!-- only important for styling -->
	<div class="header"> <!-- must have a class name -->
		<h1>Header #1</h1>
	</div>
	<div class="content"> <!-- the 'content' class is only for styling -->
		<p>Content comes here</p>
	</div>
</div>
 
<div class="module">
	<div class="header collapsed"> <!-- this will be collapsed by default (unless the cookie says otherwise) -->
		<h1>Header #2</h1>
	</div>
	<div class="content">
		<p>Content comes here</p>
	</div>
</div>
 
<div class="module">
	<div class="header">
		<h1>Header #3</h1>
	</div>
	<div class="content">
		<p>Content comes here</p>
	</div>
</div>

Initialize the Javascript:

1
2
3
4
5

<script type='text/javascript'>
$(document).ready(function() {
	$.collapsible(".module .header");
});
</script>
Tagged as: , , 2 Comments
25Apr/100

[MySQL] Batch update of all entries in table that match condition

Lest ye forget.

1

UPDATE <table> SET <column>=<value> WHERE <condition>;
Tagged as: No Comments
Page 2 of 2312345Last »

Categories

Meta

Bear