Pete Finnigan's Oracle Security Weblog

"This is a very good question but one I am afraid we probably do not have a quick easy answer for. If you could move the table to its own tablespace you would control the datafile and could then drop it, secure delete and then recreate BUT as you have already said the problem is the data in the existing datafile.

I am sure that you have seen the many papers on Oracle forensics that discuss the issues of data recovery from Oracle data blocks. Oracle has a tendancy to not overwrite existing data for some time after its updated or deleted. This is great for forensics but not if you want rid of the data completely.

The second problem with Oracle is its tendancy to replicate data all over the place, shared memory, redo, archivelogs, flashback, actual data files, audit trails....

Maybe; maybe you are coming at the problem from the wrong end. You seem to want to update application authentication but remove old authentication details / data at the same time - presumably because they are either in clear text or very simply encoded/hashed/encrypted.

Why not do your update to the new algorithm/storage but force all users to have new passwords that also must be expired to force change on use. This way any data that could be read from datafiles, memory, redo, archivelog etc is redundant; no longer valid; therefore no use to anyone who can read it?"

• CREATE SESSION


• SELECT ON SYS.USER$

SQL> connect sys/oracle1 as sysdba
Connected.
SQL> create user cracker identified by cracker;

User created.

SQL> grant create session to cracker;

Grant succeeded.

SQL> grant select on sys.user$ to cracker;

Grant succeeded.

SQL> connect cracker/cracker
Connected.
SQL> set serveroutput on size 1000000
SQL> @cracker-v2.0.sql
cracker: Release 1.0.4.0.0 - Beta on Wed Nov 26 14:00:41 2008
Copyright (c) 2008 PeteFinnigan.com Limited. All rights reserved.

T Username             Password               CR FL STA
=======================================================

