Database Security and Auditing: Protecting Data Integrity and Accessibility

1. Database Security and Auditing: Protecting Data Integrity and Accessibility Chapter 4 Profiles, Password Policies, Privileges, and Roles

2. Objectives • Define and use a profile • Design and implement password policies • Implement password policies in Oracle and SQL Server Database Security and Auditing

3. Objectives (continued) • Grant and revoke user privileges • Create, assign, and revoke user roles • List best practices for securing a network environment Database Security and Auditing

4. Defining and Using Profiles • Profile: • Describes limitation of database resources • Defines database users behavior • Prevents users from wasting resources • Not offered by every database system: • Oracle does • Microsoft SQL Server 2000 does not Database Security and Auditing

5. Creating Profiles in Oracle • Define two elements of security: • Restriction on resources • Implementation of password policies • CREATE PROFILE statement • To view all created profiles, query the data dictionary view DBA_PROFILES • Resource Manager tool: creates different CPU usage policies Database Security and Auditing

6. Creating Profiles in Oracle (continued) Database Security and Auditing

7. Creating Profiles in Oracle (continued) • ALTER PROFILE: modifies a limit for a profile • ALTER USER: assigns a profile to a user • Oracle Enterprise Manager Security Tool: view all details about users and profiles in a GUI Database Security and Auditing

8. Creating Profiles in Oracle (continued) Database Security and Auditing

9. Creating Profiles in SQL Server 2000 • Profiles are not available in Microsoft SQL Server 2000 or 2005 • Query and connection time-outs: handled at application level within OLEDB Database Security and Auditing

10. Designing and Implementing Password Policies • Password is the key to open a user account; strong passwords are harder to break • User authentication depends on passwords • Hacker violations begin with breaking a password • Companies spend on: • Training • Education Database Security and Auditing

11. What Is a Password Policy? • Set of guidelines: • Enhances the robustness of a password • Reduces the likelihood of password breaking • Deals with: • Complexity • Change frequency • Reuse Database Security and Auditing

12. Importance of Password Policies • First line of defense • Most companies invest considerable resources to strengthen authentication by adopting technological measures that protect their assets • Forces employees to abide by the guidelines set by the company and raises employee awareness of password protection • Helps ensure that a company does not fail audits Database Security and Auditing

13. Designing Password Policies • Complexity: set of guidelines for creating passwords • Aging: how long a password can be used • Usage: how many times a password can be used • Storage: storing a password in an encrypted manner Database Security and Auditing

14. Implementing Password Policies • Oracle; using profiles: • CREATE PROFILE • Oracle Enterprise Manager • PASSWORD_VERIFY_FUNCTION Database Security and Auditing

15. Implementing Password Policies (continued) Database Security and Auditing

16. Implementing Password Policies (continued) • Microsoft SQL Server 2000: • Integrated server system • Windows authentication mode • NTLM: • Challenge/response methodology • Challenge is eight bytes of random data • Response is a 24-byte DES-encrypted hash Database Security and Auditing

17. Implementing Password Policies (continued) Database Security and Auditing

18. Implementing Password Policies (continued) • Kerberos: • A key known by client and server encrypts handshake data • Requires a Key Distribution Center (KDC) • Tickets • Time must be synchronized networkwide Database Security and Auditing

19. Implementing Password Policies (continued) Database Security and Auditing

20. Implementing Password Policies (continued) Database Security and Auditing

21. Granting and Revoking User Privileges • Permit or deny access to data or to perform database operations • In Oracle: • System privileges: • Granted only by a database administrator • Granted by a user with administration privileges • Object privileges: • Granted to a user by the schema owner • Granted by a user with GRANT privileges Database Security and Auditing

22. Granting and Revoking User Privileges (continued) • In Oracle (continued): • Grant a privilege using the DCL GRANT statement • Revoke a privilege using the DCL REVOKE statement: • ADMIN option • GRANT option • Oracle Enterprise Manager Security Database Security and Auditing

23. Granting and Revoking User Privileges (continued) Database Security and Auditing

24. Granting and Revoking User Privileges (continued) Database Security and Auditing

25. Granting and Revoking User Privileges (continued) Database Security and Auditing

26. Granting and Revoking User Privileges (continued) • In SQL Server (4 levels); system/server privileges: • Sysadmin • Serveradmin • Setupadmin • Securityadmin • Processadmin • Dbcreator • Diskadmin • Bulkadmin Database Security and Auditing

