Mark Malakanov poorly maintained blog

How to recover a dropped or overriden PL/SQL using Flashback.

2010-01-23T09:46:00.000-08:00

Well, things happen. Just recently I've mistakenly run an older script that does "CREATE OR REPLACE PACKAGE ...". :(
And that happen the latest changes I've made using SQL*Developer have gone also. :(

So, what to do?
I could restore the entire DB to the point in time before the change.
But, I just was too lazy to do it. Also, in some cases such a global approach can eliminate other recent works. It was not mine case though, but you have to consider it.

Fortunately Oracle has a Flashback!

So I decided to try this. My plan was to execute following

SELECT * FROM DBA_SOURCE AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Here 1.0.1.38 is a version number in a comment line that I usually embed into my code.
And the older version of my PL/SQL, that had been written over is 1.0.1.37.
So, I needed my 1.0.1.38 back.
However the result returned by the above query always was the current package code with a version 1.0.1.37, which was not my target.

Then I've decided to try with querying the table directly. The table is SYS.SOURCE$.

SELECT * FROM SYS.SOURCE$ AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Voila! It was there!

Then I just took the proper OBJ# and spooled all rows of that object into a text file.
Ta-daa!

However, to be successful with this method, you have to be really quick.
Otherwise you can receive following error if some significant time has gone from the moment of overriding.

ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1192467665$" too small
01555. 00000 - "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause: rollback records needed by a reader for consistent read are
overwritten by other writers
*Action: If in Automatic Undo Management mode, increase undo_retention
setting. Otherwise, use larger rollback segments

In this case you have to resort to traditional recovery methods, like a point-in-time DB recovery or an import, or a Log Miner if archived logs for that time have not been removed.

Oracle Audit Vault 10g against 11g source.

2008-02-23T17:26:00.000-08:00

When Oracle Audit Vault is being configured to collect audit data from Oracle 11g database, it can throw Java SQL exception originated from ORA-27452 when you add REDO Collector.
In a log file you can find the call of dbms_src_streams_collector.setup_collector(...av_dbname => 'AV.REGRESS.RDBMS.DEV.US.ORACLE.COM' ...)
The default global name of AV Server database (AV.REGRESS.RDBMS.DEV.US.ORACLE.COM) is longer than 30 characters, this probably causes an error ORA-27452 during adding of REDO Collector.
To work this issue around I had to rename the global name of AV Server DB to something shorter.

How to call Oracle stored procedure from MS SQL

2008-01-21T13:21:00.000-08:00

It is really a pain if you need to call Oracle stored procedure from MSSQL.
MSSQL throws an error 7212 if remote procedure does not return any recordset. Oracle procedures usually don't return recordset.
One way was to create a dummy table, create trigger on it, from where stored proc is called. Then to update this table from MSSQL.

Matt Meleski has published really excellent way of calling oracle stored procedures from MS SQL! It does not require the trigger at all!
He's placed quite thorow example on his BLOG http://objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx

By the way, the example can be simplified to very short code. Main thing - the Oracle stored procedure must have at least one output parameter type of indexed PL/SQL table. Indexed - it is important. Instead of creating a custom type in PL/SQL package, one can use readily available DBMS_OUTPUT.CHARARR.

Here is an example.

Make stored proc in Oracle:

create or replace procedure SCOTT.PROC4MS(OUTPUT OUT dbms_output.chararr) is
begin
-- you do not have to assign anything to OUTPUT if you don't need it.
OUTPUT(1):='one';
OUTPUT(2):='two';
OUTPUT(3):='three';
end;
/

Then call it from MS SQL. And here is the trick - use curly braces and a magic "resultset 25" parameter.

select * from openquery(MYORADB, '{CALL SCOTT.PROC4MS({resultset 25, OUTPUT})}');

Enjoy!

Other helpful links:
- How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP http://support.microsoft.com/kb/229919

SSIS 2005. Fast load into non-MS OLE DB destination

