database security and auditing: protecting data integrity and accessibility
DESCRIPTION
Database Security and Auditing: Protecting Data Integrity and Accessibility. Chapter 5 Database Application Security Models. Objectives. Describe the different types of users in a database environment and the distinct purpose of each Identify and explain the concepts of five security models - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/1.jpg)
Database Security and Database Security and Auditing: Protecting Data Auditing: Protecting Data Integrity and AccessibilityIntegrity and Accessibility
Chapter 5 Database Application Security
Models
![Page 2: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/2.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 2
ObjectivesObjectives
• Describe the different types of users in a database environment and the distinct purpose of each
• Identify and explain the concepts of five security models
• List the most commonly used application types
![Page 3: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/3.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 3
Objectives (continued) Objectives (continued)
• Implement the most common application security models
• Understand the use of data encryption within database applications
![Page 4: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/4.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 4
Types of UsersTypes of Users
• Application:– Solves a problem
– Performs a specific business function
• Database: collection of related data files used by an application
• Application user: user within the application schema
![Page 5: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/5.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 5
Types of Users (continued)Types of Users (continued)
• Types:– Application administrator
– Application owner
– Application user
– Database administrator
– Database user
– Proxy user
– Schema owner
– Virtual user
![Page 6: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/6.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 6
Security ModelsSecurity Models
• Access Matrix Model:– Represents two main entities: objects and
subjects:• Columns represent objects• Rows represent subjects
– Objects: tables, views, procedures, database objects
– Subjects: users, roles, privileges, modules
– Authorization cell
![Page 7: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/7.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 7
Security Models (continued)Security Models (continued)
![Page 8: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/8.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 8
Security Models (continued)Security Models (continued)
• Access Modes Model:– Based on the Take-Grant model
– Uses objects and subjects
– Specifies access modes: static and dynamic modes
– Access levels: a subject has access to objects at its level and all levels below it
![Page 9: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/9.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 9
Security Models (continued)Security Models (continued)
![Page 10: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/10.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 10
Security Models (continued)Security Models (continued)
![Page 11: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/11.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 11
Application TypesApplication Types
• Client/Server applications:– Management Information System (MIS)
department:• Thirty year ago centralized information• Developed mainframe projects• Was a bottleneck
– Personal computer was introduced: developing need for client/server applications
– Based on the business model
![Page 12: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/12.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 12
Client/Server ApplicationsClient/Server Applications
![Page 13: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/13.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 13
Client/Server Applications (continued)Client/Server Applications (continued)
• Provides a flexible and scalable structure• Components:
– User interface
– Business logic
– Data access
• Components usually spread out over several tiers:– Minimum two
– Normally, four to five
![Page 14: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/14.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 14
Client/Server Applications (continued)Client/Server Applications (continued)
![Page 15: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/15.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 15
Client/Server Applications (continued)Client/Server Applications (continued)
![Page 16: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/16.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 16
Web ApplicationsWeb Applications
• Evolved with the rise of dot-com and Web-based companies
• Uses the Web to connect and communicate to the server
• A Web application uses HTML pages created using:– ActiveX
– Java applets or beans
– ASP (Active Server Pages)
![Page 17: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/17.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 17
Web Applications (continued)Web Applications (continued)
![Page 18: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/18.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 18
Web Applications (continued)Web Applications (continued)
• Components:– Web browser layer
– Web server layer
– Application server layer
– Business logic layer
– Database server layer
![Page 19: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/19.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 19
Web Applications (continued)Web Applications (continued)
![Page 20: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/20.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 20
Data Warehouse ApplicationsData Warehouse Applications
• Used in decision-support applications• Collection of many types of data taken from a
number of different databases• Typically composed of a database server• Accessed by software applications or reporting
applications: online analytical processing (OLAP)
![Page 21: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/21.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 21
Data Warehouse Applications Data Warehouse Applications (continued)(continued)
![Page 22: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/22.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 22
Application Security ModelsApplication Security Models
• Models:– Database role based
– Application role based
– Application function based
– Application role and function based
– Application table based
![Page 23: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/23.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 23
Security Model Based on Database Security Model Based on Database RolesRoles
• Application authenticates application users: maintain all users in a table
• Each user is assigned a role; roles have privileges assigned to them
• A proxy user is needed to activate assigned roles; all roles are assigned to the proxy user
• Model and privileges are database dependent
![Page 24: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/24.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 24
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
![Page 25: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/25.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 25
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
• Implementation in Oracle:– Create users
– Add content to your tables
– Add a row for an application user
– Look for application user’s role
– Activate the role for this specific session
![Page 26: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/26.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 26
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
• Implementation in SQL Server:– Use application roles:
• Special roles you that are activated at the time of authorization
• Require a password and cannot contain members
– Connect a user to the application role: overrules user’s privileges
![Page 27: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/27.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 27
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
• Implementation in SQL Server (continued):– Create and drop application roles using the
command line and the Enterprise Manager:• SP_ADDAPPROLE• SP_DROPAPPROLE
– You can activate application roles using SP_SETAPPROLE
![Page 28: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/28.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 28
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
• Implementation in SQL Server (continued):– Connect to database as the proxy user
– Validate the user name and password
– Retrieve the application role name
– Activate the application role
![Page 29: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/29.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 29
Security Model Based on Database Security Model Based on Database Roles (continued)Roles (continued)
![Page 30: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/30.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 30
Security Model Based on Application Security Model Based on Application RolesRoles
• Application roles are mapped to real business roles
• Application authenticates users• Each user is assigned to an application role;
application roles are provided with application privileges (read and write)
![Page 31: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/31.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 31
Security Model Based on Application Security Model Based on Application Roles (continued)Roles (continued)
![Page 32: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/32.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 32
Security Model Based on Application Security Model Based on Application Roles (continued)Roles (continued)
• Implementation in SQL Server– Create a database user
– Connect the application to the database using this user
– Create stored procedures to perform all database operations
![Page 33: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/33.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 33
Security Model Based on Application Security Model Based on Application FunctionsFunctions
• Application authenticates users• Application is divided into functions• Considerations:
– Isolates application security from database
– Passwords must be securely encrypted
– Must use a real database user
– Granular privileges require more effort during implementation
![Page 34: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/34.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 34
Security Model Based on Application Security Model Based on Application Functions (continued)Functions (continued)
![Page 35: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/35.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 35
Security Model Based on Application Security Model Based on Application Roles and FunctionsRoles and Functions
• Combination of models• Application authenticates users• Application is divided into functions:
– Roles are assigned to functions
– Functions are assigned to users
• Highly flexible model
![Page 36: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/36.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 36
Security Model Based on Application Security Model Based on Application Roles and Functions (continued)Roles and Functions (continued)
![Page 37: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/37.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 37
Security Model Based on Application Security Model Based on Application TablesTables
• Depends on the application to authenticate users
• Application provides privileges to the user based on tables; not on a role or a function
• User is assigned access privilege to each table owned by the application owner
![Page 38: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/38.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 38
Security Model Based on Application Security Model Based on Application Tables (continued)Tables (continued)
![Page 39: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/39.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 39
Security Model Based on Application Security Model Based on Application Tables (continued)Tables (continued)
• Implementation in SQL Server:– Grant authorization on application functions to
the end user
– Alter authorization table from the security model based on database roles; incorporate the table and access columns required to support model
![Page 40: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/40.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 40
Application Security ModelsApplication Security Models
![Page 41: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/41.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 41
Application Security Models Application Security Models (continued)(continued)
![Page 42: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/42.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 42
Data EncryptionData Encryption
• Passwords should be kept confidential and preferably encrypted
• Passwords should be compared encrypted:– Never decrypt the data
– Hash the passwords and compare the hashes
![Page 43: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/43.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 43
Data Encryption (continued)Data Encryption (continued)
![Page 44: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/44.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 44
SummarySummary
• An application user is simply a record created for a user within the application schema; usually does not have database privileges or roles assigned
• Access matrix:– Columns represent objects
– Rows represent subjects
– Authorization cell
• Access mode
![Page 45: Database Security and Auditing: Protecting Data Integrity and Accessibility](https://reader035.vdocuments.us/reader035/viewer/2022062517/56812dd0550346895d9313e3/html5/thumbnails/45.jpg)
Database Security & Auditing: Protecting Data Integrity & Accessibility 45
Summary (continued)Summary (continued)
• Application types: client/server, Web, and Data Warehouse
• Application security models– Database roles
– Application roles
– Application functions
– Roles and functions in the application
– Application tables