U "SYS"                [ORACLE1             ] DI CR OP
U "SYSTEM"             [ORACLE1             ] DI CR OP
U "OUTLN"              [OUTLN               ] DE CR EL
U "DIP"                [DIP                 ] DE CR EL
U "TSMSYS"             [TSMSYS              ] PU CR EL
U "ORACLE_OCM"         [ORACLE_OCM          ] PU CR EL
U "XDB"                [CHANGE_ON_INSTALL   ] DE CR EL
R "GLOBAL_AQ_USER_ROLE [GL-EX {GLOBAL}      ] GE CR OP
U "DBSNMP"             [ORACLE1             ] DI CR OP
U "WMSYS"              [WMSYS               ] DE CR EL
U "EXFSYS"             [EXFSYS              ] DE CR EL
U "CTXSYS"             [CHANGE_ON_INSTALL   ] DE CR EL
U "XS$NULL"            [                    ] -- -- EL
U "ANONYMOUS"          [IMP {anonymous}     ] IM CR EL
R "SPATIAL_WFS_ADMIN"  [SPATIAL_WFS_ADMIN   ] PU CR OP
U "ORDSYS"             [ORDSYS              ] DE CR EL
U "ORDPLUGINS"         [ORDPLUGINS          ] DE CR EL
U "SI_INFORMTN_SCHEMA" [SI_INFORMTN_SCHEMA  ] DE CR EL
U "MDSYS"              [MDSYS               ] DE CR EL
U "OLAPSYS"            [                    ] -- -- EL
U "MDDATA"             [MDDATA              ] DE CR EL
U "HR"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "SPATIAL_WFS_ADMIN_U [SPATIAL_WFS_ADMIN_US] PU CR EL
R "WFS_USR_ROLE"       [WFS_USR_ROLE        ] PU CR OP
R "SPATIAL_CSW_ADMIN"  [SPATIAL_CSW_ADMIN   ] PU CR OP
U "SPATIAL_CSW_ADMIN_U [SPATIAL_CSW_ADMIN_US] PU CR EL
R "CSW_USR_ROLE"       [CSW_USR_ROLE        ] PU CR OP
U "WKSYS"              [CHANGE_ON_INSTALL   ] DE CR EL
U "WKPROXY"            [CHANGE_ON_INSTALL   ] DE CR EL
U "WK_TEST"            [WK_TEST             ] DE CR EL
U "SYSMAN"             [ORACLE1             ] DI CR OP
U "MGMT_VIEW"          [                    ] -- -- OP
U "FLOWS_FILES"        [                    ] -- -- EL
U "APEX_PUBLIC_USER"   [                    ] -- -- EL
U "FLOWS_030000"       [                    ] -- -- EL
U "OWBSYS"             [OWBSYS              ] PU CR EL
R "OWB$CLIENT"         [S                   ] BF CR OP
R "OWB_DESIGNCENTER_VI [S                   ] BF CR OP
U "SCOTT"              [TIGER               ] DE CR EG
U "AB"                 [AB                  ] PU CR OP
U "OE"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "IX"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "SH"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "PM"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "BI"                 [CHANGE_ON_INSTALL   ] DE CR EL
U "PETE"               [PETE                ] DE CR OP
U "BILL"               [BILL                ] PU CR OP
U "A"                  [A                   ] PU CR OP
U "B"                  [B                   ] PU CR OP
U "C"                  [C                   ] PU CR OP
U "RES_TEST"           [RES_TEST            ] PU CR OP
U "XX"                 [123456              ] DI CR OP
U "ORASCAN"            [ORASCAN             ] PU CR OP
U "IMPOSS"             [IMP {imposs123456789] IM CR OP
U "D"                  [                    ] -- -- OP
U "P1"                 [P1                  ] PU CR OP
U "P2"                 [P2                  ] PU CR OP
U "CRACKER"            [CRACKER             ] PU CR OP
U "B1"                 [B1                  ] PU CR OP
U "LT_EXP"             [LT_EXP              ] PU CR OP


INFO: Number of crack attempts = [61791]
INFO: Elapsed time = [4.31 Seconds]
INFO: Cracks per second = [14330]

PL/SQL procedure successfully completed.

SQL>

There seems to be some confusion in some of what you report below. The
first is that you say that AppDetective reports 2000 issues of grants
against objects to PUBLIC. In 11g, the number is

SQL> select count(*) from dba_tab_privs
2 where grantee='PUBLIC';

COUNT(*)
----------
27467

SQL>

In 10gR2 its around 21.5K and in 91R2 its around 12K. This would suggest
that AppDetective is picking out a large subset of objects, some key
things like packages that manipulate web content such as OWA_COOKIE and
probably all views with the ALL_% in the name BUT certainly not
reporting all grants to PUBLIC. Most views with ALL_% have some issue
for security. The problem is that you make the mistake that just because
a user/schema has access to objects of the type exposed by a view (one
poster talked about ALL_TABLES) that its OK for the "real person" who
accesses that user/schema to see what privilege the user/schema has.
This is not always the case that its OK, Imagine that an application
schema has access to ALL_TABLES, it does by defailt via PUBLIC, it can
see all the application schema, lost of useful data for instance for
someone who wants to steal; say credit cards, it makes it easier to find
them. The problem in this example is that if the application is
breakable then anyone gaining access also has access to these views. So
its best to revoke them. The issue is that because this privileges are
granted to PUBLIC its world-wide across the database. I agree that some
should be revoked (ALL_USERS for instance is a very good example to
prevent enumeration of usernames) from PUBLIC, then if the access is
genuinely required by a user/schema, consider it carefully and grant it
back again to just that user/schema and no more.

The issue with revoking from PUBLIC are that:

1) any upgrade/patch may break if it relies on access to a particular view
2) The upgrade/ patch often puts the PUBLIC privilege back again.
3) Running catproc can also put some PUBLIC privileges back again
4) finding which users/schemas need access to the ALL_% views is
tedious. If everything is static you can check for invalid objects,
revoke the privilege, check again for invalid objects, grant the
privilege to the users/schemas who own the objects, recompile.
5) The above works for static code, if there is embedded dymamic code
that used the view/package it doesnt work. If there is external code
that uses it; again it doesnt work.

Revoking 2000 public grants is unrealistic BUT it can be done with a lot
of careful work, full understanding of the schemas installed and
application code. I do know customers of mine who have revoked quite a
lot of public privileges. Keeping them revoked is a big job also that
must be automated.

In your second point you talk about "System privilege granted to public"
128 violations BUT the examples are not grants to PUBLIC? also:

SQL> select count(*) from dba_sys_privs
2 where grantee = 'PUBLIC';

COUNT(*)
----------
0

SQL>

There have never been system privileges granted to public by default and the descriptions reported are not in fact system privileges granted to PUBLIC. I
suspect a bug in the AppDetective descriptions or perhaps your interpretation in this question?

So in summary, I can see the benefits of revoking key PUBLIC privileges
BUT you must understand the process completely, TEST and also make sure
tha changes remain in place.