Unit-5

Object-Relational and 
Extended-Relational Systems
SQL’s Object-Relational Features
•	SQL was specified in 1970s 
•	SQL was enhanced substantially in 1989 and 1992
•	A new standard called SQL3 added object-oriented features
•	A subset of SQL3 standard, now known as SQL-99 has been approved

Component of the SQL Standard
•	SQL/Framework, SQL/Foundation, SQL/Bindings, SQL/Object
•	New parts addressing temporal, transaction aspects of SQL
•	SQL/CLI (Call Level Interface)
•	SQL/PSM (Persistent Stored Modules)
SQL/Foundation	
•	New types
•	New predicates
•	Relational operators
•	Rules and triggers
•	User defined types
•	Transaction capabilities
•	Stored routines
SQL/CLI	
•	SQL/CLI stands for SQL Call Level Interface
•	SQL/CLI provides rules that allow execution of application code without providing source code 
•	Avoids the need for preprocessing
•	Contains about 50 routines for tasks such as connection to the SQL server

SQL/PSM
•	PSM = Persistent Stored Modules
•	Specifies facilities for partitioning an application between a client and a server
•	Enhances performance by minimizing network traffic
•	SQL Bindings included Embedded SQL
•	SQL/Temporal deals with historical data
Object-Relational Support in SQL-99
•	Type constructors to specify complex objects
•	Mechanism to specify object-identity
•	Mechanism for encapsulation of operations
•	Mechanism to support inheritance
•	I.e., specify specialization and generalization 
Type Constructors 
•	Two types: row and array 
•	Known as user-defined types (UDTs)
•	Syntax for a row type
•	CREATE TYPE row_type_name AS [ROW] (<component decln>)
•	An example:
	CREATE TYPE Addr_type AS (
			street VARCHAR (45),
			city VARCHAR (25),
			zip CHAR (5));
Type Constructors 
•	An array type is specified for an attribute whose value will be a collection
•	Example:
	CREATE TYPE Comp_type AS (
			comp_name VARCHAR (2).
			location VARCHAR (20) ARRAY [10] );
•	Dot notation is used to refer to components
•	E.g., comp1.comp_name is the comp_name part of comp1 (of type Comp_type)

Object-Identifiers Using References
•	A user-defined type can also be used to specify the row types of a table:
	CREATE TABLE Company OF Comp_type
 		(REF IS comp_id SYSTEM GENERATED,
		PRIMARY KEY (comp_name));
Attributes as References
•	A component attribute of one tuple may be a reference:
CREATE TYPE Employment_type AS (
	employee REF (Emp_type) SCOPE (Employee),
	company REF (Comp_type) SCOPE (Company));
•	Keyword SCOPE specifies the table whose tuples can be referenced by a reference attribute via the dereferencing notation ->
•	E.g., e.company->comp_name

Encapsulation of Operations
•	A construct similar to the class definition
•	Users can create a named user-defined type with its own methods in addition to attributes:

Method Syntax
•	Syntax: 
METHOD <name> (<arg-list>) RETURNS <type>;
•	An example
	CREATE TYPE Addr_type AS (
		street VARCHAR (45),
		city VARCHAR (25),
		zip CHAR (5)
	)
	METHOD apt_no ( ) RETURNS CHAR(8);

Inheritance in SQL
•	Inheritance is specified via the UNDER keyword
•	Example
	CREATE TYPE Manager_type UNDER Emp_type AS (dept_managed CHAR (20));
•	Manager_type inherits all features of Emp_type
•	and it has an additional attribute called dept_managed

