1 / 66

Oracle Sleuth: Who Did It?

Session id: 36652. Oracle Sleuth: Who Did It?. Sitansu S. Mittra Senior Principal Engineer & Database Management Specialist Computer Sciences Corporation. Topic Outline. Prevention of unauthorized user access – roles, privileges, profiles

ron
Download Presentation

Oracle Sleuth: Who Did It?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session id: 36652 Oracle Sleuth: Who Did It? Sitansu S. MittraSenior Principal Engineer & Database Management Specialist Computer Sciences Corporation

  2. 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

  3. 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.

  4. Relationship among users, roles, privileges, and profiles: M:N USER <<---------------------->> ROLE M:N ROLE <<---------------->> PRIVILEGE M:1 USER <<--------------------> PROFILE Role = named collection of system or object level privileges that are granted to the role

  5. A role can be granted to a user or to another role. Privilege = capability to perform authorized 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;

  6. 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.

  7. Sample Session Transcript SQL>create role A; Role created. SQL>create role B; Role created. SQL>create user X identified by *****; User created. SQL> grant select any table, execute any procedure, insert any table,delete any table, update any table to A; Grant succeeded.

  8. 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 Transcript SQL> grant create any table, insert any table, create session to B; Grant succeeded.

  9. SQL> create role C; Role created. SQL> grant B to C; Grant succeeded. SQL> grant C to X; Grant succeeded. SQL> select grantee, privilege from dba_sys_privs where grantee in ('A', 'B', 'C', 'X') order by grantee, privilege;

  10. 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.

  11. (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/***** Connected. SQL> create table P 2 (m number not null, 3 n varchar2 (10)); Table created. SQL> insert into P values (17, 'sample'); 1 row created.

  12. 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 succeeded.

  13. DBA Views about Roles and Privileges DBA_ROLES all roles that exist DBA_ROLE_PRIVS roles granted to users/roles ROLE_ROLE_PRIVS roles granted to other roles DBA_SYS_PRIVS system privileges granted to users/roles DBA_TAB_PRIVS object privileges granted to users/roles

  14. 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.

  15. 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.

  16. 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

  17. System Resource (Contd.) Memory space as private SQL area (MTS) Concurrent sessions Password Management Password aging and expiration Password history Password complexity verification Account locking

  18. 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 DEFAULT profile.

  19. 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_TYPE;

  20. 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.

  21. PUBLIC as User PUBLIC is a special user in Oracle. 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:

  22. select privilege from dba_sys_privs where grantee = 'PUBLIC' order by 1; select privilege from dba_tab_privs where grantee = 'PUBLIC' order by 1;

  23. 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.

  24. 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 information: DBA_AUDIT_EXISTS, DBA_AUDIT_OBJECT, DBA_AUDIT_SESSION, DBA_AUDIT_STATEMENT, DBA_AUDIT_TRAIL DBA_AUDIT_TRAIL is most comprehensive

  25. Auditing Login AttemptsLogin 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:

  26. select username, os_username, to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Login Time", to_char (LOGOFF_TIME, 'dd-mon-yyyy hh24:mi:ss') "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;

  27. Auditing Database Actions DBA can audit any action affecting a database object . Oracle allows 144 actions that can be listed with the query select action, name from audit_actions;

  28. 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 :

  29. select username, os_username, owner, obj_name, action_name, to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time” from dba_audit_object order by 1, 4, 6 desc; DBA can analyze the result to identify users responsible for harmful activities

  30. Access (Retrieval and Update) to Database Objects Access includes four DML commands, SELECT, INSERT, UPDATE, and DELETE For each table to be audited, enter the command AUDIT ALL ON table_name BY ACCESS;

  31. “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.

  32. Run the query: select username, os_username, owner, obj_name, action_name, to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time" from dba_audit_object order by 1, 4, 6 desc;

  33. Sample Session Transcript SQL> audit table; Audit succeeded. SQL> audit view; Audit succeeded. SQL> audit all on dqsmith.explore by access; Audit succeeded.

  34. SQL> audit all on EXPLORE_COPY by access; Audit succeeded. SQL> audit all on EXPLORE_ORIGINAL by access; Audit succeeded. SQL> select username, os_username, owner, obj_name, action_name, to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time" from dba_audit_object order by 1, 4, 6 desc;

  35. USERNAME OS_USERNAME OWNER OBJ_NAME ACTION_NAME ---------- ------------ ----------------Transaction Time -------------------- DQSMITH Mittra DQSMITH EXPLORE CREATE TABLE 18-jul-2003 15:38:36

  36. DQSMITH Mittra SEKHAR EXPLORE INSERT 17-jul-2003 09:07:26 DQSMITH Mittra SEKHAR EXPLORE_ORIGINAL SELECT 18-jul-2003 12:50:38 etc. etc. etc. ……

  37. 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$.

  38. 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

  39. LogMiner under Oracle 8i • Allows DBA to read binary Redo Log files, online or archived • Setup • Include initialization parameterutl_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.

  40. 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.)

  41. LogMiner Output • Four v$ views, v$logmnr_logs, v$logmnr_contents, v$logmnr_dictionary, and v$logmnr_parameters, contain LogMiner data. • Query v$logmnr_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

  42. LogMiner Session Transcript sql_redo SQL>select to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time", sql_redo from v$logmnr_contents where table_space != 'SYSTEM' and timestamp > '15-JUL-2003‘ order by 1;

  43. Transaction Time SQL_REDO ---------------- ---------------- 24-jul-2003 15:51:29 insert into "SYS"."TEST_LOG_MINER"("TEST_ID","TEST_DATE","TESTER_NAME","FINDINGS") values (856,TO_DATE('17-MAY-1966 00:00:00', 'DD-M ON-YYYY HH24:MI:SS'),'Sakuntala Datta','All success'

  44. LogMiner Session Transcript (Contd.) sql_undo SQL>select to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time", sql_undo from v$logmnr_contents where table_space != 'SYSTEM' and timestamp > '15-JUL-2003‘ order by 1;

  45. Transaction TimeSQL_UNDO ---------------- ------------------ 24-jul-2003 15:51:29 delete from "SYS"."TEST_LOG_MINER" where "TEST_ID" = 856 and "TEST_DATE" = TO_DATE('17-MAY-1966 00:00:00', 'DD-MON-YYYY HH24:MI:SS') and "TESTER_NAME" = 'Sakuntala Datta' and "FINDINGS" = 'All success' and ROWID = 'AAAA7NAAGAAAACPAAE';

  46. LogMiner Session Transcript (Contd.) SESSION_INFO and Related Columns select to_char (timestamp, 'dd-mon-yyyy hh24:mi:ss') "Transaction Time",username, session#, serial#, operation,session_info from v$logmnr_contents where timestamp > '07-AUG-2003' and seg_name = 'EXPLORE' order by 1;

  47. Transaction Time USERNAME SESSION# SERIAL# OPERATION -------------------- ------------------- SESSION_INFO ----------------------------------------07-aug-2003 09:00:05 TIMS 8 4975 INSERT LoginUserName = TIMS, ClientInfo = , OsUserName = Mittra, MachineName = DTS-57\CSC-TIMS33

  48. 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.

  49. LogMiner Limitations (Contd.) A session ends normally 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.

More Related