Database Security Chapter 30 PDF

12/22/2024 Database Security CHAPTER 30 1 Outline ▪ Introduction to Database Security Issues ▪ Discretionary Access Control Based on Granting and Revoking Privileges ▪ SQL injection Slide 30- 2 2...

12/22/2024 Database Security CHAPTER 30 1 Outline ▪ Introduction to Database Security Issues ▪ Discretionary Access Control Based on Granting and Revoking Privileges ▪ SQL injection Slide 30- 2 2 1  12/22/2024 30.1 Introduction to Database Security Issues Database security is a broad area that addresses many issues: ◦ Legal and ethical (the right to access information), policy (e.g. whether personal medical records should be made public), and system-related issues (security function to be handled at OS or DBMS level etc) Threats to databases can result in: ◦ Loss of integrity ◦ Improper modification of information (e.g. creating, inserting, and updating data) ◦ Loss of availability ◦ Legitimate user cannot access data objects ◦ Loss of confidentiality ◦ Unauthorized disclosure of confidential information (e.g. violation of the Data Privacy Act) Slide 30- 3 3 Introduction to Database Security Issues (cont’d.) Database Security: Not an Isolated Concern. DBMS alone cannot be responsible for maintaining the confidentiality, integrity, and availability of the data. Database works as part of a network of services including : ◦ Applications, Web servers, firewalls, SSL terminators, and security monitoring systems Four main control measures are used to provide security of data in databases: ◦ Access control - is handled by creating user accounts and passwords to control the login process by the DBMS. ◦ Inference control - E.g. It is sometimes possible to deduce or infer certain facts concerning individuals from queries that involve only summary statistics on groups; this must not be permitted. ◦ Flow control - prevents information from flowing in such a way that it reaches unauthorized users. ◦ Data encryption - used to protect sensitive data (such as credit card numbers) that is transmitted via some type of communications network Slide 30- 4 4 2  12/22/2024 Introduction to Database Security Issues (cont’d.) Two types of database security mechanisms: Discretionary security mechanisms ◦ Used to grant privileges to users including the capability to access specific data files, records, or fields in a specified mode (such as read, insert, delete, or update). Mandatory security mechanisms ◦ Classify data and users into various security classes ◦ Used to enforce multilevel security by classifying the data and users into various security classes (or levels) and then implementing the appropriate security policy of the organization. ◦ For example, a typical security policy is to permit users at a certain classification (or clearance) level to see only the data items classified at the user’s own (or lower) classification level. Slide 30- 5 5 Database Security and the DBA Database administrator (DBA) ◦ Is the central authority for administering database system ◦ has a DBA account, sometimes called superuser or system account ◦ Which provides powerful capabilities, not made available to regular database accounts and users DBA-privileged commands include commands for granting and revoking privileges to individual accounts, users, or user groups and for performing the following types of actions: ◦ Account creation ◦ Privilege granting ◦ Privilege revocation ◦ Security level assignment Slide 30- 6 6 3  12/22/2024 30.2 Discretionary Access Control Based on Granting and Revoking Privileges Typical method of enforcing discretionary access control in a database system is based on the granting and revoking of privileges. Two levels for assigning privileges to use a database system : ◦ Account level ◦ The DBA specifies the particular privileges that each account holds independently of the relations in the database. ◦ Example: CREATE, DROP, ALTER, MODIFY, SELECT privileges ◦ Not defined for SQL2 (DBMS vendors decide) ◦ Relation (or table) level ◦ At this level, the DBA can control the privilege to access each individual relation or view in the database. Slide 30- 7 7 Discretionary Access Control (cont’d.) Relation or table level (cont’d.) ◦ Each relation R assigned an owner account ◦ Owner of a relation given all privileges on that relation ◦ Owner can grant privileges to other users on any owned relation ◦ SELECT (retrieval or read) privilege on R ◦ Modification privilege on R ◦ References privilege on R Slide 30- 8 8 4  12/22/2024 Specifying Privileges Through the Use of Views Consider owner A of a relation R wants another account B to be able to retrieve only some fields of R, ◦ Then A can create a view V of R that includes only attributes A wants B to access ◦ GRANT SELECT ON V TO B Can define the view with a query that selects only those tuples from R that A wants B to access Slide 30- 9 9 Revocation and Propagation of Privileges Revoking of Privileges ◦ Useful for granting a privilege temporarily. ◦ E.g. The owner of a relation may want to grant the SELECT privilege to a user for a specific task and then revoke that privilege once the task is completed. ◦ REVOKE command is used to cancel a privilege Propagation of privileges using the GRANT OPTION ◦ When owner A of a relation R grants a privilege on R to another account B, the privilege can be given to B with or without the GRANT OPTION. ◦ If GRANT OPTION is given, B can grant privilege to other accounts ◦ DBMS must keep track of how privileges were granted if DBMS allows propagation Slide 30- 10 10 5  12/22/2024 Simple GRANT Syntax GRANT privilege_type [, privilege_type]... ON object_type TO user [user]... [WITH GRANT OPTION ] Slide 23- 11 11 Example: Granting/Revoking Privileges Suppose that the DBA creates four accounts—A1, A2, A3, and A4—and wants only A1 to be able to create base relations. DBA must issue the following GRANT command in SQL: ◦ GRANT CREATETAB TO A1; ◦ The CREATETAB(create table) privilege gives account A1 the capability to create new database tables (base relations) and is hence an account privilege. ◦ A1 can create new tables Suppose A1 creates two base relations EMPLOYEE and DEPARTMENT. Account A1 wants to grant to account A2 the privilege to insert and delete tuples in both of these relations. But, A1 does not want A2 to be able to propagate these privileges to additional accounts. A1 can issue the following command: ◦ GRANT INSERT, DELETE ON EMPLOYEE, DEPARTMENT TO A2; ◦ The owner account A1 of a relation automatically has the GRANT OPTION ◦ A2 was not given the WITH GRANT OPTION. Hence, A2 cannot give privilege to other users Slide 23- 12 12 6