27. Granting and Revoking User Privileges (continued) • In SQL Server (continued): • Database privileges: • Fixed database roles • Statement permissions • Grant permission using the GRANT statement • Revoke permission using the REVOKE statement • Enterprise Manager • Deny permission using the DENY statement Database Security and Auditing

28. Granting and Revoking User Privileges (continued) Database Security and Auditing

29. Granting and Revoking User Privileges (continued) Database Security and Auditing

30. Granting and Revoking User Privileges (continued) Database Security and Auditing

31. Granting and Revoking User Privileges (continued) • In SQL Server: • Table and database objects privileges: • GRANT, REVOKE, and DENY • EXECUTE permission • Enterprise Manager (3 methods) • Column privileges: • GRANT, REVOKE, and DENY • Enterprise Manager (2 methods) Database Security and Auditing

32. Creating, Assigning, and Revoking User Roles • Role: • Used to organize and administer privileges • It is like a user, except it cannot own object • Can be assigned privileges • Can be assigned to users Database Security and Auditing

33. Creating, Assigning, and Revoking User Roles (continued) • In Oracle: • Create a role using CREATE ROLE statement • Assign a role using GRANT statement • Oracle Enterprise Manager Roles tool • Revoke a role using REVOKE statement • Drop a role using DROP statement Database Security and Auditing

34. Creating, Assigning, and Revoking User Roles (continued) • In SQL Server; user-defined roles: • Standard and application • Create roles using SP_ADDROLE system-stored procedure • Add members to a role using SP_ADDROLEMEMBER stored procedure • Drop members from a role using SP_DROPROLEMEMBER stored procedure Database Security and Auditing

35. Creating, Assigning, and Revoking User Roles (continued) • In SQL Server (continued): • User-defined roles (continued): • Drop roles using SP_DROPROLE stored procedure • Use Enterprise Manager • Fixed server roles: • Cannot be modified or created • Add member to a role using SP_ADDSRVROLEMEMBER stored procedure Database Security and Auditing

36. Creating, Assigning, and Revoking User Roles (continued) Database Security and Auditing

37. Creating, Assigning, and Revoking User Roles (continued) • In SQL Server (continued): • Fixed server roles (continued): • Drop members from a role using SP_DROPSRVROLEMEMBER stored procedure • Use Enterprise Manager • Fixed database roles: • Cannot be modified • Give access to database administrative tasks • Add members to a role using SP_ADDROLEMEMBER stored procedure Database Security and Auditing

38. Creating, Assigning, and Revoking User Roles (continued) Database Security and Auditing

39. Creating, Assigning, and Revoking User Roles (continued) • In SQL Server (continued): • Fixed database roles (continued): • Drop members from a role using SP_DROPROLEMEMBER stored procedure • Use Enterprise Manager • Public database role: • Cannot be dropped • Users automatically belong to this role • Users cannot be dropped Database Security and Auditing

40. Best Practices • Develop a secure environment: • Never store passwords for an application in plaintext • Change passwords frequently • Use passwords at least eight characters long • Pick a password that you can remember • Use roles to control and administer privileges • Report compromise or loss of a password • Report any violation of company guidelines Database Security and Auditing

41. Best Practices (continued) • Develop a secure environment (continued): • Never give your password to anyone • Never share your password with anyone • Never give your password over the phone. • Never type your password in an e-mail • Make sure your password is complex enough • Use Windows integrated security mode • In Windows 2000/3 domain use domain users and take advantage of Kerberos Database Security and Auditing

42. Best Practices (continued) • When configuring policies: • Require complex passwords with special characters in the first seven bytes • Require a password length of at least eight • Set an account lockout threshold • Do not allow passwords to automatically reset • Expire end-user passwords • Do not expire application-user passwords • Enforce a password history Database Security and Auditing

43. Summary • Profiles define database users behavior • In Oracle: • DBA_PROFILE view • ALTER USER • SQL Server does not support profiles • Password policy: • Enhances password robustness • Reduces likelihood of password breaking Database Security and Auditing

44. Summary (continued) • In SQL Server: • NTLM • Kerberos • In Oracle: • System privileges • Object privileges • In SQL Server: • System or server, database, table and column privileges Database Security and Auditing

45. Summary (continued) • GRANT and REVOKE • Role is used to: • Organize and administer privileges in an easy manner • Role is like a user but cannot own objects • Role can be assigned privileges • GRANT and REVOKE • Best practices for developing a secure environment Database Security and Auditing