 |
|
Using Views as a Form of Access Control
Oracle Forensics tips by Paul Wright
|
VIEWs are also sometimes used as a form of
access control by Oracle, in the Author’s opinion they should not
be, but they are. For instance in the case of an undocumented VIEW
called KU$_USER_VIEW. This VIEW contains the passwords of the users
in an Oracle database, but the VIEW source code restricts the users
who can view it. The privileges on the VIEW are to public ROLE but
the actual source code in the VIEW checks who is reading the VIEW
and grants access based on that.
This view relies solely on the source code of
the view to stop PUBLIC users selecting from it as PUBLIC is granted
SELECT on this VIEW by default. The KU$_USER_VIEW or KU$_ROLE_VIEW
is a prime target for a rootkit and PUBLIC select should be revoked
from this undocumented VIEW.
SQL>
desc KU$_USER_VIEW;
Name
Null? Type
----------------------------------------- --------
---------------------------
VERS_MAJOR
CHAR(1)
VERS_MINOR
CHAR(1)
USER_ID
NUMBER
NAME
VARCHAR2(30)
TYPE_NUM NUMBER
PASSWORD
VARCHAR2(30)
DATATS
VARCHAR2(30)
TEMPTS
VARCHAR2(30)
CTIME
DATE
PTIME
DATE
EXPTIME
DATE
LTIME
DATE
PROFNUM
NUMBER
PROFNAME
VARCHAR2(30)
USER_AUDIT
VARCHAR2(38)
DEFROLE
NUMBER
DEFGRP_NUM
NUMBER
DEFGRP_SEQ_NUM
NUMBER
ASTATUS
NUMBER
SQL> select grantee from
dba_tab_privs where table_name='KU$_USER_VIEW';
GRANTEE
------------------------------
PUBLIC
This is a mislead privilege grant. Imagine
granting PUBLIC execute to a table with passwords in it.
conn
sys as sysdba
SQL> select name, password from KU$_USER_VIEW;
NAME
PASSWORD
------------------------------ ------------------------------
SCOTT
F894844C34402B67
MGMT_VIEW
4F538DF5F344F348
MDDATA
DF02A496267DEE66
NAME
PASSWORD
------------------------------ ------------------------------
SYSMAN
447B729161192C24
MDSYS
72979A94BAD2AF80
XDB
88D8364765FCE6AF
CTXSYS
71E687F036AD56E5
EXFSYS
66F4EF5650C20355
WMSYS
7C9BA362F8314299
NAME
PASSWORD
------------------------------ ------------------------------
DBSNMP
E066D214D5421CCC
TSMSYS
3DF26A8B17D0F29F
DMSYS
BFBA5A553FD9E28A
DIP
CE4A36B8E06CA59C
OUTLN
0F763FE382235763
SYSTEM
D4DF7931AB130E37
SYS
8F496E0A85640576
SQL> conn scott/tiger
Connected.
SQL> desc KU$_USER_VIEW;
ERROR:
ORA-04043: object KU$_USER_VIEW
does not exist
Question: How does this VIEW protect the
viewing of passwords since it is has SELECT granted to PUBLIC?
Answer: It is in the source code, which is not
good from a defense perspective as we shall see.
SQL>
set long 100000
SELECT owner, text
FROM all_views
WHERE owner = SYS
view_name = ‘KU$_USER_VIEW’;
OWNER
TEXT
------------------------------
-------------------------------------------------------------------
SYS
select '1','0',
u.user#,
u.name,
u.type#,
……..
OWNER
TEXT
------------------------------
-------------------------------------------------------------------
and cgm.status = 'ACTIVE'
and cgm.value = u.name), u.defschclass),
u.ext_username,
u.spare1,
u.spare2,
u.spare3,
u.spare4,
u.spare5,
u.spare6
from sys.user$ u,
sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
OWNER
TEXT
------------------------------
-------------------------------------------------------------------
where u.datats# = ts1.ts# AND
u.tempts# = ts2.ts# AND
u.type# = 1 AND
u.resource$ = p.profile#
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') = 0
OR EXISTS ( SELECT * FROM session_roles
WHERE role='SELECT_CATALOG_ROLE' ))
As
DBA we can change the source of the VIEW to
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') = 0
OR EXISTS ( SELECT username from dba_users ))
Now the attacker can VIEW the passwords
whenever they want with a low privileged account and there has been
no change to privileges or base tables. The only change is to the
source code of the VIEW. As we already know it is trivial to change
the hash to clear text (OHH).
What is needed is an integrity check for the
actual code that makes up the VIEW.
Here is a basic text output version. Note that
the source to VIEWS in sys.view$ is a LONG not varchar2 text.
SET
SERVEROUTPUT ON SIZE 1000000;
DECLARE
long_var LONG;
BEGIN
SELECT text INTO long_var
FROM dba_views
WHERE view_name='KU$_USER_VIEW';
DBMS_OUTPUT.PUT_LINE('The checksum dba_users is
'||dbms_utility.get_hash_value(long_var,1000000000,power(2,30)));
END;
/
SQL> SET SERVEROUTPUT ON SIZE
1000000;
SQL> DECLARE
2 long_var LONG;
3 BEGIN
4 SELECT text INTO long_var
5 FROM dba_views
6 WHERE view_name='KU$_USER_VIEW';
7 DBMS_OUTPUT.PUT_LINE('The checksum dba_users is
8 '||dbms_utility.get_hash_value(long_var,1000000000,power(2,30)));
9 END;
10 /
The checksum dba_users is
1646689215
PL/SQL procedure successfully
completed.
--use this to generate the number then run a
check to see if it is the same
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
long_var LONG;
BEGIN
SELECT text INTO long_var
FROM dba_views
WHERE view_name='KU$_USER_VIEW';
if dbms_utility.get_hash_value(long_var,1000000000,power(2,30)) =
1646689215
then DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is correct');
else
DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is not correct');
end if;
end;
/
Need to do this query without using the name of
the view from DBA_VIEWS.
SYS.VIEW$ is the base table.
SQL> desc sys.view$;
Name
Null? Type
OBJ#
NOT NULL NUMBER
AUDIT$
NOT NULL VARCHAR2(
COLS
NOT NULL NUMBER
INTCOLS
NOT NULL NUMBER
PROPERTY NOT NULL NUMBER
FLAGS
NOT NULL NUMBER
TEXTLENGTH NUMBER
TEXT
LONG
Better to use the base tables that contain the
VIEW source code instead of DBA_VIEWS in order to check the
integrity of the VIEW source code.
set long 4000 to be able to see all the code.
Source Text in this case is just one big LONG datatype i.e. like a
number.
Viewscheckums.sql ~ basic checksum query for a view
DECLARE
long_var LONG;
BEGIN
select sys.view$.text into long_var from sys.view$ left outer join
sys.obj$ on sys.view$.obj# = sys.obj$.obj# where sys.obj$.name='DBA_USERS';
if dbms_utility.get_hash_value(long_var,1000000000,power(2,30)) =
1958803667
then DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is correct');
else
DBMS_OUTPUT.PUT_LINE('The checksum for dba_users is not correct');
end if;
end;
/
These VIEW integrity checks should be done
before using the VIEWS to identify the integrity of OBJECTS as
illustrated in previous chapters.
Automating the collection of VIEW source code
metadata such as checksums, timestamps and file size is more complex
due to the source text being in a number format called LONG. The
next query deals with that and will allow for automatic collection
of checksums on VIEW source of a given schema. These checksums
should then be stored in the Depository for future comparison and
correlation with known good and known bad examples.
Automatedforensicviewstatecheck.sql
create
table VIEWSTATESPROBASE(OWNERIN VARCHAR2(30),USER$NAME
VARCHAR2(30),OBJ$OWNER VARCHAR2(30),
NAMEIN VARCHAR2(30),
SOURCE$OBJID NUMBER,
OBJ$TYPE VARCHAR2(30),
COUNTOUT NUMBER,
CTIMEOUT TIMESTAMP,
STIMEOUT TIMESTAMP,
LASTDDLOUT TIMESTAMP,
HASH NUMBER);
CREATE OR
REPLACE PROCEDURE VIEWSTATEPROBASE(OWNERIN VARCHAR2) AS TYPE C_TYPE
IS REF CURSOR;
CV6 C_TYPE;
USER$NAME VARCHAR2(30); --
OBJ$OWNER VARCHAR2(30);
NAMEIN VARCHAR2(30);
SOURCE$OBJID NUMBER;
OBJ$TYPE VARCHAR2(30);
COUNTOUT NUMBER;
CTIMEOUT TIMESTAMP;
STIMEOUT TIMESTAMP;
LASTDDLOUT TIMESTAMP;
long_var LONG;
HASH NUMBER;
BEGIN
OPEN CV6 FOR 'SELECT sys.user$.NAME , sys.obj$.owner#,
sys.obj$.NAME, sys.view$.obj#, sys.OBJ$.TYPE#, sys.view$.textlength,
ctime, stime, mtime from (sys.view$ join sys.obj$
ON sys.view$.obj#=sys.obj$.obj#)
inner join sys.user$ ON sys.obj$.owner# = sys.user$.user#
where sys.obj$.TYPE#=4 and sys.view$.textlength < 4000
And sys.user$.NAME = :x GROUP BY sys.user$.NAME,
sys.obj$.owner#, sys.obj$.NAME, sys.view$.obj#, sys.OBJ$.TYPE#,
sys.view$.textlength, ctime, stime, mtime' using OWNERIN;
LOOP
FETCH CV6 INTO USER$NAME, OBJ$OWNER, NAMEIN, SOURCE$OBJID, OBJ$TYPE,
COUNTOUT, CTIMEOUT, STIMEOUT,
LASTDDLOUT;
DBMS_OUTPUT.ENABLE(200000);
SELECT SYS.VIEW$.TEXT INTO long_var FROM SYS.VIEW$ WHERE
sys.view$.obj#=SOURCE$OBJID;
SELECT SUM(dbms_utility.get_hash_value(long_var,1000000000,power(2,30)))
INTO HASH from sys.view$ where
sys.view$.obj#=SOURCE$OBJID;
DBMS_OUTPUT.PUT_LINE(OWNERIN||','||USER$NAME||','||OBJ$OWNER||','||NAMEIN||','||
SOURCE$OBJID||','||OBJ$TYPE||','||COUNTOUT||','||CTIMEOUT||','||STIMEOUT||','||
LASTDDLOUT||','||HASH);
insert into VIEWSTATESPROBASE
values(OWNERIN,USER$NAME,OBJ$OWNER,NAMEIN,SOURCE$OBJID,OBJ$TYPE,COUNTOUT,
CTIMEOUT, STIMEOUT,LASTDDLOUT,HASH);
COMMIT;
long_var:=0;
HASH := 0;
EXIT WHEN CV6%NOTFOUND;
END LOOP;
CLOSE CV6;
END;
/
show errors
--EXEC VIEWSTATEPROBASE('SYS');
--SELECT * FROM VIEWSTATESPROBASE;
--TRUNCATE TABLE VIEWSTATESPROBASE;
Once again the dblink syntax can be used on the
table references in order to run this query from the Depository
against the target DB remotely as in section 11.8. Please note the
above code works on VIEWS with source code less than LONG 4000 which
is the majority.
Other types of malware apart from modified
VIEWS could include backdoored Oracle patches or a Windows Oracle
client that has been changed to sniff the database traffic. Free
database development tools could also be backdoored. Therefore it is
worth integrity checking patches and software and using free
software that comes with the source code which has been subject to
source code review. Known good hashes for all Oracle software should
be made and kept in the Depository. Examples of known bad checksums
could be the Voyager worm PoC and the procedure at OxDEADBEEF used
to run SQL as another user mentioned previously.
This is an excerpt from the book "Oracle
Forensics: Oracle Security Best Practices", by Paul M. Wright,
the father of Oracle Forensics.