2007-07-27T17:24:00.000-07:00

SSIS 2005. How to avoid Autocommit when load into OLE DB destination.

An example with using of Oracle OLE DB driver on a destination side.

I have heard from my friend that he is experiencing very slow load into Oracle database. He used SSIS 2005. Which is funny, because Oracle Warehouse Builder is available. Anyway.
The cause of slowness of SSIS, when it loads into non-Microsoft OLE DB target, appeared to be the commit after every insert. Since the ‘Autocommit’ is set to be ‘ON’ by OLE DB standard, a driver propagates this mode to the database. The only way to turn it off, is to switch OLE DB datasource into a Transactional mode by issuing a BeginTransaction() call.

SSIS does issue this call when Batch Size option is specified for MS SQL OLE DB Native Sqlserver driver. But it does not do it for any other OLE DB driver like Oracle’s or even for MSSQL .Net OLE DB.

As a result, SSIS demonstrates very poor performance when loading into Oracle OLEDB Destination.

What to do?

I’ve suggested to start transaction in Destination OLE DB session before any insert comes, then do a Commit and start transaction again after some number of rows inserted in order to simulate “Batch size”.

How to do?

I would be curious to know if anybody have found a more elegant and efficient way to fix this situation, however my way is by using Script Component.

Create Any kind of Data Source, i.e. an OLE DB Source. It should have fields that can be easily put into a target table.
Add Script Component as a Destination and feed it from data source above.
Double-click on it to open Script Transformation Editor
Select input columns

Select Connection Manager form where add New connection manager.

Rename new “Connection” to “oraxeConnection” just for distinction.

In Connection Manager Column choose “New Connection” item

Select “ADO.NET” type

In a dialog “Configure OLE DB Connection Manager” click New.

Choose Provider. In my simple case “.Net Providers for OleDb\Microsoft OLE DB Provider for SQL Server”

Test and click OK

Click OK

Choose newly created Connection Manager in “Connection Manager” column

Select “Script” item in left list

Click [Design Script…] button

Create following script

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb
Imports System.Data.Common

Public Class ScriptMain
Inherits UserComponent

Dim rowcnt As Int64
Dim out_batch_sz As Int64

Dim connMgr As IDTSConnectionManager90
Dim oledbconn As OleDbConnection
Dim oledbtran As OleDbTransaction
Dim oledbCmd As OleDbCommand
Dim oledbParam As OleDbParameter

Public Overrides Sub PreExecute()
out_batch_sz = 8 * 1024
rowcnt = 0
'MsgBox("PreExecute")

