SOX Compliance – Security, Auditing and the New Segregation of Duties

1. SOX Compliance – Security, Auditing and the New Segregation of Duties MMUG September 2007 Maria Balluch, QAD

2. Agenda • What is SOX • General Rules of Compliance • QAD 2007 and QAD 2008 • Security • Enhanced Controls • Segregation of Duties • Implementation Suggestions • Questions

3. SOX • Three letters that strike fear and loosely translated mean “extra work” • Sarbanes-Oxley mandates Control • Financials • Processes • Security • Transactions

4. What this really means to you • Ability to provide accurate Financial data with history to back up each transaction • Paper-trail to identify each and every transaction with financial impact • Ability to maintain control over activity by separating who can do what • Ability to prove system security and minimal access risk • Ability to restrict or grant access to activity by security level and role

5. Compliance • Unique user identification • Role-based security • Audit Trail to track changes and deletions of records by user • Segregation of Duties • User 1 cannot perform Duty A and Duty B • User 2 cannot perform Duty B and Duty C, but could potentially perform Duty A and Duty C • Transactional Blocking – some functions cannot be performed if certain attributes are included • Cannot receive goods without a PO if the account is not Cost of Production, for example

6. Security Enhancements • QAD 2007 • Enforce OS User Id • Security by domain • Password complexity rules • Logon history captured • User deactivated after failed logon • QAD 2008 • Role-based security • Builds onto previous security models • Assign functions to roles and then roles to user

7. Role - Permissions

8. Role – Membership Maintenance Assigning Users to Roles

9. Restrict Transaction Type By Role

10. Many other restriction types available • PO Maintenance • PO Receipts • Unplanned Issues/Receipts • Sales • DRP • SSM

11. Enhanced Controls • Audit Trails • Ability to audit every table • Select what your business needs • Controlled at the database level (trigger) • Written constantly through background process • Separate audit database • Reporting is customized “on the fly”

13. atexrp.p b+ 36.12.1 Audit Trail Report – Existing E-Records Date: 02/25/03 Page: 1 <company name> Time: 15:06:01 Table Name: pt_mstr Item Master ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, EXISTING ELECTRONIC RECORD P Field Label- Name Field Value , ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, p Item Number- pt_part 10-10000 i BOM/Formula- pt_bom_code i Description- pt_desc1 OASIS(TM) COOLING SYSTEM i Group- pt_group DISCRETE i Item Type- pt_part_type CONFIG i Model- pt_model i MRP Required- pt_mrp yes i Need PM MRP- pt_pm_mrp no i oid_pt_mstr- oid_pt_mstr 200302250000000012.1234 i Prod Line- pt_prod_line 1000 n Status- pt_status AC ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Event ID: 200302240000000005.1234567890 Date: 02/24/03 Time: 23:02:40 PST/PDT Event Type: MODIFY User Name: Sally Smith User ID: ssm P Field Label- Name Before Value After Value , ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, i Description- pt_desc1 OASIS(TM) COOLING SYSTEM - INACTIVE OASIS(TM) COOLING SYSTEM n Status- pt_status IC AC ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Event ID: 200301240000000001.1234567890 Date: 01/24/03 Time: 13:02:59 PST/PDT Event Type: CREATE User Name: John Doe User ID: jdd P Field Label- Name Before Value After Value , ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, p Item Number- pt_part 10-10001 i BOM/Formula- pt_bom_code i Description- pt_desc1 OASIS(TM) COOLING SYSTEM - INACTIVE i Group- pt_group DISCRETE i Item Type- pt_part_type CONFIG i Model- pt_model i MRP Required- pt_mrp yes i Need PM MRP- pt_pm_mrp no i oid_pt_mstr- oid_pt_mstr 200302250000000012.1234 i Prod Line- pt_prod_line 1000 n Status- pt_status IC ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Current Values Modify Event Create Event

14. Making Audit Trails Work for You • Output shows user access to functions • Can be written to display access violation • Captures all changes to records • Optionally choose to display on reports • Updates through Progress Editor are captured • Audit what you need • Take audit databases off line as they fill up and are signed off • Shows strict control over data access and monitors all changes or deletes • Used along with other Compliance tools, this is a powerful reporting application

15. E-Signatures • Primarily to meet mandate of CFR Part 11 of the FDA Regulations, but other industries benefit also • Certain functions that lead to uncontrolled movement of inventory or records must be electronically signed • Example – 3.1.1 Inventory Detail Maintenance • Can make unusable inventory usable via this function • Needs to have electronic signature authorizing it in some businesses • E-Signature is User ID and Password stamped on the transaction • Eliminates the use of some one else’s machine to perform an action (entry of password required)

16. Segregation of Duties • Prevents one user from performing two duties that are in conflict with each other • Receiving from Purchasing • Requisitions from Purchasing • Purchasing from AP • Supplier Master from AP and Receiving • Item Master from Sales or Purchasing • Inventory Control from AP • These are the common examples, there are others

17. Type of Segregation • Forbid transaction always • Forbid transaction except with highest authority • Allow transaction based on rules • Allow transaction based on reason code

18. Categories

20. Assigning Resources

21. SOD… • Add resource (menu or activity) to category • Category restrictions will allow or disallow transactions from the resources depending on compatibility • Add users to Roles • Users are now allowed or restricted based on their role and the category restrictions

22. Implementation Suggestions - SOD • A detailed list of roles • A detailed list of SOD category requirements • A detail of when exceptions should be designed • Map it out • Review and audit yourself with SOD reporting • As simple as possible!

23. Suggestions – Audit Trails • Determine level of requirement • Mandatory • Good to know • Turn on all tables that may be candidate • Evaluate reports • Turn off what is not necessary • Cycle Audit Database to keep information on period basis

24. QUESTIONS? mzb@qad.com