Agenda

2. Agenda • Database Overview • Oracle Audit/Security/Control

3. Objectives • Your objectives • Magic Disk • Database Control Objectives • Frank W. Lyons • Entellusfl@aol.com • 407-774-8397

4. Terminology • Database • A set of data • Tablespaces • Logical division of a database • Files • datafile • Instances • Also known as a server • Table, columns and datatypes

5. Columns • The characteristics of a column are made up of two parts: its datatype and its length. • For columns using the NUMBER datatype, the additional characteristics of precision and scale can be specified. Precision determines the number of significant digits and Scale determines the placement of the decimal point

6. The Database Language • Structure Query Language (SQL) • Data Definition Language • DDL • Create, Drop, Alter • Data Manipulation Language • DML • Select, Insert, Update, Delete • Data Control Language • DCL • Grant, Revoke

7. Database Structure • Obtain the Data Structure Diagram Customer Table Warehouse Table Order Table The data structure diagram provides all the tables and columns

8. Tables • Tables owned by the user SYS are called the data dictionary tables • Dictionary tables provide a system catalog that the database uses to manage itself • The database maintains the relationship between table by using referential integrity

9. Database Structure • Object Dependencies Database This is the file name under the operating system Tablespace Table Index Synonym View Synonym

10. Databases • A database is a set of data. • Oracle provides the ability to store and access data in a manner consistent with a defined model known as the Relational Model.

11. Tablespaces • A tablespace is a logical division of a database • Each tablespace is constituted of one or more files, called datafiles, on a disk. A datafile can belong to one and only one tablespace

12. Tablespaces • To prevent users from creating objects in the SYSTEM tablespace, any quotas on SYSTEM, which could give a user the ability to create objects in the SYSTEM tablespace, must be revoked: • alter user Frank quota 0 on SYSTEM • When you create a new user via the create user command, you can specify a default tablespace: • create user Frank identified by excellence • default tablespace Human_Resources

13. Tablespaces • SYSTEM Data Dictionary • DATA Standard-operation tables • DATA_2 Static tables used during standard operation • INDEXES Indexes for the standard operation tables • INDEXES_2 Indexes for the static tables • RBS Standard operation rollback segment • RBS_2 Specialty rollback segments used for data loads • TEMP Standard operation temporary segments • TEMP_USER Temporary segments created by a user • TOOLS RDBMS tools tables • TOOLS_1 Indexes for RDBMS tools tables • USERS User objects, in development databases

14. Instance • In order to access the data in the database, Oracle uses a set of background processes that are shared by all users. • A database instance (also known as a server) is a set of memory structures and background processes that access a set of database files.

15. Views • Views appear to be a table containing columns and is queried in the same manner that a table is queried • Views do not use physical storage to store data • Views can not be indexed.

16. Typical Authentication Pyramid Application Logon Reporting Tool Database Accountability lessens as you move down the pyramid Data

17. Where Are the Application Controls? • Direct access usually by-passes application controls • User profiling is normally only used within the application • Views of reporting data could be incorporated • Summarized data could be used to reduce sensitivity

18. Relational Data Storage Employee Table

19. Views Based on User Profile View of the Commercial Division Security is based on data value

20. Reduced Data Sensitivity Employee Locator View Security is based on columns selected

21. Summarized Views View Summarized by Division

22. Access Type Difference • Id and Passwords • User Profiles • Accountability and audit ability

23. Data Access • Application interfaces • Reporting Tools • Direct access

24. ID and Password Controls • Where is the ID and Password Stored for verification? • How is the password stored? • Is the same user ID used for multiple applications? • Is the password stored in multiple locations?

25. User Profiles • Profiles allow access according to job responsibilities • Division • Position • Security Clearance

26. User Profiles Controls • Who creates and modifies the user profile? • What audit trails are in place for profile changes? • What approvals are required for changes to the user profile? • What is the notification process for job responsibility changes?

27. Accountability and Auditability • Is the user id translated to a high powered id during application access to the data? • Does the application record read access? • When data is inserted, modified, or deleted is the change logged? • If there is a log who is reviewing the log?

28. ID Translation • Used to prevent users from accessing data directly • Prevents authentication by the database management system • Creates tuning and monitoring challenges

29. Reporting Tools and Direct Access • Ad-hoc reporting • User flexibility • Less labor to support user reporting requirements • Checks and balance to insure information accuracy

30. Separate Reporting Database Reporting Database On-line Database Synchronization can be done real time or on intervals

31. Advantages of Separate Reporting Databases • Design for reporting efficiencies • On-line environment not impacted by the reporting workload • Data can be summarized to reduce data sensitivity • Multiple reporting databases can be defined to limit access to sensitive information

32. Disadvantages of Separate Reporting Databases • Extra storage and processor resources are required • Extra labor resources are required to support replication process • Special controls needed for direct access authority • Read activity needs to be logged for audit ability

33. Reporting Tools Reporting Tool View of Data Reporting Database Reporting tools can limit access by column, data value, or through summarization.

34. Web Access • Reporting Tools can push static or dynamic information • Design should balance performance differences with flexibility • Security at the data level needs to be well understood so that access is based on data sensitivity and job responsibilities

35. Application Recovery • Code is normally static • Code changes should be installed on well defined intervals • Recovery usually requires reboot of application server • Corrupted application files can be restored from the last copy

36. Database Recovery • Data is a constantly changing resource • Rebooting the database server causes the database system to recovery any in-flight units of work • Restoring data from backup requires the database logs to be applied to the data in order to restore data consistency

37. Database Logs Data Identification (Record Header) Before Image of Data After Image of Data

38. Log Contains All Data Modifications User Activity Database Log Database

39. Backout Due to Abort or Abend User Activity Database Log Database Aborted Unit of Work Log Records are read to backout the changes to the data

40. Protecting Database Log • Critical Recovery Resource • Contains Sensitive Information • Needed for on-site and off-site recovery • Log shipping often done for off-site recovery

41. On-site Database Log Database Log Shipping Off-site Database Log Copy

42. Criteria for Server Selection in the Database Environment • Stability • Security • Recoverability • Performance

43. Key Points • Application access controls are by-passed through most reporting interfaces • Security design MUST be centered on the data and incorporated in each interface • Data and application recovery have key differences and need special considerations

44. Key Points • Security should be designed around the DATA • All interface points must be reviewed • Data recoverability needs differ between application and data • Database logs contain sensitive information

45. Oracle

46. Identify Risks • Default Users • Operating System configuration • Database server configuration • Listener process • Privileges • Database links • Patches

47. Init.ora • Database startup file • This file is read during the instance startup and may be modified by the DBA. Any modifications to this file will not take affect until the next startup that uses this file

48. Init.ora • In the default directory configuration, the init.ora file is stored in a directory named • /orasw/app/oracle/admin/instance_name/pfile • The init.ora file does not list the names of the datafiles or online redo log files for the database as these are stored in the data dictionary. • Init.ora does list the names of the control files for the database

49. Config.ora • A second configuration file is typically used to store the settings of variables that do not change after database creation • Such as the database block size • In order for the config.ora settings to be used, the file must be listed as an include file via the IFILE parameter in the instance’s init.ora file

50. Procedures • A procedure is a block of PL/SQL statements that is stored in the data dictionary and is called by applications • Stored procedures help to enforce data security