Jerry Held

1
(No Transcript)
2
Session id 36652
Oracle Sleuth Who Did It?
Sitansu S. MittraSenior Principal Engineer
Database Management Specialist
Computer Sciences Corporation
3
Topic Outline

• Prevention of unauthorized user access roles,
  privileges, profiles
  
• Detection of harmful activities AUDIT TRAIL,
  LogMiner
  
• Correction of impact of activities via undo and
  redo entries LogMiner
  
• Roadmap for Ensuring Data Security in Oracle
  Databases
  
• Bibliography

4
Prevention of Unauthorized User Access

• Create a user account .
• Assign one or more roles to the user .
• Assign system and object level privileges to each
  role
  
• Set up a profile for the user or use DEFAULT
  profile.

5
Relationship among users, roles, privileges, and
profiles MN USER -------- ROLE MN ROLE ------ PRIVILEGE M1 USER ----------------- PROFILE Role named collect
ion of system or object level privileges that
are granted to the role
6
A role can be granted to a user or to another
role. Privilege capability to perform authoriz
ed function(s) in the database
A system privilege enables a user to perform
designated tasks in the database. There are 126
such privileges that can be listed by the query
select distinct NAME from system_privilege_map
order by 1
7
An object privilege enables a user to perform
primarily DML type tasks (INSERT, UPDATE, DELETE)
on designated objects (tables, views, sequences,
procedures) in the database. There are 8 such
privileges GRANT Privileges are assigned to
roles or users and roles are assigned to other
roles or users via the GRANT command.
8
Sample Session Transcript SQLcreate role A R
ole created. SQLcreate role B Role created. S
QLcreate user X identified by
User created. SQL grant select any table, execut
e any procedure, insert any table,delete any
table, update any table to A Grant succeeded.
9
Privileges granted directly to a role or a user
can be displayed by querying dba_sys_privs or
dba_tab_privs, as the case may be. But
privileges granted indirectly to a role or a user
cannot be so displayed. Sample Session Transcri
pt SQL grant create any table, insert any table,
create session to B Grant succeeded.
10
SQL create role C Role created. SQL grant B t
o C Grant succeeded. SQL grant C to X Grant
succeeded. SQL select grantee, privilege from db
a_sys_privs where grantee in ('A', 'B', 'C',
'X') order by grantee, privilege
11
GRANTEE PRIVILEGE ----------------- ---
-------------- A CREATE SESSION
A DELETE ANY TABLE
A EXECUTE ANY PROCEDURE
A SELECT ANY TABLE
A UPDATE ANY TABLE
B CREATE ANY TABLE
B CREATE SESSION
B INSERT ANY TABLE
8 rows selected.
12
(Note that C and X are not listed since they were
granted the privileges indirectly. But X does
have the privileges granted to B. For example, X
can create a table and insert rows into the
table, as shown below.) SQL connect x/ Con
nected. SQL create table P 2 (m number not n
ull, 3 n varchar2 (10)) Table created. SQL
insert into P values (17, 'sample')
1 row created.
13
REVOKE Privileges granted directly to a role or
a user can be taken away from that role or user
via the REVOKE command. But privileges granted
indirectly to a role or a user cannot be so taken
away. You must find the direct grantee from whom
you can revoke the privileges.
SQL revoke insert any table from A Revoke suc
ceeded.
14
DBA Views about Roles and Privileges
DBA_ROLES all roles that exist DBA_ROLE_PRIVS r
oles granted to users/roles
ROLE_ROLE_PRIVS roles granted to other
roles DBA_SYS_PRIVS system privileges g
ranted to users/roles DBA_TAB_PRIVS object privil
eges granted to users/roles
15
GRANT WITH GRANT or ADMIN OPTION
One can grant an object privilege to a user WITH
GRANT OPTION whereby the user can grant those
privileges to other users or roles. These are
called grantable privileges and can be assigned
only to users, but not to roles.
One can grant a system privilege to a user or a
role WITH ADMIN OPTION whereby the user can grant
those privileges to other users or roles.
16
Default Role A user can have one or more roles.
By default, when a user logs in, all of his/her
roles are activated. One can designate a subset
of these roles as default roles. In that case,
only the default roles are activated at logon
time.
17
Profile A profile is a collection of quotas and
privileges related to the following system
resource limits and password management
System Resource CPU time I/O operation
Idle time Connect time
18
System Resource (Contd.) Memory space as priv
ate SQL area (MTS) Concurrent sessions Pas
sword Management Password aging and expiration
Password history Password complexity
verification Account locking
19
A user can have only one profile at a time -
either Oracle provided DEFAULT profile or a
profile created via CREATE PROFILE command and
assigned to a user via ALTER USER command.
A CREATEd profile can be dropped, but not the DE
FAULT profile.
20
All limits of the DEFAULT profile are UNLIMITED.
But the DBA can change the values via ALTER
PROFILE command so that the modified values apply
to all users who are assigned the DEFAULT
profile. Query to find limits of any profile
select from dba_profiles order by RESOURCE_TYP
E
21
Changes made to a profile via ALTER PROFILE
command take effect for subsequent sessions only.
A profile that has users assigned to it can be
dropped only via the command DROP PROFILE
profile CASCADE. In that case, Oracle assigns
the DEFAULT profile to all users who had the
dropped profile as their default profile.
22
PUBLIC as User PUBLIC is a special user in Orac
le. Any system level or object level privilege
granted to PUBLIC can be used by all current and
future users of the database. Ideally,
privileges should not be granted to PUBLIC.
Privileges granted to PUBLIC can be determined by
running the following queries
23
select privilege from dba_sys_privs where grante
e 'PUBLIC' order by 1 select privilege from
dba_tab_privs where grantee 'PUBLIC' order by
1
24
Detection of Harmful Activities via AUDIT TRAIL

