Oracle 12 Data Dictionary

I found this 2 weeks ago but it was covered with advertisements for a company selling Oracle support and services.
As I dislike putting something like that on my wall I edited it a bit.

For anyone who wants it: a big ass mo’fo Oracle 12 DDL poster. (click thru for the PDF)

Simple logon auditing

It’s always hell to find out which user was active in the database like “yesterday” so like a lot of other people I made user of after logon triggers to do some basic logging. No need for fancy auditing, just basic logging of logon events.

First I started with creating a table.

CREATE TABLE LOGON_AUDIT(
	LOGON_DATE	    DATE,	
	USERNAME         VARCHAR2 (30),
	OSUSER           VARCHAR2 (30),
	MACHINE          VARCHAR2 (30),
	PROGRAM          VARCHAR2 (60)
);

Now the table is done where logging is going to happen we just have to create the trigger that fills it. Because I didn’t want to log all access (monitoring agents, windows services) I added an “if user not in” that specifies the accounts that I don’t want to log.

CREATE OR REPLACE TRIGGER LOGON_AUDIT_TRG 
AFTER LOGON
ON DATABASE
BEGIN
	IF (user not in ('DONTLIKEYOU','DONTWANTYOU','DONTNEEDYOU')) then
	INSERT INTO LOGON_AUDIT (
		LOGON_DATE,
		USERNAME,
		OSUSER,
		MACHINE,
		PROGRAM
		)
	VALUES (
		SYSDATE,
		SYS_CONTEXT ('USERENV', 'SESSION_USER'),
		SYS_CONTEXT ('USERENV', 'OS_USER'),
		SYS_CONTEXT ('USERENV', 'HOST'),
		SYS_CONTEXT ('USERENV', 'MODULE')
		);
	END IF;
END;
/

And we are done! It is wise to add a job to clean up the log table or to archive it cause this thing can really grow if you have a lot of users/services connecting to your database! More on that in another post.

DDL logging made easy

One of our customers requested logging of DDL statements happening in their databases. There’s a lot to find on the internet about this. Oracle itself has a few (licensed) options but it’s quite easy to do this yourself with just the help of a trigger, a table… and perhaps a job to clean up the log once in a while.

The customer just wanted to see which user touched what object so I didn’t even start to look in to logging the sql_text but just the simple alter/create/drop statements and the objects that are involved.

First I started with creating a table.

CREATE TABLE DDL_AUDIT
(
   DATE   DATE,
   USERNAME         VARCHAR2(30),
   OSUSER           VARCHAR2(30),
   MACHINE          VARCHAR2(30),
   PROGRAM          VARCHAR2(60),
   OPERATION        VARCHAR2(30),
   OBJTYPE          VARCHAR2(30),
   OBJOWNER         VARCHAR2(30),
   OBJNAME          VARCHAR2(30)
);

Now the table is done where logging is going to happen we just have to create the trigger that fills it.

CREATE OR REPLACE TRIGGER DDL_AUDIT_TRG
   AFTER CREATE OR DROP OR ALTER
   ON DATABASE
BEGIN
   INSERT INTO DDL_AUDIT (DATE,
                              USERNAME,
                              OSUSER,
                              MACHINE,
                              PROGRAM,
                              OPERATION,
                              OBJTYPE,
                              OBJOWNER,
                              OBJNAME)
        VALUES (SYSDATE,
                SYS_CONTEXT ('USERENV', 'SESSION_USER'),
                SYS_CONTEXT ('USERENV', 'OS_USER'),
                SYS_CONTEXT ('USERENV', 'HOST'),
                SYS_CONTEXT ('USERENV', 'MODULE'),
                ORA_SYSEVENT,
                ORA_DICT_OBJ_TYPE,
                ORA_DICT_OBJ_OWNER,
                ORA_DICT_OBJ_NAME);
END;
/

And we are done!