Object-Relational Features of Oracle
•	VARRAY for representing multi-valued attributes
	CREATE TYPE phone_type 
		AS OBJECT (phone_number CHAR (10));
	CREATE TYPE phone_list_type 
		AS VARRAY (5) of phone_type;
	CREATE TYPE customer_type AS
	OBJECT (customer_name(VARCHAR (20), phone_numbers phone_list_type);

	CREATE TABLE customer of customer_type;
	SELECT customer_name phone_numbers FROM customer;

Managing Large Objects
•	Oracle can store extremely large objects:
•	RBLOB (binary large object)
•	CLOB (character large object)
•	BFILE (binary file stored outside the database)
•	NCLOB (fixed-width multibyte CLOB)

Nested Relational Model
•	Nested relational mode:
•	Removes the restriction of the first normal form (1NF)
•	No commercial database supports a nested relational model
•	Visual representation:

Attributes of Nested Relations
•	Simple value attributes
•	Multi-valued simple attributes
•	Multi-valued composite attributes
•	Single-valued composite attributes
Manipulating Nested Relations
•	Two operations for converting between nested and flat relations:
•	NEST
•	UNNEST

Example of NEST
•	To nest un-nested attributes:

EMP_PROJ_FLAT ←
	П SSN, ENAME, PNUMBER, HOURS (EMP_PRO)
EMP_PROJ_NESTED ←
	NEST PROJ = (PNUMBER,HOURS) (EMP_PROJ_FLAT)

•	Nested relation PROJS within EMP_PROJ_NESTED groups together the tuples with the same value for the attributes that are not specified in the NEST operation
Example of UNNEST
•	UNNEST operation is the inverse of NEST; thus we can recover EMP_PROJ_FLAT:
	EMP_PROJ_FLAT ← UNNEST PROJS = (PNUMBER,HOURS) (EMP_PROJ_NESTED) 
Database Security and Authorization

Introduction to Database Security Issues
•	Types of Security
•	Legal and ethical issues
•	Policy issues
•	System-related issues
•	The need to identify multiple security levels 

•	Threats to databases
•	Loss of integrity
•	Loss of availability
•	Loss of confidentiality	

•	To protect databases against these types of threats four kinds of countermeasures can be implemented:
•	Access control
•	Inference control
•	Flow control
•	Encryption
 
•	A DBMS typically includes a database security and authorization subsystem that is responsible for ensuring the security portions of a database against unauthorized access.

•	Two types of database security mechanisms:
•	Discretionary security mechanisms
•	Mandatory security mechanisms

•	The security mechanism of a DBMS must include provisions for restricting access to the database as a whole
•	This function is called access control and is handled by creating user accounts and passwords to control login process by the DBMS.

•	The security problem associated with databases is that of controlling the access to a statistical database, which is used to provide statistical information or summaries of values based on various criteria.
•	The countermeasures to statistical database security problem is called inference control measures.

•	Another security is that of flow control, which prevents information from flowing in such a way that it reaches unauthorized users.
•	Channels that are pathways for information to flow implicitly in ways that violate the security policy of an organization are called covert channels.
•	A final security issue is data encryption, which is used to protect sensitive data (such as credit card numbers) that is being transmitted via some type communication network.
•	The data is encoded using some encoding algorithm.
•	An unauthorized user who access encoded data will have difficulty deciphering it, but authorized users are given decoding or decrypting algorithms (or keys) to decipher data.

Database Security and the DBA 
•	The database administrator (DBA) is the central authority for managing a database system.
•	The DBA’s responsibilities include
•	granting privileges to users who need to use the system
•	classifying users and data in accordance with the policy of the organization
•	The DBA is responsible for the overall security of the database system.
•	The DBA has a DBA account in the DBMS
•	Sometimes these are called a system or superuser account
•	These accounts provide powerful capabilities such as:
•	1. Account creation
•	2. Privilege granting
•	3. Privilege revocation
•	4. Security level assignment
•	Action 1 is access control, whereas 2 and 3 are discretionary and 4 is used to control mandatory authorization

Access Protection, User Accounts, and Database Audits
•	Whenever a person or group of persons need to access a database system, the individual or group must first apply for a user account.
•	The DBA will then create a new account id and password for the user if he/she deems there is a legitimate need to access the database
•	The user must log in to the DBMS by entering account id and password whenever database access is needed.
•	The database system must also keep track of all operations on the database that are applied by a certain user throughout each login session.
•	If any tampering with the database is suspected, a database audit is performed
•	A database audit consists of reviewing the log to examine all accesses and operations applied to the database during a certain time period.
•	A database log that is used mainly for security purposes is sometimes called an audit trail.

Discretionary Access Control Based on Granting and Revoking Privileges
•	The typical method of enforcing discretionary access control in a database system is based on the granting and revoking privileges.
Types of Discretionary Privileges
•	The account level:
•	At this level, the DBA specifies the particular privileges that each account holds independently of the relations in the database.
•	The relation level (or table level):
•	At this level, the DBA can control the privilege to access each individual relation or view in the database.
Types of Discretionary Privileges
•	The privileges at the account level apply to the capabilities provided to the account itself and can include
•	the CREATE SCHEMA or CREATE TABLE privilege, to create a schema or base relation;
•	the CREATE VIEW privilege;
•	the ALTER privilege, to apply schema changes such adding or removing attributes from relations;
•	the DROP privilege, to delete relations or views;
•	the MODIFY privilege, to insert, delete, or update tuples;
•	and the SELECT privilege, to retrieve information from the database by using a SELECT query.
•	The second level of privileges applies to the relation level
•	This includes base relations and virtual (view) relations.
•	The granting and revoking of privileges generally follow an authorization model for discretionary privileges known as the access matrix model where 
•	The rows of a matrix M represents subjects (users, accounts, programs)
•	The columns represent objects (relations, records, columns, views, operations).
•	Each position M(i,j) in the matrix represents the types of privileges (read, write, update) that subject i holds on object j.
•	In SQL the following types of privileges can be granted on each individual relation R:
•	SELECT (retrieval or read) privilege on R:
•	Gives the account retrieval privilege.
•	In SQL this gives the account the privilege to use the SELECT statement to retrieve tuples from R.
•	MODIFY privileges on R:
•	This gives the account the capability to modify tuples of R.
•	In SQL this privilege is further divided into UPDATE, DELETE, and INSERT privileges to apply the corresponding SQL command to R.
•	In addition, both the INSERT and UPDATE privileges can specify that only certain attributes can be updated by the account.
 Revoking Privileges
•	In some cases it is desirable to grant a privilege to a user temporarily. For example, 
•	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.
•	Hence, a mechanism for revoking privileges is needed. In SQL, a REVOKE command is included for the purpose of canceling privileges.
 Propagation of Privileges using the GRANT OPTION
•	Whenever the owner A of a relation R grants a privilege on R to another account B, privilege can be given to B with or without the GRANT OPTION.
•	If the GRANT OPTION is given, this means that B can also grant that privilege on R to other accounts. 

 Mandatory Access Control and Role-Based Access Control for Multilevel Security
•	The discretionary access control techniques of granting and revoking privileges on relations has traditionally been the main security mechanism for relational database systems.
•	This is an all-or-nothing method:
•	A user either has or does not have a certain privilege.
•	In many applications, and additional security policy is needed that classifies data and users based on security classes. 
•	This approach as mandatory access control, would typically be combined with the discretionary access control mechanisms.
 Mandatory Access Control and Role-Based Access Control for Multilevel Security
Typical security classes are top secret (TS), secret (S), confidential (C), and unclassified (U), where TS is the highest level and U the lowest: TS ≥ S ≥ C ≥ U
•	The commonly used model for multilevel security, known as the Bell-LaPadula model, classifies each subject (user, account, program) and object (relation, tuple, column, view, operation) into one of the security classifications, T, S, C, or U:
•	Clearance (classification) of a subject S as class(S) and to the classification of an object O as class(O).
Comparing Discretionary Access Control and Mandatory Access Control
•	Discretionary Access Control (DAC) policies are characterized by a high degree of flexibility, which makes them suitable for a large variety of application domains.
•	The main drawback of DAC models is their vulnerability to malicious attacks, such as Trojan horses embedded in application programs.
•	By contrast, mandatory policies ensure a high degree of protection in a way, they prevent any illegal flow of information.
•	Mandatory policies have the drawback of being too rigid and they are only applicable in limited environments.
•	In many practical situations, discretionary policies are preferred because they offer a better trade-off between security and applicability.
Role-Based Access Control
•	Role-based access control (RBAC) emerged rapidly in the 1990s as a proven technology for managing and enforcing security in large-scale enterprise wide systems.
•	Its basic notion is that permissions are associated with roles, and users are assigned to appropriate roles.
•	Roles can be created using the CREATE ROLE and DESTROY ROLE commands. 
•	The GRANT and REVOKE commands discussed under DAC can then be used to assign and revoke privileges from roles.
•	RBAC appears to be a viable alternative to traditional discretionary and mandatory access controls; it ensures that only authorized users are given access to certain data or resources.
•	Many DBMSs have allowed the concept of roles, where privileges can be assigned to roles.
•	Another important consideration in RBAC systems is the possible temporal constraints that may exist on roles, such as time and duration of role activations, and timed triggering of a role by an activation of  another role.
•	Using an RBAC model is highly desirable goal for addressing the key security requirements of Web-based applications.
•	In contrast, discretionary access control (DAC) and mandatory access control (MAC) models lack capabilities needed to support the security requirements emerging enterprises and Web-based applications.
Introduction to Statistical Database Security
•	Statistical databases are used mainly to produce statistics on various populations.
•	The database may contain confidential data on individuals, which should be protected from user access.
•	Users are permitted to retrieve statistical information on the populations, such as averages, sums, counts, maximums, minimums, and standard deviations.

 Introduction to Flow Control
•	Flow control regulates the distribution or flow of information among accessible objects.
•	A flow between object X and object Y occurs when a program reads values from X and writes values into Y.
•	Flow controls check that information contained in some objects does not flow explicitly or implicitly into less protected objects.
•	A flow policy specifies the channels along which information is allowed to move.
•	The simplest flow policy specifies just two classes of information:
•	confidential (C)  and nonconfidential (N)
•	and allows all flows except those from class C to class N.
Covert Channels
•	A covert channel allows a transfer of information that violates the security or the policy.
•	A covert channel allows information to pass from a higher classification level to a lower classification level through improper means.
Covert Channels
•	Covert channels can be classified into two broad categories:
•	Storage channels do not require any temporal synchronization, in that information is conveyed by accessing system information or what is otherwise inaccessible to the user.
•	Timing channel allow the information to be conveyed by the timing of events or processes.

Encryption and Public Key Infrastructures
•	Encryption is a means of maintaining secure data in an insecure environment.
•	Encryption consists of applying an encryption algorithm to data using some pre specified encryption key.
•	The resulting data has to be decrypted using a decryption key to recover the original data.
The Data and Advanced Encryption Standards
•	The Data Encryption Standard (DES) is a system developed by the U.S. government for use by the general public.
•	DES can provide end-to-end encryption on the channel between the sender A and receiver B.
•	DES algorithm is a careful and complex combination of two of the fundamental building blocks of encryption:
•	substitution and permutation (transposition).
•	The DES algorithm derives its strength from repeated application of these two techniques for a total of 16 cycles.
•	Plaintext (the original form of the message) is encrypted as blocks of 64 bits.
•	After questioning the adequacy of DES, the National Institute of Standards (NIST) introduced the Advanced Encryption Standards (AES).
•	This algorithm has a block size of 128 bits and thus takes longer time to crack.
 Public Key Encryption
•	The two keys used for public key encryption are referred to as the public key and the private key.
•	the private key is kept secret, but it is referred to as private key rather than a secret key (the word used in conventional encryption to avoid confusion with conventional encryption).
•	A public key encryption scheme, or infrastructure, has six ingredients:
•	Plaintext: This is the data or readable message that is fed into the algorithm as input.
•	Encryption algorithm: The encryption algorithm performs various transformations on the plaintext.
•	Public and private keys: These are pair of keys that have been selected so that if one is used for encryption, the other is used for decryption. 
•	one key for encryption and a different but related key for decryption
•	The essential steps are as follows:
•	Each user generates a pair of keys to be used for the encryption and decryption of messages.
•	Each user places one of the two keys in a public register or other accessible file. This is the public key. The companion key is kept private (private key).
•	If a sender wishes to send a private message to a receiver, the sender encrypts the message using the receiver’s public key.
•	When the receiver receives the message, he or she decrypts it using the receiver’s private key.
•	No other recipient can decrypt the message because only the receiver knows his or her private key.

 Digital Signatures
•	A digital signature is an example of using encryption techniques to provide authentication services in e-commerce applications.
•	A digital signature is a means of associating a mark unique to an individual with a body of text.
•	The mark should be unforgettable, meaning that others should be able to check that the signature does come from the originator.
•	A digital signature consists of a string of symbols. 
•	Signature must be different for each use. 
•	This can be achieved by making each digital signature a function of the message that it is signing, together with a time stamp.
•	Public key techniques are the means creating digital signatures.