• A DBA can track the following user activities via
  the auditing capability
  
•  login attempts,
•  database actions, and
•   access (retrieval and update) to database
  objects.
  

25
Prerequisite set the initialization parameter
AUDIT_TRAIL to the value TRUE or DB (default is
FALSE or NONE). Audit records are written to the
table SYS.AUD DBA_ views containing auditing i
nformation DBA_AUDIT_EXISTS, DBA_AUDIT_OBJECT, D
BA_AUDIT_SESSION, DBA_AUDIT_STATEMENT,
DBA_AUDIT_TRAIL DBA_AUDIT_TRAIL is most comprehen
sive
26
Auditing Login Attempts Login as a
DBA privileged account Type audit
session Allow users to login as needed.
Run the following query to see the login and
logout activities, both successful and
unsuccessful 
27
select username, os_username, to_char
(timestamp, 'dd-mon-yyyy hh24miss')
"Login Time", to_char (LOGOFF_TIME,
'dd-mon-yyyy hh24miss') "Logout Time",
decode (returncode, 0, 'Connected',
01017, 'invalid username/password',
01005, 'null password given', returncode)
"Login Status" from dba_audit_session order
by 1, 3 desc
28
Auditing Database Actions DBA can audit any acti
on affecting a database object .
Oracle allows 144 actions that can be listed with
the query select action, name from audit_acti
ons
29
Possible actions such as CREATE, ALTER, and DROP
that can affect a specific object type can be
grouped together during auditing
e.g., audit table will audit all actions
affecting a table With auditing activated for an
object, all actions pertaining to that object are
recorded in the view DBA_AUDIT_OBJECT. Run the
following query to get a list of all such actions

