Lecture 10 Dr Taysir Hassan Abdel Hamid May

Lecture 10: Dr. Taysir Hassan Abdel Hamid May 10, 2015

Outline • • Data Control Language (DCL) Views JDBC Object-oriented Data Modeling

Data Control Language (DCL)

Sub-sets of SQL ü Data retrieval: SELECT • Data Manipulation Language (DML): INSERT, UPDATE, DELETE ü Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME • Data Control Language (DCL): GRANT, REVOKE 4

Introduction to DB Security • Secrecy: Users shouldn’t be able to see things they are not supposed to. – E. g. , A student can’t see other students’ grades. • Integrity: Users shouldn’t be able to modify things they are not supposed to. – E. g. , Only instructors can assign grades. • Availability: Users should be able to see and modify things they are allowed to.

GRANT Command GRANT privileges ON object TO users [WITH GRANT OPTION] • The following privileges can be specified: – SELECT Can read all columns including those added later via ALTER TABLE command – INSERT(column-name) Can insert tuples with non-null or nondefault values in this column. – INSERT means same right with respect to all columns. – DELETE Can delete tuples. – REFERENCES (column-name) Can define foreign keys (in other tables) that refer to this column. • If you want the recipient(s) to be able to pass the privilege(s) to others add: WITH GRANT OPTION

Grant Example I • Suppose Joe has created the tables – Sailors(sid, sname, rating, age) – Boats(bid, bname, color) – Reserves(sid, bid, day) • Joe now executes the following: GRANT INSERT, DELETE ON Reserves TO Omar WITH GRANT OPTION; • Omar can now insert or delete Reserves rows and authorize someone else to do the same.

Grant Example II • Joe further executes: GRANT SELECT ON Reserves TO Michael; GRANT SELECT ON Sailors TO Michael WITH GRANT OPTION; • Michael can now execute SELECT queries on Sailors and Reserves, and he can pass this privilege to others for Sailors but not for Reserves.

Grant Example V • Suppose now that Joe executes: GRANT SELECT, REFERENCES(bid) ON Boats TO Bill; • Bill can then refer to the bid column of Boats as a foreign key in another table. E. g. But, why the SQL CREATE TABLE Bill. Table ( bid INTEGER, … FOREIGN KEY (bid) REFERENCES Boats ); standard chose to introduce the REFERENCES privilege rather than to simply allow the SELECT privilege to be used when creating a Foreign Key?

Role-Based Authorization • Privileges can also be assigned to roles. • Roles can then be granted to users and to other roles. • Reflects how real organizations work. Example. CREATE ROLE some_role; GRANT SELECT ON Reserves TO some_role; GRANT INSERT ON Sailors TO some_role; GRANT UPDATE ON Boats TO some_role; GRANT some_role TO Michael; GRANT some_role TO Bill;

Revoke Example I REVOKE [GRANT OPTION FOR] privileges ON object FROM users {RESTRICT | CASCADE} Suppose Joe is the creator of Sailors. GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Art) REVOKE SELECT ON Sailors FROM Art CASCADE (executed by Joe)

Revoke Example II • Art loses the SELECT privilege on Sailors. • Then Bob, who received this privilege from Art, and only Art, also loses this privilege. – Bob’s privilege is said to be abandoned • When CASCADE is specified, all abandoned privileges are also revoked – Possibly causing privileges held by other users to become abandoned and thereby revoked recursively. • If the RESTRICT keyword is specified, the command is rejected if revoking privileges causes other privileges becoming abandoned.

Revoke Example III Joe executes: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION Joe executes: GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION Art executes: GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION Joe executes: REVOKE SELECT ON Sailors FROM Art CASCADE • As before, Art loses the SELECT privilege on Sailors. • But what about Bob? • Bob received this privilege from Art, but he also received it independently from Joe. So, he doesn’t lose the privilege.

Revoke Example IV Joe executes: GRANT SELECT ON Sailors TO Art WITH GRANT OPTION REVOKE SELECT ON Sailors FROM Art CASCADE • Since Joe granted the privilege to Art twice and only revoked it once, does Art get to keep the privilege? • As per the SQL, NO. It doesn’t matter how many times we grant a privilege.

