Answer To: This is a graduate level Lab Project. All queries involved should be executed only using an Oracle...
Neha answered on Feb 19 2021
[Document title]
Student Name
Table of Contents
1. Introduction 2
2. Database 3
3. Security Concerns 3
3.1. CIA Triad 4
4. Risks Associated with Database 5
4.1. SQL Injection 5
4.2. External Vulnerability 5
4.3. Weak Audit Trail 5
4.4. Excessive Privileges 6
4.5. Types of Attacks 6
4.5.1. Credential Stuffing 6
4.5.2. Phishing Attacks 6
4.5.3. Denial of Service (DoS) 7
5. Database Security Requirements 7
6. Bank of America Data Breach 7
7. Timeline 8
8. Data Modelling 10
8.1. Conceptual Data Model 10
8.2. Logical Data Model 13
8.3. Physical Design 16
8.3.1. Database Implementation 19
9. Security and Privacy Requirements 25
10. Protecting the Data Privacy of Consumers 26
11. Security and Privacy Implementation 27
11.1. Preparing for changing standards 27
11.2. Authorized Users Access 27
11.3. Use of WASP 30
11.4. Prevention from SQLIA 31
11.5. Access Management 32
11.6. Auditing and Accountability 33
11.7. Encryption 33
12. Security Testing & Verification of DB Implementation 34
12.1. Creating VPD (Virtual Private Database) 35
13. Some Practical Tips 38
14. Conclusion 40
15. References 42
Table of Figures
Figure 1: CIA Triad 5
Figure 2: Database Risks 5
Figure 3: Database Design Process 9
Figure 4: Conceptual Model 12
Figure 5: Logical Model 15
Figure 6: Physical Model 18
Figure 7: Three Levels of Encryption 25
1. Introduction
In the modern era, data is considered the most valuable asset for an organization. It has been found that organizations adopting information systems to large extent. These organizations are supporting the data sets that contain the sensitive data. Hence, data security has become a serious concern for all organizations. It includes the prevention of confidential data that is stored in databases. The reason for providing database security is to mitigate any illegal access at any level. With the implementation of database security, user actions get restricted on the basis of their privileges. Many organizations are strictly concerned about mitigating any illegitimate access to the data (Deepika & Soni, 2015). In this report we will discuss how the database design can be used by the developer to have a secure environment for the data present in the database warehouse. A large bank database of American Bank has been considered for further analysis the report. The main objective of the report is to discuss security concern and security measures that can be provided to the existing database of the bank. Also, the database for bank will be prepared using Oracle database engine and what type of privileges will be provided to users. Some test scenarios will be prepared that can show the users have access according to their access privileges.
The report will discuss about the different aspects which can be utilised by the banks and create a new database design which can be followed by them to have a more secure environment for the personal information shared by the business owners and the individuals. It is not easier to have a database design which can be appropriate from the engineer point of view and also for the customer, but the following report can be used to have step by step approach and create a more secure designed by keeping the ethical considerations for the data. The report further, consists of various techniques which can be used by the attackers to attack database and sensitive data. It also provides detailed information about the countermeasures which can be taken and how we can grant the privileges on limited terms so that every user is not able to access the data and perform malicious code.
2. Database
A database can be defined as the collection of data which is present on the hard drive of the computer system. The databases are generally used as they allow us to have authorised user to access, analyse and enter the data easily and efficiently. The database may also have the collection of tables, queries, and different views. The data which is present in the database is generally stored in the organised manner which has model aspects and IT support different processes which need information retrieval and storage. We can store large amount of the data in the repository which is known as the database. User interface which is used for the database is known as the database management system and the database management system can be defined as the software application which allows the user to interact with the database and other applications for analysing and capturing the data.
The database is generally used to organise the data to have better performance and quick retrieval of the data as it maintains the data in the form of indices. DBMS is used to perform the function of the concurrency control. This management system allows us to perform the data recovery operations over the data present in the database. Currently the organizations need databases for storing different types of data as it provides to have faster speed and affordable cost. There are different advantages of the database and one of them is that it allows us to have automation with different procedures, save resources and reduce human labour.
3. Security Concerns
The privileges which are provided by the databases can be abused in different manner. The user can abuse the privileges for the unauthorized purpose. There are different manners in which it can be abused like legitimate privilege abuse, unused privilege abuse and excessive privilege abuse. This type of threat is most dangerous as the authorised users can misuse the data present in database. These privileges can be abused by the user and it creates unnecessary risk for the owner. If we grant excessive permissions to the user then it can be problematic. Around 80% of the attacks takes place on the company data and they are executed by either employees or ex-employees. We can grant many privileges or not revoke these privileges on time can create unnecessary simpler environment for the users to execute. Some of the actions can be executed inadvertently or without having any perception of the extent as they are abuse of the privileges.
3.1. CIA Triad
Database security is differentiated in three triads - confidentiality, integrity and availability. It is a model that is intended to manage various policies for data security within the organization's database structure. Three triads are discussed as following:
Confidentiality - The confidentiality name is given to some standards that can restrict the database access. The access will be restricted to only authorized users. It will help in mitigating access to unauthorized users. It will be responsibility of the organization to create some strict policies for accessing the data and stop the access when an employee leaves the organization as 80% of the breach occurs by ex-employees only.
Integrity - The term integrity means that information stored in the database is accurate and reliable. The modification will be done by only authorized users. The consistency and accuracy must be maintained by authorized users in order to protect it from unauthorized users.
Availability - It implies that data must be available to authorized users despite the time period. It can only be maintained when the hardware is managed consistently. The redundancy of data can be maintained when the proper backup is planned and saved in some drive. Additional security tool or some software and hardware application like firewalls and proxy servers can monitor server downtime and inaccessible information because of some malicious activities like denial of service (DoS) attacks or any network intrusions (Ali & Afzal, 2017).
Figure 1: CIA Triad
4. Risks Associated with Database
The database systems are generally used to have a simple backend functionality. The data is supplied by the user as the input and it is generally used dynamically by building the SQL statements which can affect the database directly. Security is the major issue in the world currently and it is the incredibly challenging task which is faced by the people around the world. The databases are overly complex and there are many security professionals who do not understand the security issues or the risk which are related with the databases.
Figure 2: Database Risks
4.1. SQL Injection
The input injection can be used as the attack which has a for subverting the original intent of the application which is submitted by the attacker and it behaves like the SQL statements which are directly entered into the backend database. There are majorly 2 types of input injection which are no SQL injection and SQL injection. The SQL injection has the target for the traditional database system, and it attacks involving the injection of unauthorized statements into the input fields. The no SQL injections are used to target the big data platform and it involves inserting the malicious statements into the big data components like the map reduce or hive. The successful input injection attack into the no SQL or SQL can allow the user to have unrestricted access for the database. To solve this issue, we can either use stored procedure instead of the direct queries or implement MVC architecture.
4.2. External Vulnerability
The cybercriminals are the hackers use the advanced text techniques which can blend multiple tactics like spear phishing emails or the malware which can penetrate the company and steal sensitive information out of them. The user is generally unaware that malware has already infected the device and legitimate user behaves the user who can access network or the sensitive data. There are few countermeasures for such attack like malware enable firewall protection and install antivirus.
4.3. Weak Audit Trail
In case of weak audit trail, risks that are associated with consistence, discouragement, location, forensic analysis and recuperation. If the organization fails to collect audit database details can be a serious risk at different levels of database security. The automated recording of the transactions in database involves some sensitive data which can be the part of any database deployment. The organization which has weak database ordered mechanist can find odds with the industry and the regulatory requirement of governments. Most of the ordered mechanists are not aware of the and users because the activities are associated with the web application and account name. In this case, the administrative user can abuse his access privileges can turn off the native database auditing feature in order to hide any malicious activity.
4.4. Excessive Privileges
Sometimes, the authorized user can abuse his privileges for unauthorized purpose. Excessive privileges can be of many types – legitimate privileges abuse, excessive privilege abuse, and unused privilege abuse. These risks are considered as most dangerous as it is the authorized user who is misusing the access privileges. It has been analyzed that about 80% of the attacks are done by the company’s employees or ex-employees (Malik & Patel, 2016).
4.5. Types of Attacks
4.5.1. Credential Stuffing
The credential stuffing is the type of cyber-attack which is generally used to target the personal data of the customers who are related with the band. In this case the hackers try to steal the credentials regarding account, and they get unauthorised access for the user account with the help of automated large scale login requests. After stealing the information, the hacker can use this to bombard the website or the servers and gain access for the critical infrastructure. This practice is called as the credential stuffing. The hacker is able to get list of the username and passwords with the help of dark web and it allows the hackers to save a lot of time to avoid the requirement of password guessing game. As per the report there is an automated process with which the hacker is able to log thousands of the bridge passwords and the usernames with the help of web automation tools. The credential stuffing is different from the brute force attack. In credential stuffing the attacker generally use the username and passwords which are known to him due to some relationship. For the banks this practice has emerged as the credible threat which can get worse as the number of breaches will increase in coming future.
4.5.2. Phishing Attacks
This is another type of cyber-attack which is generally performed by the attackers to get access for the user data which also includes the credit card numbers and the login credentials. Recently there was seen an immense increase in this type of attacks which targets employees of the bank. This takes place when the attacker tries to attack over unsuspecting victim with the help of malicious link which leads to the installation of malware and then it freezes the system which becomes the part of ransomware attack. This attack can have devastating results over the business specially for the bank. This attack can be used to get the access for foothold present in the network which can be helpful to have larger attack like the advanced persistent threat event. In this case the employee gets compromised to bypass the security parameters, gain privileged access for the secure data or distribute the malware within the closed environment of bank.
4.5.3. Denial of Service (DoS)
This attack restricts the access of the authorized user to use various database services. DOS can happen utilizing distinctive procedure. The attackers get the access to the data set and attempt to crash the server over-burdening, network flooding and information debasement can be the methods for making states of DOS attack (Basharat & Azam, 2012).
5. Database Security Requirements
The database security requirements are different from other computing requirements. The critical issues with the database system are access control, the prohibition of misleading information, confirmation of clients, and to provide reliability to the organization's data. The main security requirements for database are mentioned below:
a) Maintain the integrity of database: The organization's data set are safe to actual physical issues like a power failure. The physical design can be recreated in case of any disastrous event.
b) Maintain logical Database integrity: It is important to preserve the database structure. By maintaining the logical database integrity, when a modification to one field is made, it must not affect the other field.
c) Ability of Auditing: The audit details about who has accessed the database and what changes have been made must be saved so that unauthorized information can be tracked.
d) Access control: Only authorized information should be accessed by the authorized user only.
e) User validation: The authenticated users must be identified for the audit trail and to get the permissions for accessing the data.
f) Data Availability: Data must be available to authorized users (Gaikwad & Raut, 2014).
6. Bank of America Data Breach
Recently the Bank of America Corporation has received that they have faced the data breach on the information about business clients for the Paycheck protection program. This bridge took place on 22nd of April as they uploaded the payback protection program applications onto the United States and small business administration was used as the test platform. According to the information filed with the California attorney general’s office the test platform was used by the bank. It provided only limited access to the lenders for testing the Paycheck protection program before they started with the second round. They informed that the application information was also visible to other authorised leaders and the vendors. There was no indication that the information was misused or viewed by the leaders or their vendors. The information was not visible to any other business clients who were applying for the loans at any time.
The compromise information also included business details like the tax identification number for address or the information of the business owner like their name, Social Security number, citizenship status, address, email or the phone number. They were having applicants from the nationwide pool and the customers were from different states. They did not specify the name of the states, but the spokesperson described them as the small number of clients. They also informed that the data breach did not affect the submission of the applicants and it asked the SBA to remove visible information on the same day. Every bank customer has their own personal details which needs to be kept secret from the unauthorised access. Personal details are shared with the bank, which is mandatory, but it is responsibility of the bank to keep it safe from the hackers and do not share it with any 3rd party. The personal data about the business owner is more at risk.
The bank did not inform about the number of customers which were affected as they declined to comment on this article. It is very important for the clients that they also monitor their account for next few years. It is the most important responsibility of the bank to keep the information confidential and they notify that there personal and business information is kept protected from the hackers or unauthorized access. The bank also informed that it was the first time they had large lender for launching the online paycheck protection plan portal. The release of this plan loan data was intended to bring some transparency for the loan program which can support the small businesses also during the pandemic time. There were big mistakes from some of the banks which can be caused due to more transparency as compared to the Small Business Administration.
7. Timeline
For designing the database, it needs to have a proper design which allows us to have accurate information and up to date data. The correct design of the database is essential to achieve the goals while working with the database we need to invest our time to learn the basic principles of good design full storm the design of the database should be able to achieve the requirements which were set initially, and it should also accommodate changes easily. There are certain principles which can be used as the guidance for designing process of database. The first principle is to remove the duplicate information or the redundant data which is bad as it wastes a lot of space and increases the error and inconsistency in data principle is completeness and correctness of data which is particularly important. If the database contains some incorrect information, then any report which is generated based on that information will also show incorrect information. The following are few key points which can be used as guidelines for having good database design.
· Divide the overall information into the subject based tables which can help us to reduce redundant data.
· Provide access for the information which needs joining of the information presented the tables together as per the requirement.
· Helps to ensure and support the integrity and accuracy of the data.
· Accommodate the data processing and reporting which is required.
The first step is to determine the purpose of the database as it can help us to cover the remaining steps. Secondly find an organised required information. Collect all the types of information which we want to store in the database like the order number of product number but in this case, we will store the information about the customers and their account type. We will store personal information about the customers like their name, address, phone number or email address. The all information will be divided into tables. We can divide the information items into the major subjects or entities like the customer information or account details which of the subject will become the table for the database.
Create the information items into the form of columns. Decide which information will be stored in each table. Every table will have the field and it will be displayed as the column name of the table. For example, the customer may have fields like last name or email address. Every table needs to have a primary key which will be specified in this step. The primary key of every table is used as the uniquely identify field for each row. For example, we will have the customer ID as the unique value for each table. Set the relationship between the tables. Analyse each table and decide how that data present in one table will be related with another table. We can add fields to the table or create new tables to establish the relationship between them as per the requirement. Now analyse the design to check if it has any error or not. Create the table and try to add few records as the sample data. Once the database is created the next step is to apply the normalization rules so that the database is in its normalized format.
Figure 3: Database Design Process
The database design can be created if we follow the software development life cycle steps. The first step is to define the objective or the problem of creating the database design. The first step is to define the problem and then the second is to specify the requirements. We can collect the user database requirements with the help of elicitation method. It is the task of database designers to collect all the information. The 3rd step is to analyse the data requirements. This step is database management system independent. The database designers will produce conceptual data model which will be expanded to the logical data model and then it will be converted into the physical data model.
The conceptual data model is the easiest one to design and it will be converted to the physical data model which can be directly implemented into the database. The next stage is for the database designers who will construct logical design of the database. The logical design will have all the tables, fields and the relationship between the tables. We will also declare the constraints of the database. The database designers will then refine and normalise the whole database so that it can eliminate data redundancy present in the database. The last stage is the physical implementation of the database which will be managed by the DBMS and it is DBMS dependent step.
Action
Duration
Define problem
1 week
Requirements specification
4 days
Data requirements
3 days
Conceptual Design
2 days
Logical Design
3 days
Physical Design
5 days
8. Data Modelling
The data model can be defined as the process in which we can create data model for the information which needs to be stored in the database. This data model can be used as a conceptual representation of all the data objects and the relationship or Association which is present between the data objects along with the rules. Data modelling is generally followed as it helps to have visual representation of the data and all the business rules, government policies and the regulatory compliance over the data. The data model is used to make sure that we have consistency for the naming conventions, semantics, security, and default values and also make sure that we have a good quality of the data in it.
The data model can be defined as the abstracted model which can organise data semantics, consistency constraints on the data description of data. The data model has major emphasise over what type of data is required how we can organise it instead of thinking about the operations which can be performed over it. The data model is used as the building plan of architect which can help us to build conceptual models and great relationship between all the date items. The basic 2 types of data model techniques are unified modelling language and entity relationship model.
The major goal of using the data model is to make sure that all the data objects which are needed by the database are represented in the most accurate manner and if there is omission of the data then it can result in creating faulty reports which can also produce the incorrect results for the user. The data model is used by the designer to design the database at the logical, conceptual and physical level. We can use the data model structure to make sure that we are defining relational tables, stored procedures, primary keys and foreign keys in the best possible manner. The data model is generally followed to get a clear picture of the base data and it can be used to create the physical database by the developers. It is also helpful to find out the redundant ever missing data from the database. The initial phase of creating data model is time consuming but it can make the IT infrastructure better and help us to get cheaper and faster maintenance for it.
8.1. Conceptual Data Model
The actual data model is a type of data modelling which help us to get the organised view of database concepts and relationship in it. The major purpose for creating the conceptual data model is to find out the entities or names of table, attributes or column names and the relationship among the tables or entities. In the conceptual data model level, all the detailed information will be present for the actual database structure. The business stakeholders and the architects can create a conceptual data model with the information provided by the clients. The basic terms for creating the conceptual data model or entity which is the real-world thing, attribute which is the property or characteristics of the entity and relationship which is the Association or dependency between 2 entities.
The characteristics of conceptual data model are extremely useful for the designer. It offers the wide coverage for the organization on the basis of business concepts. The conceptual model can be used for designing and developing the model of the basis of business audience. We can develop the conceptual model independent of hardware specification like the location, software specification, vendor, technology, or the data storage capacity. The major focus of the conceptual data model is to represent the data as the user wants to see it in the real world. The conceptual data model is known as the domain models which are used to create some common vocabulary which can be utilised by the stakeholders for creating the basic concepts and scope. We can develop the conceptual data model based on the detail information which is provided by the users and it is the critical task for creating the overall bad database design. In this particular phase we can create the structure of the database which can be implemented along with the constraints.
This conceptual model is extremely easy to understand for the user and can be used to create the basis for communication with the users. It does not have any implementation details but allow the user or designers to focus on specific properties of the data without having major concern about the storage details for the file structures. As per research done by the users the conceptual modelling can be used for introducing additional formalist which can capture the wide area for representations and also compare different data models to make it easier for representing the human factors. The processes which are employed by the designers for eliciting the user requirements and representing them in the conceptual model has received major attention during the research work. A deeper understanding is required to utilise this process as it can give useful insights for pedagogy in the area, partially automating the database design and building the knowledge-based systems for support. With the help of knowledge which is gained by the user the database design is just not restricted for experience or well-trained designers.
Currently the bank is facing security issues with the design and they are not able to stop the breach. Recently they have faced the data bridge and hacker has stolen the information about the individuals or the business owners. The information needs to be kept very securely as it is personal for the people and it is the responsibility of bank that that information is not shared with any other person or group. The following diagram shows the conceptual data model for the bank which can be used by them.
Figure 4: Conceptual Model
As the image shows that the bank will have customers, account, documents, payments, performance reports, financial products, event types and event. All the entities which are declared in the diagram are connected with the single entity events. We can use events as the major entity and the diagram follows star schema. The events are used to show the different types of events which are taking place in the banks for specific account. The customers will have their information in the bank for their personal details like first name, last name, email address, address, phone number or any other details. The customer can be business owner who will have their security number, text number or any other details related to the business. The customer is dependent on the account stable.
Each customer will have their unique account and the further account is dependent over the events table. Events is related with the event type table which will show what type of event can be performed in the bank. The event type can be deals, payments, financial transaction or the settlements. There can be different event types, and each will be referenced by the events table. The financial products are used as the separate entity which will be used by the customers. Payments entities has interdependent relationship with the events. The payments will be done by the customers from their account and it will be registered as the event of financial transaction.
The payment will be deducted from the account or credited into the account of the customer. The customer will also have documents which can be the passbook. Every detail about the customer will be present in the table regarding the document. Each customer may have to provide the basic information like proof for their business, proof for the address or bank card, passbook or any other document for confirming their identity. All these documents will be present in the document table and they will be referenced by the event and we can join both the tables to get information about the customers. The performance reports will be used to analyse the performance of each staff member.
With the help of the performance reports it will be easier for the bank to analyse the number of customers they have successfully installed in their bank and how much loan they have credited to get the profit from the customers. The performance report will be generated on the basis of transactions done by the customers. This conceptual data model can be designed on the basis of basic information collected by the designers. It can be used to create the logical model in the next step. When we are working with...