oledbCmd = New OleDbCommand("INSERT INTO MM_TST_B2(C, D) VALUES(?, ?)", oledbconn)
oledbParam = New OleDbParameter("@C", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@D", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)

oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("First Transaction began")
MyBase.PreExecute()
End Sub

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.oraxeConnection
oledbconn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
'MsgBox(oledbconn.Provider & " " & oledbconn.ConnectionString)
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With oledbCmd
.Parameters("@C").Value = Row.C
.Parameters("@D").Value = Row.D
.ExecuteNonQuery()
End With

rowcnt = rowcnt + 1
If (rowcnt Mod out_batch_sz) = 0 Then
MsgBox("Batch Commit " & rowcnt)
oledbtran.Commit()
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("Batch Transaction began")
End If
End Sub

Public Overrides Sub PostExecute()
'MsgBox("PostExecute")
MyBase.PostExecute()
End Sub

Public Overrides Sub ReleaseConnections()
'MsgBox("ReleaseConnections")
oledbtran.Commit()
MyBase.ReleaseConnections()
End Sub

Save and exit.
Run

Comparison of this Script Destination method with the basic OLE DB Destination, where both work via the same Oracle OLE DB Driver, shows that the Script method is times faster.

Restoration of RPM database.

2006-08-09T08:12:00.000-07:00

This article may help you, if your RPM database has corrupted, and that’s what happened to mine. In fact, I tried to use `rpm --rebulddb' command on it in an attempt to fix it. Unfortunately, most of package’s meta-information had gone. However, all, or so I hoped, files remained on the filesystem. I have decided that this could be a my source of restoration of the RPM database.
Fortunately( to me :) ) other people have encountered this problem already, and some solutions exist. Here is the URL for this document: http://www.informatimago.com/linux/rpm-rebuilddb.html. This one actually worked for me as a starting point. It is based on comparison of actual files and rpm'ed files by names, deriving an rpm name where filenames match. It could have been a good solution, if there would be only one version of rpm packages. But in real life we have many. I have downloaded all packages from http://download.fedoralegacy.org/redhat/9/os/i386/ and http://download.fedoralegacy.org/redhat/9/updates/i386/. As a result, there were multiple versions of packages. Which one to apply? I have decided to use some more information about the file that will uniquely identify it. Sure it was MD5 check sum. The idea was to create two lists, speaking relation database language, tables, and then to join them.
Table DISK_CONTENT ( filename, bytesize, md5sum )
Table RPM_CONTENT ( rpmfullname, filename, bytesize, md5sum )

Then I planned to load the data into these tables for joining using SQL language. When joining, ideally, a package suggested to be previously installed if all of its files exist on the filesystem, and the sizes and MD5 checksums are identical. But, some files, like config files, can be modified during or after installation, and the size/md5 will not match. Thus I decided to assign a merit 0 (zero) to files that md5 match, merit 1 where just size matches, merit 3 where only name matches, and merit 4 where file exist in a package but does not exist on the filesystem.

So, I started from gathering the metadata of actual files

1. On a machine where RPM database has been severely damaged I gather information about all files, including absolute path, byte size and md5 checksum

find / -fstype ext3 -type f -printf '%s' -exec md5sum {} \; >/tmp/disk-contents

note: I have only ext3 filesystems, thus I specified -fstype ext3 to exclude /proc and network mounted filesystems. Ideally /tmp, /opt and /home should be excluded as well, but I didn't bother.

The format of a disk-contents file is "filenamesizemd5sumfilename", because md5sum just types this way. I removed the last filename using sed.

sed 's/ \//\\//' /tmp/disk-contents >/tmp/disk-contents-n

2. On a machine where I have all rpms downloaded I gathered meta-information about all files that sit inside rpms, including the rpm file name, absolute path, byte size and md5 checksum
Note: it can be the same machine, but due to space constraints I made it on a separate one. It can sound funny, but I've done it on a Windows machine using Cygwin's utilities, bash and rpm. (yes we can have rpm in windows!)

RPMDIR=g:/download/Linux/RH9/os cd $RPMDIR rm /tmp/rpm-contents
for f in *.rpm ; do rpm -q --nopgp --queryformat='[%{FILENAMES}%{FILESIZES}%{FILEMD5S}'$f'\n]' -p $f >> /tmp/rpm-contents done

I decided to introduce one more table that keeps some info about the whole RPM. Particularly, the fields NAME, PROVIDES, VERSION. That will allow me to group by NAME to group together packages of different versions like lvm-1.0.3-12.i386.rpm and lvm-1.0.3-12.1.legacy.i386.rpm. Why do this? The idea is to check all the files for packages. The package that is most closely presented by the filesystem files will win. (Later I found that the fields PROVIDES and VERSION are not necessery)

RPMDIR=g:/download/Linux/RH9/os cd $RPMDIR rm /tmp/rpm-info
for f in *.rpm ; do rpm -q --nopgp --queryformat=$f'%{NAME}%{VERSION}%{PROVIDES}%{PROVIDENAME}%{PROVIDEVERSION}\n' -p $f >> /tmp/rpm-info
done

3. Load to RDBMS. I decided to go with MySQL(5.0), although I prefer Oracle, I thought that MySQL example can be more helpful to linux people, because of its simplicity and availability.

drop table DISK_CONTENT;

create table DISK_CONTENT (bytes decimal, md5sum varchar(64), filename varchar(2000) );

load data infile 'E:/cygwin/tmp/disk-contents-n' into table DISK_CONTENT fields terminated by '';

create index DISK_CONTENT_IX01 on DISK_CONTENT( filename );

drop table RPM_CONTENT;

create table RPM_CONTENT (filename varchar(2000), bytes decimal, md5sum varchar(64), rpmfilename varchar(128));

load data infile 'E:/cygwin/tmp/rpm-contents' into table RPM_CONTENT fields terminated by '';

alter table RPM_CONTENT drop index RPM_CONTENT_IX01;

alter table RPM_CONTENT add index RPM_CONTENT_IX01 ( filename ); alter table RPM_CONTENT drop index RPM_CONTENT_IX02;

alter table RPM_CONTENT add index RPM_CONTENT_IX02 ( rpmfilename );

drop table RPM_INFO;

create table RPM_INFO (rpmfilename varchar(128), name varchar(128), version varchar(128), provides varchar(128), providename varchar(128), provideversion varchar(128));

load data infile 'E:/cygwin/tmp/rpm-info' into table RPM_INFO fields terminated by '';

alter table RPM_INFO add index RPM_INFO_IX01 ( rpmfilename );

Good. All files metadata has been loaded successfully.

4. Deriving of RPMs. Now I need to find out the RPMs that are supposedly have been previously installed. I wanted to do that in a single query:

select distinct rpmfilename from ( select rpmfilename, case when name=@N then @R:=@R+1 else @R:=1 end Rank, @N:=name r from (select @R:=1, @N:='') r, (select name, rpmfilename, sum(proximity) sumprox from (select I.name, R.filename R_filename, D.filename D_filename, case when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum = R.md5sum) then 0 when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum != R.md5sum) then 1 when (D.filename is not NULL and D.bytes=R.bytes and R.md5sum is NULL ) then 2 when (D.filename is not NULL and D.bytes!=R.bytes) then 3 when (D.filename is NULL) then 4 end proximity, R.rpmfilename, D.md5sum D_md5sum, R.md5sum R_md5sum from RPM_INFO I inner join RPM_CONTENT R on I.rpmfilename=R.rpmfilename left outer join DISK_CONTENT D on D.filename=R.filename /*where R.filename like '/sbin/%'*/ /*order by 1, 4*/ ) v group by name, rpmfilename having count(D_filename)>0 order by 1, 3) v2 ) v3 where Rank=1;

This query works but it requires enormous amount of space for temporary results, gigabytes. I had just 2.5 Gb of free space in my %TEMP% disk, so it has failed.
To resolve this, I’ve split this query to be run sequentially, implicitly storing temporary results in a table rpm_files_tmp.

drop table rpm_files_tmp;

/* place the join into a temp table*/
create table rpm_files_tmp as
select I.name, R.rpmfilename, D.filename D_filename, /*R.filename R_filename, */
case
when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum = R.md5sum) then 0
when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum != R.md5sum) then 1
when (D.filename is not NULL and D.bytes=R.bytes and R.md5sum is NULL ) then 2
when (D.filename is not NULL and D.bytes!=R.bytes) then 3 when (D.filename is NULL) then 4
end proximity
from RPM_INFO I
inner join RPM_CONTENT R on I.rpmfilename=R.rpmfilename
left outer join DISK_CONTENT D on D.filename=R.filename;

/* here I rank packages by a composite merit - sum(proximity), which ideally equals 0. But may be greater, when some files don't match exactly or absent. I selected packages with a best possible match.*/
select distinct rpmfilename into outfile 'E:/cygwin/tmp/rpm-to-install.lst' lines terminated by '\n'
from ( select rpmfilename, case when name=@N then @R:=@R+1 else @R:=1 end Rank, @N:=name r
from (select @R:=1, @N:='') r,
(select name, rpmfilename, sum(proximity) sumprox
from rpm_files_tmp v
group by name, rpmfilename
having count(D_filename)>0
order by 1, 3) v2 ) v3
where Rank=1;

drop table rpm_files_tmp;

5. Install rpms from the list produced in previous step.

# Reinitialize a new rpm database.
mv /var/lib/rpm /var/lib/rpm-damaged-beyond-hope mkdir /var/lib/rpm rpm --initdb

# install GPG keys rpm --import http://www.redhat.com/security/db42a60e.txt rpm --import http://www.fedoralegacy.org/FEDORA-LEGACY-GPG-KEY

# install packages into RPM database only, do not touch files on filesystems.
rpm -i -v --justdb --nodeps --noscripts --notriggers `cat ~/rpm/rpm-to-install.lst`

# check
rpm -qa

Done!

Oracle ANSI join security bug. Bad news again.

2006-05-12T05:36:00.000-07:00

Trying to figure out a workaround, I have found that these DMLs that break authorization, also can not be AUDIT TRAILED.

Followup: Oracle block size limit on different platforms

2006-05-04T08:36:00.000-07:00

There was a long and annoying dispute on one of the forums about why a maximum block size on Windows (and Linux) platform is 16K, while on "serious" platforms like HP, and Sun, it is 32K. I had a wild suggestion, a bold guess, that the limit has been set based on some business strategy, that should push users to migrate from "minor" platforms towards "serious" ones. My assumption was (and still is) based on a fact, that couple of years ago Oracle had different prices for Intel and RISC CPUs, and having a 32Kb block size could be one more reason for users to migrate on more expensive RISC platforms.I never believed in arguments of other people that the 16K limitation had been caused by some technical deficiencies of "minor" platforms. Sure, it is clear that in Windows, in Linux and even in DOS, it is possible to do I/O by 32Kb chunks. However, nobody could neither confirm nor disprove that the idea of 16Kb limit was a business driven decision. Bill S, has kindly contacted Tom Kyte and the OakTable members. But, to my understanding, nobody could remember the reasoning (http://dizwell.com/forum/index.php?topic=253.30). So, I have decided to contact Oracle Support. I have created a "Request for Improvement". My idea was: Support people may point out for me a technical incapability of "minor" platforms, if there is such.Oracle had accepted this RFI and registered as a Bug(!) #5196815. Funny, they call requests for improvements "bugs". May be because people are bugging them by their requests? :)))
BLOCK SIZE LIMIT THE SAME ON ALL PLATFORMS Doc ID 5196815
https://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=BUG&p_id=5196815

However, they have not answered why it had been decided to have 16K on Windows and Linux. That indirectly confirms my suggestion about a "business driven decision".

Oracle-like ROWNUM in MySQL

2006-04-30T06:47:00.000-07:00

It needs sometimes to exactly mimic Oracle's ROWNUM where is no possibility to initiate a counter in previous statement by SET @rownum:=0;.

It is still possible in a single SQL.

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;

Things are even worse

2006-04-28T19:28:00.000-07:00

Today I have found that this vulnerability can be exploited not only when a select privilege granted on a table, but also if select has been granted to a view. The view must keep keys of table though, that is to be updateable.
Even if a view is not updateable, oracle passes DMLs through security control, then it says that view is not updateable.
Select on a view can be granted to user directly or via role, or via chain of roles, it does not make a difference.

I have granted SELECT_CATALOG_ROLE to a role that is granted to another role that is granted to an account ("hacker"). And the attack returned

HACKER>update (...censored code to access DBA_USERS...)
4 set password='xxx' where username='HACKER'

set password='xxx' where username='HACKER'
*ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

It means that if the view would preserve a key, that would be updateable.
And there are many views in SYS that are updatable.
Resume: be very careful when grant any access to dictionary views

Oracle security bug. select privilege escalation via view

2006-04-27T21:08:00.000-07:00

I already read about the bug on Red Database Security page "Read-only user can modify data" http://www.red-database-security.com/advisory/oracle_modify_data_via_views.html

This problem has been discussed on some web forums. However no clear mitigation instructions were proposed. Initially it was simple: revoke CREATE VIEW from CONNECT role and enjoy. But Andrew Max has discovered something more dangerous. <http://andrewmax.blogspot.com/2006/04/yet-another-security-alert.html>
It has not taken too much of my time to crack oracle security just having "CREATE SESSION" system privilege and "SELECT" privilege on a table of other user (see example on Andrew Max blog). I will not provide you with details how I have managed to crack an oracle authorization control.
But I will try to provide you with some recommendations how temporarily to mitigate the threat.
First of all, check what accounts in database have direct "SELECT" privilege on tables of other's accounts. Check both the privilege that has been granted directly to account, and granted via some role or chain of roles.

Here is an example. I create users "hacker" and "towner" (table owner).
Hacker is a regular user that has only CREATE SESSION system privilege.
Towner will have a table and grant select to hacker, just SELECT object privilege.
I will not repeat the attack here. Trust me, hacker account can do whatever he wants with data in towner.emp table. That is delete, insert and update.

SQL> connect system...
SQL>create user towner identified by password 2 default tablespace users quota 1M on users;
User created.
SQL>create user hacker identified by password;
User created.
SQL>grant create session to hacker ;
Grant succeeded.
SQL>create table towner.emp (id number primary key, sal number) tablespace users;
Table created.
SQL>insert into towner.emp values (1,100);
1 row created.
SQL>commit;
Commit complete.
SQL>SQL>grant select on towner.emp to hacker ;
Grant succeeded.
SQL> SELECT U.USERNAME, U.ACCOUNT_STATUS, U.LOCK_DATE
FROM DBA_USERS U WHERE ACCOUNT_STATUS='OPEN';

USERNAME ACCOUNT_STATUS
------------------------------ ----------------
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
HACKER OPEN
TOWNER OPEN

We do not bother about expired and locked accounts.
We also can trust SYS, SYSTEM and DBSNMP*.
Lets look who have direct access to somebody's tables (this SQL does not show privileges acquired via roles, may be later I will write this. However the procedure below does traverse roles.)

SQL>col GRANTEE format A10
SQL>col TABLE format A40
SQL>SELECT P.GRANTEE, P.OWNER'.'P.TABLE_NAME "TABLE"
FROM DBA_TAB_PRIVS P, DBA_TABLES T, DBA_USERS U
WHERE P.OWNER=T.OWNER AND P.TABLE_NAME=T.TABLE_NAME
AND P.GRANTEE=U.USERNAME
AND U.ACCOUNT_STATUS='OPEN'
AND P.PRIVILEGE='SELECT'
AND P.GRANTEE not in ('SYS','SYSTEM')ORDER BY 1,2;
GRANTEE TABLE
---------- -----------------------
HACKER TOWNER.EMP
ETLJOB FINANCE.GENERAL_LEDGER

We see that above tables are potentially vulnerable for privilege escalation attack.
Further we should figure out, who/what are these HACKER and REPORTJOB accounts. We should find out why these accounts do need direct SELECT privilege to tables.
Lets imagine that HACKER is a legitimate human user (not a real hacker at all). All he needs is to run his reports.
ETLJOB is an account that is used by some ETL procedure to load data warehouse.
It is totally on your discretion, to trust or not to trust.
How we can make sure that these accounts can not be used for attack?
The only way I see to temporary mitigate this vulnerability is to create triggers. Statement level triggers before insert/update/delete. As a fast remedy, triggers can hardcode usernames, allowing some users to proceed, and rising an exception for others. It is very simple and fast, but may be not flexible enough in more or less rapidly changing environments or databases with large number of users and complex role based security. It can create maintenance problem when DBA has to ajust triggers all the time after users permissions or roles changed. Better way to do it is to mimic Oracle's proper, planned behavior. Oracle gets information about access privileges from its dictionary, so we can do.
Here is an example of such trigger. I just have created it, did not test it thoroughly, did not benchmark. Thus you should test it if you going to apply it.

I will create procedure in a SYS schema first, to get easy acces to dictionary views. This proc checks if user has Insert, Update or Delete privileges on given table, directly or via roles.

create or replace procedure check_obj_privs(
owner in string, table_name in string, grantee in string,
insertable out char, updateable out char, deleteable out char
) as
owner_ DBA_TAB_PRIVS.OWNER%TYPE:=owner;
table_name_ DBA_TAB_PRIVS.TABLE_NAME%TYPE:=table_name;
grantee_ DBA_TAB_PRIVS.GRANTEE%TYPE:=grantee;
begin
SELECT
max(case when OBJ_PRIV='INSERT' then 'Y'
when SYS_PRIV='INSERT ANY TABLE' then 'Y'
else 'N'
end ) INSERTABLE,
max(case when OBJ_PRIV='UPDATE' then 'Y'
when SYS_PRIV='UPDATE ANY TABLE' then 'Y'
else 'N'
end ) UPDATEABLE,
max(case when OBJ_PRIV='DELETE' then 'Y'
when SYS_PRIV='DELETE ANY TABLE' then 'Y'
else 'N'
end ) DELETEABLE
INTO insertable, updateable, deleteable
FROM (
SELECT P.PRIVILEGE OBJ_PRIV, S.PRIVILEGE SYS_PRIV
FROM
(SELECT ROLE, GRANTED_ROLE
FROM ROLE_ROLE_PRIVS P
CONNECT BY PRIOR GRANTED_ROLE = ROLE
START WITH ROLE IN (SELECT S.GRANTED_ROLE
FROM DBA_ROLE_PRIVS S WHERE GRANTEE=grantee_)) R
LEFT OUTER JOIN DBA_TAB_PRIVS P ON P.GRANTEE IN (R.GRANTED_ROLE, R.ROLE, grantee_)
LEFT OUTER JOIN DBA_SYS_PRIVS S ON S.GRANTEE IN (R.GRANTED_ROLE, R.ROLE, grantee_)
AND S.PRIVILEGE IN ('INSERT ANY TABLE','UPDATE ANY TABLE','DELETE ANY TABLE')
WHERE P.OWNER=owner_ AND P.TABLE_NAME=table_name_ );
end;
/

grant execute on sys.check_obj_privs to public;

Then I create trigger.

create or replace trigger towner.PROTECT_EMP_BIUD
before insert or update or delete
on towner.emp
declare i char; u char; d char;
begin
if user not in ('SYS','SYSTEM','TOWNER') then
sys.check_obj_privs( 'TOWNER', 'EMP', user, i, u, d);
--dbms_output.put_line(i':'u':'d);
if (inserting and i!='Y')
or(deleting and d!='Y')
or(updating and u!='Y')
then
raise_application_error(-20031, 'insufficient privileges');
end if;
end if;
end;
/

Test
HACKER>select * from session_roles;
ROLE
------------------------------
SELECT_TNAME_EMP_ROLE
1 row selected.
HACKER>select * from session_privs;
PRIVILEGE
-----------------------------------
CREATE SESSION
1 row selected.
HACKER>select id, sal from towner.emp;
ID SAL
---------- ----------
1 100
1 row selected.
Hacker can select, that is OK.
HACKER>update towner.emp set sal=0;
update towner.emp set sal=0
*
ERROR at line 1:ORA-01031: insufficient privileges
Hacker can not update, which is OK too.

HACKER>UPDATE
(censored code of specifically crafted dynamic view to TOWNER.EMP table)
SET SAL=0;

UPDATE
*
ERROR at line 1:
ORA-20031: insufficient privileges
ORA-06512: at "TOWNER.PROTECT_EMP_BIUD", line 13
ORA-04088: error during execution of trigger 'TOWNER.PROTECT_EMP_BIUD'

It works!