Privilege Descriptors • When a GRANT is executed, a privilege descriptor is added to a table of such descriptors maintained by the DBMS. • The privilege descriptor specifies the: – – grantor of the privilege, grantee who receives the privilege, granted privilege grant option • When a user creates a table or view he 'automatically' gets privileges, – A privilege descriptor with system as the grantor is entered into the descriptors table.

Authorization Graphs • Nodes are users. Arcs indicate how privileges are passed. GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Art) GRANT SELECT ON Sailors TO Art WITH GRANT OPTION (executed by Bob) GRANT SELECT ON Sailors TO Cal WITH GRANT OPTION (executed by Joe) GRANT SELECT ON Sailors TO Bob WITH GRANT OPTION (executed by Cal)

Views in SQL - A view is a “virtual” table that is derived from other tables - Allows for limited update operations (since the table may not physically be stored) - Allows full query operations - A convenience for defining complex operations once and reusing the definition - Can also be used as a security mechanism

Specification of Views SQL command: CREATE VIEW - a virtual table (view) name - a possible list of attribute names (for example, when arithmetic operations are specified or when we want the names to be different from the attributes in the base relations) - a query to specify the view contents

SQL Views: An Example - Specify a virtual DEPT_INFO table to summarize departmental information - Makes it easier to query without having to specify the aggregate functions, GROUP BY, and HAVING CREATE VIEW DEPT_INFO(DNO, NO_EMPS, TOTAL_SAL) AS SELECT DNO, COUNT(*), SUM(SALARY) FROM EMPLOYEE GROUP BY DNO;

Querying the View - We can specify SQL retrieval queries on a view table, same as on a base table: SELECT DNO FROM DEPT_INFO WHERE NO_OF_EMPS > 100; - Can also specify joins and other retrieval operations on the view

SQL Views: Another Example - Specify a virtual WORKS_ON table (called WORKS_ON_NEW), with EMPLOYEE and PROJECT names (instead of numbers) - This makes it easier to query by names without having to specify the two join conditions CREATE VIEW WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME;

Querying a View (cont. ) We can specify SQL retrieval queries on a view table, same as on a base table: SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME=‘Research’; When no longer needed, a view can be dropped: DROP WORKS_ON_NEW;

Schema modification in SQL - There are two main commands for modifying schema constructs - DROP statement can remove named schema constructs, such as tables, constraints, assertions, views, and even schemas - ALTER statement can be used to change a table by adding or dropping of attributes and table constraints

Example: DROP TABLE • Used to remove a relation (base table) and its definition • The relation can no longer be used in queries, updates, or any other commands since its description no longer exists • Example: DROP TABLE DEPENDENT;

Example: DROP TABLE (cont. ) • If the table being dropped is referenced from other tables, it cannot be dropped an error is generated • By adding CASCADE, all references to the table are automatically removed • Example: DROP TABLE DEPENDENT CASCADE;

Example: ALTER TABLE • Can be used to add or drop an attribute from a base relation – Suppose we want to remove the attribute BDATE from the EMPLOYEE table • Example: ALTER TABLE EMPLOYEE DROP BDATE ; • If the attribute is referenced from another table, an error is generated unless CASCADE is used • ALTER TABLE EMPLOYEE DROP BDATE CASCADE;

Example: ALTER TABLE (cont. ) • Suppose we want to add an attribute JOB – Will have NULLs (or some default) in all the tuples after command is executed; hence, NOT NULL not allowed for new JOB attribute • Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); • The database users must enter values for the new attribute JOB for each EMPLOYEE tuple. – This can be done using the UPDATE command.

JDBC • JDBC is an alternative to ODBC and ADO that provides database access to programs written in Java. • JDBC is not an acronym — it doesn’t stand for anything! • JDBC drivers are available for most DBMS products: – http: //java. sun. com/products/jdbc DAVID M. KROENKE’S 14 -28

JDBC Driver Types DAVID M. KROENKE’S 14 -29

JDBC Components DAVID M. KROENKE’S 14 -30

Using JDBC 1. Load the driver: – The driver class libraries need to be in the CLASSPATH for the Java compiler and for the Java virtual machine. – The most reliable way to load the driver into the program is: Class. for. Name(string). new. Instance(); 2. Establish a connection to the database: – A connection string includes the literal jdbc: , followed by the name of the driver and a URL to the database. Connection conn = Driver. Manager. get. Connection(string); 14 -31

