Chapter 6: Integrity and Security

1. Chapter 6:Integrity and Security Thomas Nikl 19 October, 2004 CS157B

2. Integrity! • Integrity constraints ensure that changes made to the database by authorized users do not result in loss of data consistency.

3. Domain Constraints • A domain of possible values must be associated with every attribute in the database. • Declaring an attribute of a particular domain acts as a restraint on the values it can take. • They are easily tested by the system • EX1: cannot set an integer variable to “cat”.

4. Creating New Domains • The ‘create domain’ clause allows you to create your own domain types. • EX1:create domain Dollars numeric(12,2) • These create numerical domains with 12 total digits, two of which are after the decimal point.

5. Referential Integrity • Ensuring that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. • EX1: In a banking system, the attribute branch-name in Account-Schema is a foreign key referencing the primary key of Branch-Schema.

6. Database Modification • Inserting, deleting and updating can cause violations of referential integrity. • Therefore, the system must check that referential integrity is maintained when you perform these operations. • If referential integrity is violated during these operations, the default action is to reject the operation. • However, you can define other actions (more later).

7. Referential Integrity in SQL: Foreign Keys • Foreign Keys are specified as part of the SQL ‘create table’ statement by using the ‘foreign key’ clause. • By default, a foreign key references the primary key attributes of the referenced table.

8. Foreign Key Declaration • EX1: create table account ( account-number char(10), branch –name char(15), balance integer, primary key (account-number), foreign key (branch-name) references branch, check (balance >= 0))

9. Cascading • When referential integrity is violated during a modification, instead of just rejecting the modification, you can cascade: • Delete cascade • Update cascade

10. Delete Cascade • In a delete cascade, anything that has references to the deleted item is also deleted.

11. Update Cascade • In an update cascade, when the updated item results in a violation of referential integrity, the system will update accordingly to fix the problem.

12. Defining a Cascade Operation • EX1: create table account (… foreign key (branch-name) references branch on delete cascade on update cascade, …)

13. Assertions • An assertion is a predicate expressing a condition that we wish the database to always satisfy. • Domain constraints and referential integrity constraints are special forms of assertions. • But there are many constraints we cannot express by using only these special forms. • EX1: The sum of all loan amounts for each branch must be less than the sum of all account balances at the branch.

14. Creating an Assertion • EX1: create assertion sum-constraint check (not exists (select * from branch where (select sum (amount) from loan where loan.branch-name = branch.branch-name) >= (select sum (balance) from account where account.branch-name = branch.branch-name)))

15. Creating an Assertion • When an assertion is created, the system will test it for validity. • If the assertion is valid, then any future modification to the database is allowed only if it does not cause the assertion to be violated. • But assertions can create a considerable amount of overhead, especially if complex assertions have been made. • Therefore, assertions should only be used with great care.

16. Triggers • A trigger is a statement that the system executes automatically as a side effect of a modification to the database. • To design a trigger we must meet two requirements: • 1. Specify when a trigger is to be executed. This is broken up into an event that causes the trigger to be checked and a condition that must be satisfied for trigger execution to proceed. • 2. Specify the actions to be taken when the trigger executes. • This is referred to as the event-condition-action model of triggers

17. Triggers • The database stores triggers just as if they were regular data. • This way they are persistent and are accessible to all database operations. • Once a trigger is entered into the database, the database system takes on the responsibility of executing it whenever the event occurs and the condition is satisfied.

18. Need for Triggers • EX1: A good use for a trigger would be, for instance, if you own a warehouse and you sell out of a particular item, to automatically re-order that item and automatically generate the order invoice. • So, triggers are very useful for automating things in your database.

19. Security! • The information in your database is important. • Therefore, you need a way to protect it against unauthorized access, malicious destruction or alteration, and accidental introduction of data inconsistency.

20. Database Security • Database Security refers to protection from malicious access. • Absolute protection is impossible • Therefore, make the cost to the perpetrator so high it will deter most attempts.

21. Malicious Access • Some forms of malicious access: • Unauthorized reading (theft) of data • Unauthorized modification of data • Unauthorized destruction of data • To protect a database, we must take security measures at several levels.

22. Security Levels • Database System: Since some users may modify data while some may only query, it is the job of the system to enforce authorization rules. • Operating System: No matter how secure the database system is, the operating system may serve as another means of unauthorized access. • Network: Since most databases allow remote access, hardware and software security is crucial. • Physical: Sites with computer systems must be physically secured against entry by intruders or terrorists. • Human: Users must be authorized carefully to reduce the chance of a user giving access to an intruder.

23. Authorization • For security purposes, we may assign a user several forms of authorization on parts of the databases which allow: • Read: read tuples. • Insert: insert new tuple, not modify existing tuples. • Update: modification, not deletion, of tuples. • Delete: deletion of tuples. • We may assign the user all, none, or a combination of these.

24. Authorization • In addition to the previously mentioned, we may also assign a user rights to modify the database schema: • Index: allows creation and modification of indices. • Resource: allows creation of new relations. • Alteration: addition or deletion of attributes in a tuple. • Drop: allows the deletion of relations.

25. Authorization in SQL • The SQL language offers a fairly powerful mechanism for defining authorizations by using privileges.

26. Privileges in SQL • SQL standard includes the privileges: • Delete • Insert • Select • Update • References: permits declaration of foreign keys. • SQL includes commands to grant and revoke privileges.

27. Privileges in SQL • EX1: grant <privilege list> on <relation or view name> to <user> • EX2: grant update (amount) on loan to U1, U3, U4

28. Privilege to Grant Privileges • By default, a user granted privileges is not allowed to grant those privileges to other users. • To allow this, we append the term “with grant option” clause to the appropriate grant command. • EX1: grant select on branch to U1with grant option

29. Revoking Privileges • To revoke a privilege we use the ‘revoke’ clause, which is used very much like ‘grant’. • EX1: revoke <privilege list> on <relation or view name> from <user list>

30. Integrity: Conclusion • It is essential to ensure that the data in a database is accurate. • It is also important to protect the database from domain and referential integrity violations. • If the data is inaccurate or lacks integrity then the database loses effectiveness!

31. Security: Conclusion • We must also ensure that unauthorized users are prevented from accessing or modifying our database. • To do this, we implement authorization rules for users called privileges.

32. Thank You for your Attention!