Users can seriously jeopardize the integrity of a database. User limits and selected privileges are two of the most frequent approaches for preventing unauthorized users from accessing sensitive data while restricting access to authorized users (Taylor, 2013). Many occurrences involving database compromise entail user manipulation. In the following sections of this article, three alternative scenarios will be discussed that would necessitate database protection schemes via access control mechanisms, followed by recommended solutions and reasons for the selection of the offered solution. In an e-commerce platform, it is customary for the buyers to insert sensitive personal information including addresses, contact details and relationship status. Using this information, anybody can impersonate the buyer and harass him in personal life. Hackers collect buyers’ info to steal their financial or medical records and sell them in the underground at high cost. To avoid this from happening, major e-commerce site builders must restrict access to their customer info databases through the use of grant or revoke permissions.
Proposed Protection Operation
In this scenario, only the sales manager should be privileged with the access to customer databases and made accountable for any type of customer information leakage. It will prevent any user other than the manager from accessing the database and see or collect sensitive customer information. The Grant command will look like as follows:
GRANT all ON customer TO {manager_sales};
Revoke delete ON customer TO {manager_sales};
Rationale
The proposed protection operation–granting specific user with privileged permission–has been selected for two reasons. First of all, it will not allow only the assigned user to access the database and perform any type of operations except deletion of an entry. Secondly, it will automatically revoke other users from accessing the database. Another solution might be revoking PUBLIC from accessing the database except the selected user (Fehily, 2014).
Scenario 2
SQL injection is a potential threat to any database, in which an attacker uses regular SQL commands to exploit access and control. In its simplest from, the attacker sends an insert command through any of the interfaces like GUI, login page, register page etc. and forces the database to return sensitive user data like userid, password or session access. For example, following code can be used to extract userid from the database (Exploiting SQL Injections In Insert Statements, 2007).
insert into secret values ($id, 'Welcome');
Using the id returned (here 1000) from the previous command, the attacker can attempt to exploit password of the user using following command.
insert into secret values (1000, (select passwd from users where id=1)) #, 'Welcome');
Proposed Protection Operation
The database designer can revoke insert statement into user entry table unless it is validated through a secondary authentication system like mobile verification or email verification, the record to mobile number or email address being saved to a separate table not accessible to public. For example,
REVOKE all ON user TO {Public};
Rationale
Revoke statement is selected here to avoid any action to database unless it comes from a registered user with valid email or mobile number. The system will send a verification code via email or text and prompt user to enter the code to access the system. If the user does not have any registered account into the website, the database will not respond to any query and revoke all types of permissions to the public users.
Scenario 3
It becomes essential for certain transactions to ensure that the entered data cannot be modified after certain period of time. For example, in banking or financial transactions, the teller or accountant or common users should not be allowed to delete or edit data after a certain period like one day after the actual transaction happens or after bank is closed for that day, to prevent data forgery or illegal manipulation.
Proposed Protection Operation
In these cases, all users including the manager should be barred from all types of database command that might affect the integrity of the data. A database designer should impose a revoke operation on the condition that of the specified time lapses. For example,
REVOKE all ON user TO {Public} (Some conditions for specifying time limit)
Rationale
The time limit should allow the user to modify or delete any mistakes immediately. Without this option the user will not be able to correct any mistakes and will have to go with wrong entries. On the other hand, without the time limit, the user will be able to change the entry from anywhere, anytime beyond office hour and cause serious damage to database integrity. The revoke statement will restrict all types of database commands and keep the data safe malpractice and manipulation.
References
Exploiting SQL Injections In Insert Statements. (2007, October 07). Retrieved from Not So Secure: https://www.notsosecure.com/exploiting-sql-injections-in-insert-statements/
Fehily, C. (2014). SQL (Database Programming). Questing Vole Press.
Taylor, A. G. (2013). SQL for dummies. Wiley Publishing Inc. .