Using JDBC (Continued) 3. Create a statement: Statement stmt = conn. create. Statement(); 4. Process a the statement: Example statements: Result. Set rs = stmt. execute. Query(querystring); int result = stmt. execute. Update(updatestring); Result. Set. Meta. Data rs. Meta = rs. get. Meta. Data(); • Both compiled queries and stored procedures can be processed via JDBC using Prepared. Statement and Callable. Statement objects. 14 -32

Import java. sql. *; Public class type_one { public static void main (String[] args) { try { class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”)//load driver Connection con=Driver. Manager. get. Connection(“jdbc: odbc: HOD_DATA ”) //create connection with datasource Statement s = con. create. Statement(); //create statement

String query = “select * from Data”; //create query S. execute(query); Resultset rs = s. get. Result. Set(); //return data from state While (rs. next() ) //retrieve data from Result. Set { System. out. println(“serial number “+rs. get. String(1)); System. out. println(“, name “+rs. get. String(2)); System. out. println(“ city “+rs. get. String(3)); System. out. println(“ and Age “+rs. get. String(4)); } s. close(); con. close(); } catch (Exception e) { System. out. println(“Exception”+e); }}}

Object-oriented data modeling

Key Definitions • Object-oriented techniques view a system as a collection of self-contained objects which include both data and processes. • The Unified Modeling Language (UML) – the object modeling standard – adds a variety of techniques to the field of system development.

Object Concepts • An object is a person, place, event, or thing about which we want to capture information. • Each object has properties (or attributes). • The state of an object is defined by the value of its properties and relations with other objects at a point in time. • Objects have behaviors -- things that they can do -- which are described by methods (or operations). • Objects do not use primary or foreign keys, instead each instance is assigned a unique identifier (UID) when it is created.

Classes and Objects 15 - 38

Class • A class is a general template we use to define and create specific instances or objects. 15 - 39

Object • An object is an instantiation of a class. • An object is a person, place, event, or thing about which we want to capture information. 15 - 40

Messages and Methods • Messages are information sent to objects to trigger methods 15 - 41

Class Hierarchy 15 - 42

Inheritance Power. Point Presentation for 15 - 43

Unified Modeling Language – UML (Version 2) • Defines a set of fourteen object diagramming techniques • The key building block is the use case • Diagrams are tightly integrated syntactically and conceptually to represent an integrated whole • Application of UML can vary among organizations 15 - 44

UML 2. 0 Diagram Summary 15 - 45

Integration of four UML Diagrams 15 - 46

USE CASE DIAGRAM 15 - 47

Use Case Diagram Concepts • Summarizes all use cases (for the part of the system being modeled) together in one picture • Shows the associations between actors and use cases 15 - 48

Use Case Diagram for Appointment System Power. Point Presentation for 15 - 49

Syntax for Use Case Diagram 15 - 50

Use Case Diagram for Specialized Actor 15 - 51

Steps in Creating the Use Case Diagram 1. Identify Use Cases 2. Draw the system boundary 3. Place Use Cases on the diagram Group Use Cases into packages Add special Use Case associations 4. Identify the actors 5. Add associations 15 - 52

CLASS DIAGRAM 15 - 53

Elements of a Class Diagram • A static model that shows the classes and relationships among classes that remain constant in the system over time • Resembles the ERD, but depicts classes which include both behaviors and states, while entities in the ERD include only attributes • Scope not system wide, but pertaining to a single Use Case 15 - 54

Class Diagram for Manage Appointment 15 - 55

Class Diagram Syntax 15 - 56

Operation Types • Similar to relationships in ERDs • Multiplicity shows how an instance of an object can be associated with other instances 15 - 57

Multiplicity 15 - 58

Steps in Creating a Class Diagram 1. Identify classes 2. Identify attributes and operations 3. Draw associations between classes 15 - 59

Initial Attributes for Class Diagrams 15 - 60

Revised Attributes and Associations 15 - 61

Final Class Diagram 15 - 62

Your final exam • 6 questions and you will answer FIVE ONLY • ERD, EER, schema, SQL, Normalization, RA, security

Grading Scheme: Total 100 Final Exam (50 points): Year Work: (30 points) • Midterm Exam: 15 points • Attendance: 3 • Assignments & quizzes: 12 Lab exam: (20 points) Project: 10 points Lab final: 10 points Good Luck