IS 221 Database Management Lecture 8 - Database Security PDF

1 IS 221 Database Management Lecture 8 Database Security Book:Fundamentals of Database Systems – Ramez Elmasri and Shamkant B.Navathe – 7th Edition...

1 IS 221 Database Management Lecture 8 Database Security Book:Fundamentals of Database Systems – Ramez Elmasri and Shamkant B.Navathe – 7th Edition 1  Outline Database Security – Legal and ethical concerns regarding access rights. Common Threats to Databases – Loss of integrity, availability, and confidentiality. Strategies to protect databases: – Access control, inference control, flow control, and data encryption. Types of Database Security Mechanisms – Discretionary and mandatory security mechanisms. Database Security and the Database Administrator (DBA) – Responsibilities of the DBA, including granting privileges and ensuring overall system security. Access Control, User Accounts, and Database Audits – Processes for user account creation, login verification, and audit trail creation. Types of Access Controls – Discretionary Access Control (DAC) and Mandatory Access Control (MAC) 2  Introduction to Database Security Database Security Systems: A part of the DBMS dedicated to prevent unauthorized database access. Objective: ❑ Understand how to protect sensitive data in databases from unauthorized access. Why is Database Security Important? Ensuring database security involves legal, ethical, policy, and technical dimensions : Legal and Ethical : Database Security ensure that only authorized users should access private and sensitive information. Policy Management: Database security setting rules to decide which data should be kept confidential. Technical Aspects: Database Security setting up technical security at different system levels including (hardware, operating system, DBMS) to protect data from unauthorized access and breaches. 3  Common Threats Threats can decrease the integrity, availability, and confidentiality of database information: 1. Loss of Integrity: This means someone did unauthorized or improper changes to data when they shouldn’t do. 2. Loss of Availability: This is when legall users cannot access data when needed because something is blocking them. 3. Loss of Confidentiality: This happens when private or secret information gets out to users who are not supposed to see it. ❖ In simple terms, threats to database security can mess up your data, block your access to it, or let the wrong people see it. 4  Strategies to Protect Databases ◼ To protect databases against these types of threats, four kinds of Strategies can be implemented: 1- Access Control: Make sure only allowed users can access the data 2- Inference Control: prevent users from figuring out secret info from available data. 3- Flow Control: Manage data paths to ensure information does not reach unauthorized users. 4- Data Encryption: Encode data to protect it during storage and transmission, so only allowed users can read data. ◼ A combination of these strategies helps secure databases effectively. 5  1. Access Control Objective: ◼ Ensure only authorized users can access the database. Deciding who access data and who doesn’t. How Does It Work in Databases? ◼ In databases, it's about setting up rules and permissions to ensure that only authorized users can access or modify the data. This prevents unauthorized access and helps protect sensitive information from falling into the wrong hands. Methods: ◼ Use of user IDs and passwords to manage permissions. Example: ◼ A university database may allow professors to access and modify grades and course materials, while students can only view this information. 6  2. Inference Control ◼ What is Inference Control? Definition: it is used to prevent someone from extraction (inference) private information from data that is publicly available. ◼ Why is it Important? Prevents unauthorized discovery of private data even when some data is public. ◼ Example: Situation: Salary data is published by departments without naming specific individuals. Risk: Someone could guess individual salaries if only one professor works in a small department. Inference Control: Mask or aggregate data to ensure individual salaries cannot be guessed from the published information. 7  3. Flow Control ◼ What is Flow Control? Definition: Measures that prevent data from being accessed or leaked in ways that could reach unauthorized users. ◼ Covert Channels: unexpected ways that information can sneak through without anyone noticing.. ◼ Why is it Important? Ensures sensitive data doesn't accidentally leak outside of authorized pathways. ◼ Example: Scenario: Imagine an office where certain documents should only be seen by the management team. However, an employee figures out that they can see these documents by using a shared printer's memory. Flow Control Measures: Setting rules that only allow the printer to store documents temporarily or encrypting documents to prevent unauthorized viewing even if accessed. ◼ Conclusion : Flow control is about setting up strict pathways for data transmission and storage to prevent unauthorized access and ensure that data only flows where it is supposed to. 8  4. Data encryption ◼ What is Data Encryption? Definition: A security method used to protect sensitive data, like credit card numbers, by transforming it into a secure format that can only be read with a special key. ◼ How Does it Work? Encoding Data: Data is converted using an encryption algorithm, making it unreadable to anyone who doesn't have the decryption key. Decryption: Authorized users with the correct key can convert the encoded data back to its original form. ◼ Why is it Important? Protects sensitive information during transmission across networks, preventing unauthorized access and theft. ◼ Example: Scenario: When you make an online purchase, your credit card information is encrypted before it's sent over the internet. This way, data cannot be read without the decryption key. 9  Types of Database security mechanisms Two Main Types of Security Mechanisms: 1. Discretionary Security Mechanisms (DSM) : It is flexible and controlled by administrators. Purpose: Allows the assignment of specific access rights (like reading, inserting, deleting, or updating) to users Example: A professor can modify course grades, but a student can only view them. Control: Administered by database administrators who grant and revoke these privileges. 2. Mandatory Security Mechanisms (MSM) : It is strict and controlled by predefined rules. Purpose: Enforces rules based on data sensitivity and user clearance levels Example: Classified information in a government database can only be accessed by users with a matching or higher security clearance. Implementation: Data and users are categorized into classes such as top secret, secret, confidential, etc. 10  Slide 8- 11 Database Security and the DBA Who is a DBA? Definition: The Database Administrator (DBA) is the central authority for managing a database system. Key Responsibilities: 1. Granting Privileges: _ Assigns specific permissions to users depending on their roles. 2. Classifying Data and Users: _ Organizes data and users into categories based on sensitivity and clearance—similar to assigning employees to different clearance levels in a secure facility. ❖ The DBA is responsible for the overall security of the database system. 11  Slide 8- 12 Database Security and the DBA What is a DBA Account? It is called a system or superuser account, The DBA account has special capabilities beyond those of regular database users. Capabilities of a DBA Account : 1. Account Creation: used to controls access to the DBMS system. 2. Privilege Granting: granting specific roles and what resources user can access. 3. Privilege Revocation: revoking access when user no longer needs access 4. Security Level Assignment: determining who can access confidential or top- secret level data. ❖ Capability 1 in the preceding list is used to control access to the DBMS as a whole, whereas actions 2 and 3 are used to control discretionary database authorization, and action 4 is used to control mandatory authorization. 12 Access Control, User Accounts, and Database Audits ◼ DBA manages database security by Access Control, User Accounts, and Database Audits ◼ Accessing the Database: User Account Creation: To access the database, users must apply for an account. The DBA will create a unique account ID and password for each approved user. ◼ Login Process: Users must log in using their assigned account ID and password. This ensures that only authorized personnel can access the database. ◼ How It Works: The DBMS verifies the account ID and password. If they are valid, the user gains access to the database. ❖ User accounts and login procedures are fundamental for controlling who can access the database, ensuring that only those with authorized access can enter. 13  Access Control , User Accounts, and Database Audits Now, let’s keep our database safe not just from unauthorised users, but making sure those legal users follow the rules. This is where Database Audits come into play : Purpose of System Logs: ❑ The system log records every action performed by users during their login sessions. And keep detailed record of all operations, such as updates, deletions, and queries made by each user. Why It's Important? ❑ To ensure all database operations are tracked for security and for diagnosing issues and recovering from errors like transaction failures or system crashes. 14  Slide Access Control, User Accounts, and Database 8- 15 Audits If any tampering with the database is suspected, a database audit is performed What is a Database Audit? Definition: reviewing of the database logs to verify all activities performed within the system over a specific period. A database log that is used mainly for security purposes is sometimes called an audit trail.. 15 Discretionary Access Control ◼ Discretionary access control in a database system is based on the granting and revoking privileges. ◼ Grant Command: ❑ GRANT privileges ON object TO users [WITH GRANT OPTIONS] ◼ Revoke Command: ❑ REVOKE [GRANT OPTION FOR] privileges ON object FROM users 16  Slide 8- 17 Discretionary Access Control 1. GRANT Command The GRANT command is used to give users specific privileges or permissions on an object within a database. GRANT privileges ON object TO users [WITH GRANT OPTION] privileges: These are the rights or actions that you are allowing the user to perform, such as SELECT, INSERT, UPDATE, DELETE etc object: This is the database object (like a table or ,column ) on which the privileges are being granted. users: These are the database accounts or roles to which the privileges are being granted. WITH GRANT OPTION: This optional clause allows the user receiving the privileges to further pass and grant these privileges to other users. Example Suppose you want to allow user 'John' to read and write data in the 'Employee' table. The command would look like this: GRANT SELECT, INSERT, UPDATE ON Employee TO John; If you want John to be able to pass these privileges on to another user, you would use: GRANT SELECT, INSERT, UPDATE ON Employee TO John WITH GRANT OPTION; 17  Slide 8- 18 Discretionary Access Control 2. REVOKE Command The REVOKE command is used to remove previously granted privileges from users. REVOKE [GRANT OPTION FOR] privileges ON object FROM users GRANT OPTION FOR: This optional clause is used if you want to revoke the capability of the user to grant the specified privileges to others, without revoking the base privilege itself. privileges: These are the privileges you are revoking. object: This is the database object from which the privileges are being revoked. users: These are the users or roles from whom the privileges are being revoked. Example If you decide that John should no longer be able to update the 'Employee' table, you would use: REVOKE UPDATE ON Employee FROM John; If John had the ability to grant access to others and you want to remove that ability, you would use: REVOKE GRANT OPTION FOR UPDATE ON Employee FROM John; 18  Mandatory Access Control What is it? This is a more stringent security measure where data and users are classified based on their security sensitivity. How does it work? Data and users are labeled with security levels (like confidential, secret, top-secret). Users can only access the data if their security level is appropriate. For example, a user with a "confidential" clearance cannot access "secret" data. ◼ Typical security classes are top secret (TS), secret (S), confidential (C), and unclassified (U), where TS is the highest level and U the lowest: TS ≥ S ≥ C ≥ U 19 Comparing Discretionary Access Control and Mandatory Access Control Advantage of DAC: Flexible: It's easy to customize. The administrator can quickly give or take away access to any part of the system, making it great for many different kinds of applications. Disadvantage of of DAC: Security Risk: The biggest problem with DAC is that it can be easily attacked. For example, if someone were to install a harmful program (like a Trojan horse) on the system, they might be able to get unauthorized access. Since DAC focuses more on convenience and flexibility, it sometimes doesn't have strong enough protection against these kinds of threats. 20 Comparing Discretionary Access Control and Mandatory Access Control Advantage of MAC: Ensure a high degree of protection in a way, they prevent any illegal flow of information. It is a great at preventing unauthorized sharing of sensitive information because they are very strict about who can see what. Disadvantage of MAC: Mandatory policies have the drawback of being too rigid/strict and they are only applicable in limited environments. Which is better? It depends on the situation. In places where security is the top priority, like military databases, mandatory policies are better. But in a more dynamic environment where needs change often, discretionary policies might be more practical. 21  1 IS 221 Database Management Lecture 6 Database Performance Tuning and Query Optimization Book: Database Systems- Design, Implementation and Management – Chapter 11- Coronel Morris: Chapter 11 : Database Performance Tuning And Query Optimization 1  Chapter Outline ▪ Goal of Database Performance ▪ Database Performance Tuning ▪ Performance Tuning: Client and Server ▪ DBMS Architecture : Client-Side Components and Server-Side Components ▪ Indexes and Query Optimization 2 Database Performance-Tuning Concepts Goal of Database Performance: The primary goal is to execute queries as fast as possible, minimizing the time it takes to retrieve data. Database Performance Tuning: Involves a series of activities and procedures aimed at reducing the response time of the database system. Optimizing Performance: It is crucial to ensure that all components of the system are operating at their optimum levels. Minimizing bottlenecks is essential for enhancing overall performance. 3 Performance Tuning: Client and Server Database performance-tuning activities can be divided into those : 1- On the Client side: Goal: to generates SQL query that returns correct answer in least amount of time, using the minimum amount of resources at the server end. The activities required to achieve that goal are referred to as SQL performance tuning. 2- On the Server side: Goal: to Configure the DBMS to respond to clients’ requests as fast as possible, while making optimum use of existing resources. The activities required to achieve that goal are referred to as DBMS performance tuning. 4  DBMS Architecture Purpose: The architecture of a DBMS is designed to manage databases efficiently through various processes and structures. Client-Side Components: Client Process: Initiates SQL queries to the DBMS. SQL Query: The SQL command sent from the client to the server. Server-Side Components: Listener: Receives SQL queries from clients. Figure 11.1 - Basic DBMS Architecture Scheduler: Manages the execution of SQL requests concurrently. Lock Manager: Controls access to data by managing locks on database objects. Optimizer: Analyzes SQL queries to determine the most efficient way to execute them. 5 DBMS Architecture SQL Cache: Stores recently executed SQL statements or procedures for quick retrieval. Data Cache: Holds the most recently accessed data blocks in RAM, making data retrieval faster. I/O Operations: Handles read and write operations to and from disk storage. Database Storage: Table Spaces: Logical groups of data files storing related data. Data Files: Where the actual database data is permanently stored. Figure 11.1 - Basic DBMS Architecture 6 Query Processing The DBMS processes a query in three phases: 1. Parsing: The DBMS analyzes the SQL query and selects the best execution plan. 2. Execution: The DBMS executes the SQL query according to the chosen plan. 3. Fetching: The DBMS retrieves the required data and returns the result to the client. 7  Query Processing phases 1. SQL Parsing Phase Breakdown: Split the query into smaller units to be analyzed. Transformation: The original SQL query is modified into a more efficient version that is equivalent to the original. Query Optimizer Role: Analyzes the SQL query to determine the most efficient way to access data. Access Plans: Generated during parsing; these plans outline the steps the DBMS will take to execute the query and return results efficiently. SQL Cache Check: Before creating a new plan, the system checks if an existing access plan can be reused to save time. 8 Query Processing phases 2. SQL Execution Phase Execution of I/O Operations: Based on the access plan, the DBMS performs data retrieval and other I/O operations. Locks: Necessary locks are applied to ensure data integrity during transaction processing. Data Retrieval: Data needed for the query is retrieved from data files and loaded into the data cache for processing. 9 Query Processing phases 3. SQL Fetching Phase Result Set Generation: The DBMS compiles the results of the query. Result Delivery: Data is sent from the server cache to the client in batches or as requested, ensuring efficient data transfer. Example: Batched Result Delivery: If a query generates a large result set (e.g., 9000 rows), the server may send the first 100 rows to the client initially, and then wait for further requests to send more, until all results are delivered. 10 Query Processing Bottlenecks Definition: A delay during query execution, particularly during I/O operations, that slows down the overall system performance. Caused by the: – CPU – RAM – Hard disk – Network – Application code 11 Indexes and Query Optimization Indexes: Speed up data retrieval from the database. An index is an ordered set of values associated with pointers to the actual data records. Index scans are typically more efficient than full table scans. Data Sparsity and Indexing Data Sparsity: refers to the number of different values in a column. Low data Sparsity: Few unique values, e.g., gender column with values 'M' or 'F'. High data Sparsity: Many unique values, e.g., a name column. Understanding sparsity helps decide whether indexing a column will improve performance or not. 12 Optimizer Choices The Query optimizer can operate in one of two modes: 1- Rule-Based Optimizer: Uses predefined rules to determine the best way to execute a query. 2- Cost-Based Optimizer: Analyzes the statistics about the data to choose the most efficient query execution plan. 13  1 IS 221 Database Management Lecture 7 Database Administration Book:Database Systems- Design, Implementation and Management – Chapter 16- Coronel Morris 12th edition 1  2 Outline The Data-Information-Decision Making Cycle Dirty Data and Data Quality Role of a Database in an Organization Introduction to Database Systems Evolution of the Database Administration Function Information Systems (IS) Department Functions Role and Placement of a Database Administrator (DBA) Data Dictionary 2  The Data-Information-Decision Making Cycle The Data-Information-Decision Making Cycle 1. Data Collection: How users gather raw data. 2. Analysis: Application of intelligence to data. 3. Information: Processed data that becomes useful. 4. Knowledge: Organized and processed form of information. 5. Decision Making: Using knowledge to make informed choices 6. Actions: Steps taken based on decisions, leading to new data. 3  Slide 8- 4 Key Strategies for Ensuring Data Integrity Dirty Data: Refers to data with errors and inaccuracy. Data Quality: Ensures that data is accurate, valid, and timely to make the data reliable for decision-making. Data Profiling Software: Analyzes data and checks if data meets the organization's standards. Master Data Management (MDM) Software: Ensuring all systems have up-to-date and accurate information to prevent dirty data. 4 The Role of a Database in an Organization How management at each level interacts with data and databases? 1.Top Management Level: o Focuses on strategic decisions, growth, and policies. o Aims to reduce costs and increase productivity. o Provides overarching feedback. 2.Middle Management Level: o Involved in tactical planning and monitoring resource use. o Evaluates performance and enforces data security and privacy. 3.Operational Management Level: o Supports daily operations and ensures efficient query performance. o Enhances short-term operations and overall functionality. 5  6 Introducing of Database system: Special Aspects The three key aspects of introducing a database system: 1.Technological Aspect: 1. Focus: Setting up and maintaining the database system. 2. Key Points: Choose the right database and hardware, install, configure, and keep it running efficiently. 2.Managerial Aspect: 1. Focus: Organizing how the database is used. 2. Key Points: Decide who can access the database and how it will be used within the organization. 3.Cultural Aspect: 1. Focus: Ensuring users understands how to use the system effectively. 2. Key Points: Communicate and train users to ensure they understand and can use the database properly. 6 Evolution of the Database Administration Function Information Systems (IS) Department What They Do: Helps users with data management and provides solutions for their information needs. Database Administrator Responsibility: Manages and controls the central database that everyone in the organization shares. Systems Administrator Role: General coordinator of all DBAs.(The boss of the Database Administrators) Data Administrator (DA) or Information Resource Manager (IRM) Authority: Holds more responsibility than a DBA 7  Information Systems (IS) department within an organization Information Systems (IS) department within an organization, highlighting two key functional areas: 1.Application Development: What it is ? This is about making the software that the company uses to do its work. It includes coming up with new software ideas, building them, and making sure they keep working over time. 1.Database Operations: What it is ? This part deals with handling all the data the company uses and stores. It involves making sure that the data is organized, safe, and can be accessed by people in the company who need it. 8 Role and Placement of a Database Administrator (DBA) ◼ Who is a DBA? A Database Administrator (DBA) manages the control of centralized and shared databases. ◼ What roles can a DBA have? 1. Staff Position: 1. Role: Acts as a consultant. 2. Responsibilities: Helps devise strategies for data administration but doesn't enforce policies or resolve conflicts. 2. Line Position: 1. Role: Plays a key managerial role. 2. Responsibilities: Plans, defines, implements, and enforces data management policies and procedures. 9 Responsibilities of (DBA) The Key Responsibilities of a Database Administrator (DBA) 1- Planning: Sets standards and procedures. Ensures rules are followed. 2- Design: Conceptual Design: Determines what data is needed and how it should be organized. Logical Design: Focuses on how the data will be stored in the database with respect to relationships and constraints. Physical Design: Involves the actual setup of the database on a physical storage system. 3--Testing and Maintenance: Testing: Checks for errors and ensures the database runs smoothly. Maintenance: Includes updating the database and switching old systems to new ones. 4- Support and Training: Helps users understand and utilize the database effectively. 5- Data Quality and Management: Continuously monitors and improves the quality of data within the database. 10  Data Administrator (DA) Data Administrator (DA) vs. Database Administrator (DBA): DA: - Plans for the long-term. - Sets company-wide data rules. DBA: - Manages day-to-day database tasks. - Handles technical details. DBA Skills Needed: 1- Managerial Skills: Understands how the business works. Keeps things organized. Solves problems when they come up. 2- Technical Skills: Knows a lot about databases. Keeps systems updated and running smoothly. Tools DBAs Use: 1. Monitoring Tools: Watches over database performance. 2. Performance Tools:Improves how fast and well databases work. 3. Backup Tools: Regularly saves data to prevent loss. 11  Data Dictionary What is a Data Dictionary? It's a centralized repository that stores descriptions of all data objects in a database, such as tables, columns, indexes, and relationships. Types of Data Dictionaries: 1. Integrated: This is included within the Database Management System (DBMS) and is directly managed by it. It is closely tied to the database and often automatically updates with changes to the database structure. 2. Standalone: This is a separate, not included within the DBMS and may not update automatically. (require more effort to keep it synchronized with the database changes.) 12