30
select username, os_username, owner, obj_name,
action_name, to_char (timestamp, 'dd-mon-yyyy hh2
4miss') "Transaction Time from
dba_audit_object order by 1, 4, 6 desc  DBA ca
n analyze the result to identify users
responsible for harmful activities
31
Access (Retrieval and Update) to Database Objects
Access includes four DML commands, SELECT, INS
ERT, UPDATE, and DELETE For each table to be aud
ited, enter the command AUDIT ALL ON table_name B
Y ACCESS
32
BY ACCESS option causes an audit record to be
written to the table SYS.AUD once for each time
the table is accessed e.g., if a user performs
four update transactions (INSERT, UPDATE, DELETE)
on a table, then four separate records are
written to SYS.AUD. They can be viewed by
querying the view DBA_AUDIT_OBJECT.
33
Run the query select username, os_username, owne
r, obj_name, action_name, to_char (timestamp, '
dd-mon-yyyy hh24miss') "Transaction Time"
from dba_audit_object order by 1, 4, 6 desc

34
Sample Session Transcript SQL audit table Audi
t succeeded. SQL audit view Audit succeeded.
SQL audit all on dqsmith.explore by access
Audit succeeded.
35
SQL audit all on EXPLORE_COPY by access
Audit succeeded. SQL audit all on EXPLORE_ORIGIN
AL by access Audit succeeded. SQL select usern
ame, os_username, owner, obj_name, action_name,
to_char (timestamp, 'dd-mon-yyyy hh24miss')
"Transaction Time" from dba_audit_object order by
1, 4, 6 desc
36
USERNAME OS_USERNAME OWNER
OBJ_NAME ACTION_NAME
---------- ------------ ----------------Transactio
n Time -------------------- DQSMITH Mittra
DQSMITH EXPLORE
CREATE TABLE 18-jul-2003 153836
37
DQSMITH Mittra SEKHAR
EXPLORE INSERT 17-jul-2003 090726
DQSMITH Mittra SEKHAR
EXPLORE_ORIGINAL SELECT
18-jul-2003 125038 etc. etc. etc.
38

• Storage Management for AUD
• During auditing AUD grows rapidly and fragments
  SYSTEM tablespace. Archive its rows frequently to
  remedy this situation
  
• Determine archiving frequency based on growth
  rate of AUD.
  
• Copy the rows of AUD into AUD_COPY, say,
  created in a non-SYSTEM tablespace.
  
• Truncate AUD.

39

• Correction of Impact of Harmful Activities with
  LogMiner
  
• LogMiner provides two additional capabilities not
  available with AUDIT TRAIL
  
• DML commands executed and DML commands to
  reverse them
  
• Session information to identify the user

40

• LogMiner under Oracle 8i
• Allows DBA to read binary Redo Log files,
  online or archived
  
• Setup
• Include initialization parameter utl_file_dir in
  init.ora file whose value is a directory to which
  Oracle can write.
  
• Run scripts dbmslmd.sql and dbmslm.sql located in
  ORACLE_HOME/rdbms/admin.

41

• Setup (Contd.)
• Create LogMiner dictionary.
• Include Redo Log files for LogMiner analysis.
• Start a LogMiner session.
• Always end LogMiner session with the procedure
  DBMS_LOGMNR.END_LOGMNR to avoid an Ora-00600
  internal error.
  
• (Appendix 1 contains all the scripts to setup
  LogMiner under Oracle 8i.)

42

• LogMiner Output
• Four v views, vlogmnr_logs, vlogmnr_contents,
  vlogmnr_dictionary, and vlogmnr_parameters,
  contain LogMiner data.
  
• Query vlogmnr_contents on columns
• sql_redo (retrieve executed DML commands) and
  sql_undo (retrieve reverse of executed DML
  commands) to track user activities
  
• session_info and related columns to retrieve user
  identification information

43
LogMiner Session Transcript sql_redo SQLselec
t to_char (timestamp, 'dd-mon-yyyy hh24miss')
"Transaction Time", sql_redo from
vlogmnr_contents where table_space ! 'SYSTEM' a
nd timestamp '15-JUL-2003 order by 1 
44
Transaction Time SQL_REDO
---------------- ----------------
24-jul-2003 155129 insert into "SYS"."TEST_LOG_
MINER"("TEST_ID","TEST_DATE","TESTER_NAME","FINDIN
GS") values (856,TO_DATE('17-MAY-1966 000000',
'DD-M ON-YYYY HH24MISS'),'Sakuntala Datta','All
success'
45
LogMiner Session Transcript (Contd.)
sql_undo SQLselect to_char (timestamp, 'dd-mon
-yyyy hh24miss') "Transaction Time", sql_undo
from vlogmnr_contents where table_space ! 'SYST
EM' and timestamp '15-JUL-2003 order by 1
46
Transaction Time SQL_UNDO
---------------- ------------------
24-jul-2003 155129 delete from "SYS"."TEST_LOG_
MINER" where "TEST_ID" 856 and "TEST_DATE"
TO_DATE('17-MAY-1966 000000', 'DD-MON-YYYY
HH24MISS') and "TESTER_NAME" 'Sakuntala Datt
a' and "FINDINGS" 'All success' and ROWID
'AAAA7NAAGAAAACPAAE'
47
LogMiner Session Transcript (Contd.)
SESSION_INFO and Related Columns
select to_char (timestamp, 'dd-mon-yyyy
hh24miss') "Transaction Time",username,
session, serial, operation,session_info
from vlogmnr_contents where timestamp '07-AUG-
2003' and seg_name 'EXPLORE' order by 1
48
Transaction Time USERNAME
SESSION SERIAL OPERATION
-------------------- -------------------
SESSION_INFO ------------------------------------
----07-aug-2003 090005 TIMS
8 4975 INSERT
LoginUserName TIMS, ClientInfo , OsUserName
Mittra, MachineName DTS-57\CSC-TIMS33
49
LogMiner Limitations (1) LogMiner must be invoked
afresh with each new session. All LogMiner
session information resides in the PGA (Program
Global Area) which is a component of the SGA
(System Global Area). PGA is flushed when a user
session ends normally or abnormally and all the
session information is lost.
50
LogMiner Limitations (Contd.) A session ends norm
ally by executing the procedure
dbms_logmnr.end_logmnr. A session ends abnormally
generating a silent internal Oracle error
ORA-00600 when the user types exit or issues
the command connect username/password without
exiting.
51
Session Transcript SQL connect / as sysdba Co
nnected. (Session as user SYS starts)
SQL show user USER is "SYS (LogMiner is start
ed successfully.) SQL connect tims/ Co
nnected. (Session as user SYS ends abnormally
. PGA is flushed. LogMiner session is lost)
52
SQL select to_char (timestamp, 'dd-mon-yyyy
hh24miss') "Transaction Time", username,
sql_redo, sql_undo from vlogmnr_contents where
TABLE_SPACE 'TIMSDATA' order by 1 desc
sql_redo, sql_undo from vlogmnr_contents
ERROR at line 2 ORA-0
1306 dbms_logmnr.start_logmnr() must be invoked
before selecting from vlogmnr_contents
53
LogMiner Limitations (Contd.) (2) All LogMiner ta
bles are created in the SYSTEM tablespace, which
may get fragmented over time due to the growth of
these tables. So, Oracle recommends that a
separate tablespace be created to store the
tables created and used by LogMiner. The
procedure dbms_logmnr_d.set_tablespace can be
used to create such a tablespace.
54
Session Transcript SQL create tablespace logmnr_
tblspc datafile '/tims1/ssmittra/dev01/monami_ins
tance/logminer/LOGMNR_TBLSPC01.dbf'
size 200M Tablespace created. SQL EXEC DBMS_LO
GMNR_D.SET_TABLESPACE ('logmnr_tblspc')
 PL/SQL procedure successfully completed.
55
LogMiner Limitations (Contd.) (3) LogMiner dictio
nary is static under 8i, but dynamic under 9i.
This means that DML actions made after the
dictionary is created are not recorded there
under 8i. But this limitation has been removed in
9i.
56
LogMiner under Oracle 9i Setup is same as under O
racle 8i except that there is no need to run
dbmslmd.sql to create a static dictionary,
because the LogMiner dictionary is dynamically
updated continuously from the online data
dictionary. Appendix 2 contains all the scripts t
o setup LogMiner under Oracle 9i.
57

• LogMiner under Oracle 9i (Contd.)
• Columns of vlogmnr_contents (e.g., sql_redo,
  sql_undo, session_info) return the same
  information as under Oracle 8i
  
• Other three LogMiner v views are identical under
  8i and 9i

58
LogMiner dictionary is dynamic under 9i
Sample Session Transcript 092542 SQL select to
_char (timestamp, 'dd-mon-yyyy hh24miss')
"Transaction Time", username,sql_redo from
vlogmnr_contents where TABLE_SPACE 'TIMSDATA
and to_char (timestamp, 'dd-mon-yyyy hh24miss')
'04-aug-2003 112427' order by 1
59
Transaction Time USERNAME -------------------
- --------------- SQL_REDO ---------------------
-------------------- 04-aug-2003 112506 SYS in
sert into "TIMS"."EXPLORE"("COL01","COL02","COL03"
,"COL04") values ('35128','a vav',TO_DATE('02-AUG
-03', 'DD-MON-RR'),'17') (ENTER ONE INSERT AND
ONE DELETE)
60
094048 SQL INSERT into tims.explore values
(6513, 'Indumati', sysdate - 3, 16)
1 row created. 094331 SQL DELETE from tims.e
xplore_copy where col02 'cxxc'
1 row deleted.
61
094414 SQL select to_char (timestamp,
'dd-mon-yyyy hh24miss') "Transaction Time",
username,sql_redo from vlogmnr_contents
where TABLE_SPACE 'TIMSDATA and to_char
(timestamp, 'dd-mon-yyyy hh24miss')
'04-aug-2003 112427' order by 1
62
Transaction Time USERNAME -------------------
- --------------- SQL_REDO ---------------------
-------------------04-aug-2003 112506 SYS
insert into "TIMS"."EXPLORE"("COL01","COL02","COL0
3","COL04") values ('35128','a
vav',TO_DATE('02-AUG-03', 'DD-MON-RR'),'17')
63
05-aug-2003 094111 SYS INSERT into TIMS"."EXPL
ORE"("COL01","COL02","COL03","COL04") values
('6513','In dumati',TO_DATE('02-AUG-03', 'DD-MON-
RR'),'16')
64
05-aug-2003 094416 SYS DELETE from "TIMS"."EXPL
ORE_COPY" where "COL01" '2013' and "COL02"
'cxxc' an d "COL03" TO_DATE('04-AUG-03', 'DD-MO
N-RR') and "COL04" '11' and ROWID 'AA
AHxBAMgAAASOUAAB' (INSERT AND DELETE COMMANDS HA
VE BEEN RECORDED IN THE DICTIONARY DYNAMICALLY)
65

• Roadmap for Ensuring Data Security in Oracle
  Databases
  
• Follow the principle of prevention, detection,
  and correction of harmful user activities,
  intentional or unintentional.
  
• Restore database to an instant prior to the
  occurrence of a harmful event.
  
• Avoid PUBLIC as user as far as possible.
• Review and update security management plan.

66

• Bibliography
• Kevin Loney and Marlene Theriault Oracle 9i DBA
  Handbook, Chapters. 10 and 11, Oracle Press,
  2002.
  
• Sitansu S. Mittra Database Performance Tuning
  and Optimization Using Oracle, Chapter 5,
  Springer-Verlag, 2003.
  
• Oracle MetaLink papers on LogMiner e.g., Notes
  62508.1, 111886.1, etc.
  
• Marlene Theriault and Aaron Newman - Oracle
  Security Handbook, Chapters 16 and 17, Oracle
  Press, 2001.