System Aspects of SQL

(1)

System Aspects of SQL

SQL Environment User Access Control

SQL in Programming Environment Embedded SQL

SQL and Java

Transactions (Programmers View)

A. Hinze, Freie Universität Berlin, SS 2002, Einführung in Datenbanken+Datenbanken für die Bioinfor

SQL Environment: Introduction

SQL server

- Supports operations on database elements - Typically runs on large host machine

SQL client

- Supports user connections to server - Runs on (different) host machine

Connection

- Channel between client and server

(2)

7.3

A. Hinze, Freie Universität Berlin, SS 2002, Einführung in Datenbanken+Datenbanken für die Bioinformatik

SQL Environment: Introduction

Session

- All SQL operations performed while connection open - Current catalog, current schema , authorized user

Application

- Module: application program - SQL agent: executionof module

SQL Client SQL Server Connection

Session SQL Environment

SQL agent

7.4

SQL Environment: Module Types

Generic SQL Interface:

- Module: each query or statement

Embedded SQL:

- SQL statements within host-language program - SQL statements pre-processed to function calls - Calls executed at run-time

True modules:

- Collection of stored procedures - Host language code, SQL code

(3)

7.5

SQL Environment: Privileges

User

- Outside schema, handling implementation dependent - Identification by Authorization ID (user name)

Role

- Defines user group

- Inside schema, handling via SQL statements - Identification by Authorization ID (role name) - All users: special role PUBLIC

- Examples:

CREATE ROLE Customer;

CREATE ROLE Secretary WITH ADMIN Klaus;

CREATE ROLE Movie_staff;

CREATE ROLE Shop_owner;

User Access Control: Introduction

Secrecy:

- Users should not be able to see things they are not supposed to.

- e.g., A student can’t see other students’ grades.

Integrity:

- Users should not 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.

(4)

7.7

User Access Control: Introduction

Security policy specifies authorization

Security mechanism enforces a security policy Two mechanisms at DBMS level

Discretionary access control

- Concept of privileges for objects (tables and views) - Mechanisms for giving and revoking users privileges

Mandatory access control

- System-wide policies for DBS - DB object have security class

- Rules on security classes govern access

- Used for specialized (e.g., military) applications

7.8

User Access Control: Privileges

Privileges

- Right to perform SQL statement type on objects - Assigned to roles (authorization IDs)

- Creator of object: all privileges

- DBMS: management of privileges and access rights

Privilege types:

- SELECT on table or view - INSERT on table or view - DELETE on table or view - UPDATE on table or view

- REFERENCES: right to refer to relation in constraint - USAGE: (SQL-92) right to use specified domain - ALL PRIVILEGES: short form for all privileges

(5)

7.9

User Access Control: Privileges

Example

Privileges:

- SELECT on Tape - SELECT on Format - INSERT on Format

INSERT INTO Format(name) SELECT format FROM Tape t

WHERE t.format NOT IN (SELECT name FROM format);

User Access Control: Privileges

Grant privilege

- GRANT OPTION: Right to pass privilege on to other users - Only owner can execute CREATE, ALTER, and DROP

GRANT <privileges> ON <object>

TO <users> [WITH GRANT OPTION]

GRANT <privileges>

ON <tablename(<attributenames>)>

TO <users> [WITH GRANT OPTION]

Privilege to SELECT particular columns in a table

(6)

7.11

User Access Control: Privileges

Examples:

GRANT INSERT, SELECT ON Movie TO Klaus Klaus can query Movie or insert tuples into it.

GRANT DELETE ON Movie TO shop_owner WITH GRANT OPTION Anna can delete tuples, and also authorize others to do so

GRANT UPDATE (pricePDay) ON Movie TO movie_staff Staff can update (only) the price field of Movie tuples GRANT SELECT ON MovieView TO Customers

This does NOT allow the customers to query Movie directly!

7.12

User Access Control: Privileges on views

Creator has privilege on view if privilege on all underlying tables

Creator loses SELECT privilege on underlying table

⇒ view is dropped

Creator loses a privilege on underlying table ⇒ creator loses privilege on view

Creator loses a privilege held with grant option on

underlying table ⇒ users who were granted that

privilege on the view lose privilege on view

(7)

7.13

User Access Control: Privileges

Revoke privilege

- RESTRICT: only revoke if non of the privileges have been granted by theseusers

- Privilege given from different users – must be revoked from all users to loose privilege

REVOKE <privileges>

ON <object>

FROM <users> RESTRICT

Core SQL:1999

User Access Control: Examples

Owner: GRANT Update ON Movie TO Klaus;

Owner: GRANT Update ON Movie TO Anna;

owner

Movie Priv

Klaus Priv

Anna Priv

Owner: REVOKE Update ON Movie FROM Klaus RESTRICT;

owner

Movie Priv

Klaus Anna

Priv

(8)

7.15

User Access Control: Examples

Owner: GRANT Update ON Movie TO Klaus WITH GRANT OPTION;

Klaus: GRANT Update ON Movie TO Anna;

owner

Movie Priv

Klaus Priv

Anna Priv

Owner: REVOKE Update ON Movie FROM Klaus RESTRICT;

owner

Movie Priv

Klaus Anna

Grant

Priv Priv

Grant Command fails !

7.16

User Access Control: Privileges

Revoke privilege

- CASCADE: revoke from all users that have been granted the privilege by these users

- RESTRICT: only revoke if non of the privileges have been granted by thisuser

REVOKE [GRANT OPTION FOR] <privileges>

ON <object>

FROM <users> {RESTRICT | CASCADE}

enhanced SQL:1999

(9)

7.17

Grant

User Access Control: Examples

owner

Movie Priv

Klaus Priv

Anna Priv

Owner: REVOKE Update ON Movie FROM Klaus CASCADE;

owner

Movie Priv

Klaus Anna

Grant

User Access Control: Examples

owner

Movie Priv

Klaus Priv

Anna Priv

Owner: REVOKE GRANT OPTION FOR Update ON Movie FROM Klaus CASCADE;

owner

Movie Priv

Klaus Anna

Priv

(10)

7.19

User Access Control: Examples

Owner: REVOKE GRANT OPTION FOR Update ON Movie FROM Klaus CASCADE;

owner

Movie Priv

Klaus Anna

Priv

Owner: GRANT Update ON Movie TO Klaus WITH GRAND OPTION;

Owner: GRANT Update ON Movie TO Anna;

owner

Movie Priv

Klaus Priv

Anna Priv Klaus: GRANT Update ON Movie TO Anna;

7.20

User Access Control: Object owners

Schema owner:

- Right for create, drop, alter (no privilege, not grantable) - All privileges on schema objects

Object creator/owner:

- Create statement: current authorizationID is owner - Enhanced SQL:1999 : owner needn't be creator

Current user privileges in Oracle:

SQL> SELECT * FROM session_privs;

PRIVILEGE

--- CREATE SESSION

ALTER SESSION CREATE TABLE ....

(11)

7.21

SQL in Programs: Introduction

SQL

- Sub-language for data access - Efficient database operations

Host language:

- Control structures - Complex computations

- User interface: output formatting, forms - Transactions: DB interactions as unit of work

SQL and host language needed

SQL in Programs: Impedance Mismatch

Impedance Mismatch:

differing data model of SQL and host language

Problems:

- Set oriented operations vs manipulation of individuals - Interconnection of program variables and SQL statements - Compilation time of embedded SQL-statements

(12)

7.23

SQL in Programs: Program/DBS Communication

1. Fourth Generation Languages (4GL)

- Decreasing importance

2. Module Languages

- Standardized in SQL:1999

3. Call level interface

- Most important approach - Standardized in SQL:1999

4. Component architectures

- Hiding the details of DB interaction - Example: Enterprise Java Beans (EJB)

7.24

SQL in Programs: 1. 4GL

Underlying assumption:

- application programs algorithmically simple - sophisticated output formatting needed - difficult to switch between different DBS

Technical concept:

Decreasing importance

Client workstation (presentation,

requests, GUI) Database server Proprietary protocol

(13)

7.25

SQL in Programs: 2. Modules

Parameterized modules of SQL statements Standardized in SQL:1999

Compiled for a particular language

Linked to application program (statically?) Language Examples: COBOL, C, ADA, ...

Disadvantages:

- SQL code hidden in application and vice versa - Not widely used

Used in stored procedures (e.g., Oracle PL/SQL) Executed under control of DBS

SQL in Programs: 2. Modules (cont)

Example:

MODULE demo NAMES are ascii LANGUAGE FORTRAN

SCHEMA movie_db AUTHORIZATION ...

PROCEDURE discount_op

(SQLSTATE, :title VARCHAR(40), :discount DECIMAL(3,2)) UPDATE Movie M

SET pricePday = pricePday - :discount WHERE M.title = :title;

PROCEDURE customerState

(SQLSTATE, :customer INTEGER) SELECT movie_id,tape_id,from_date FROM Tape T, Rental R

WHERE R.member = customer AND R.tape_id = T.id;

Program Language variables Returned state value

(14)

7.27

SQL in Programs: 3. Call level interface

Interface in standard programming languages Proprietary library routines, API

Embedded C / Java / ..

Standardized language extensions

Standardized API

- Open Database connection (ODBC), - Java Database Connectivity (JDBC)

7.28

SQL in Programs: 3. Call level interface

Language/DBS specific library of procedures

Example: MySQL C API

- Buffer for transferring commands and results - API data types, e.g.,

- API functions, e.g.,

MYSQL handle for db connections MYSQL_RES result set structure

mysql_real_query()

mysql_real_query(MYSQL *mysql, const char *query,

unsigned int queryLength)

(15)

7.29

SQL in Programs: Embedded SQL

Direct SQL:

- SQL interpreter accepts and executes SQL commands

SQL in host language:

- Program in programming language (C, Java,…) - Parts of program in SQL statements

- Most implementations: call level interface used - Most popular: Embedded C (Oracle: PRO*C)

Java support

- SQLJ = Embedded Java

- JDBC = Standardized call interface for Java

SQL in Programs: Embedded SQL

Program with of "native" and SQL-like statements Pre-compiler = Preprocessor creates native code Calls to DBS resources included

Programmer: embedded SQL or function calls

Preprocessor Host language

+ Embedded SQL

Host language +

Function Calls Host language compiler

Object-code program SQL

library

(16)

7.31

SQL in Programs: Static/dynamic embedding

Static embedding:

- SQL commands known in advance

- SQL-compilation and language binding at pre-compile time

Dynamic SQL:

- SQL-String compiled at runtime - variable bindings at runtime

7.32

SQL in Programs: Embedded SQL

Concepts:

- Well defined type mapping (for different languages) - Syntax for embedded SQL statements

- Binding to host language variables

- Exception handling

WHENEVER <condition> <action>

SQLSTATE

EXEC SQL {SELECT title FROM ...}

EXEC SQL {SELECT id FROM Movie

WHERE titel = :titleString};...

(17)

7.33

SQL in Programs: Embedded SQL

SQL / Host Language Interface:

- Embedded SQL-statement:

- Shared variables:

- Exception handling:

EXEC SQL <sql statement>

:<variableName> (access in SQL)

<variableName> (access in host language)

SQLSTATE (SQL function execution status) e.g., 00000 - no problem

02000 – answer tuple not found

SQL in Programs: Embedded SQL

Shared variable declaration Syntax:

- Declaration in host language - Use variable types in common

Example:

EXEC SQL BEGIN DECLARE SECTION;

…

EXEC SQL END DECLARE SECTION;

integer movie_number;

integer tape_number;

(18)

7.35

SQL in Programs: Embedded SQL

Single row results:

- direct insert into variable

Syntax:

Multiple row results:

- Use of cursors on result set

EXEC SQL SELECT <attributeName>

INTO :<sharedVariable>

FROM <tableNames>

WHERE <condition>

7.36

SQL in Programs: Cursor concept

Cursor:

- Name of SQL statement and

- Handle for processing the result set record by record

Defined at runtime

Opened at runtime (SQL-statement executed)

Used in most language embeddings of SQL

- e.g., ESQL-C, PL/SQL, JDBC

Important concept

(19)

7.37

SQL in Programs: Cursor concept

No binding of result attributes to variables Allows traversal of result set row by row

1. Cursor declaration 2. Cursor initialisation 3. Fetch tuples

4. Close cursor

OPEN FETCH EMPTY? CLOSE

DECLARE yes

no

SQL in Programs: Cursor concept

Cursor declaration:

Cursor initialisation:

- binds input variables - executes query

- puts first results into communication area

- positions cursor before first row of the result set EXEC SQL DECLARE <cursorName> CURSOR

FOR <query>

EXEC SQL OPEN <cursorName>;

(20)

7.39

SQL in Programs: Cursor concept

Fetch tuples:

- Puts next results into communication area

- Positions cursor before before next row of the result set - Assigns tuple to shared variables

- Sets SQLSTATE

EXEC SQL FETCH <cursorName>

INTO :<shared variable>;

7.40

SQL in Programs: Example

#include <stdio.h>

/* declare host variables */

char userid[12] = "ABEL/xyz";

char movie_name[10];

int movie_number;

int tape_number;

char temp[32];

void sql_error();

/* include the SQL Communication Area */

#include <sqlca.h>

(21)

7.41

SQL in Programs: Example

/* main program */

main()

{ movie_number = 200;

/* handle errors */

EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");

/* connect to Oracle */

EXEC SQL CONNECT :userid;

printf("Connected.\n");

SQL in Programs: Example

/* declare a cursor */

EXEC SQL DECLARE movie_cursor CURSOR FOR

SELECT m.title

FROM movie m, tape t

WHERE t.id = :tape_number AND t.movie_id = m.id;

/* get user data */

printf(“Tape number? ");

gets(temp);

tape_number = atoi(temp);

(22)

7.43

SQL in Programs: Example

/* open the cursor and identify the result set */

EXEC SQL OPEN movie_cursor;

…

/* fetch and process data in a loop exit when no more data */

EXEC SQL WHENEVER NOT FOUND DO break;

while (1){

EXEC SQL FETCH movie_cursor INTO :movie_name; …

}

7.44

SQL in Programs: Example

/* close cursor before another SQL statement is executed */

EXEC SQL CLOSE movie_cursor;

EXEC SQL COMMIT WORK RELEASE;

exit(0);

}

(23)

7.45

SQL in Programs: Positioned Update

Step through set of rows and update or delete Syntax:

Example:

EXEC SQL DECLARE <cursorName> CURSOR FOR <query>

FOR UPDATE ON <attribute>;

… WHERE CURRENT OF <cursorName>…

EXEC SQL DECLARE myCurs CURSOR

FOR SELECT id,length,title FROM Movie FOR UPDATE ON length

EXEC SQL UPDATE Movie

SET lenght = length + 1 WHERE CURRENT OF myCurs;

SQL in Programs: Cursor Options

Ordering tuples

- Use ORDER BYin query

Cursor motion

- SCROLL CURSOR

- Relative to current position: PRIOR/NECT/RELATIVE<nr>

e.g., FETCH <cursorName> PRIOR INTO ...

- Absolute position: first/last/ABSOLUTE<nr>

Limit effect of changes

- Performance: cursor FOR READ ONLY

- Concurrent access: INSENSITIVE CURSOR FOR …

(24)

7.47

SQL in Programs: Cursor sensitivity

Example:

Changes not visible in result set Visible if cursor closed and reopened

EXEC SQL DECLARE myCurs INSENSITIVE CURSOR FOR SELECT id,length,title FROM Movie FOR UPDATE ON length WHERE id >100;

EXEC SQL OPEN...

EXEC SQL FETCH myCurs INTO ...

UPDATE Movie SET lenght = length + 20 WHERE CURRENT OF myCurs;

7.48

SQL in Programs: Dynamic SQL

Statements not known at compile time

- Statements computed by host language - User input of query

Tasks at run-time:

- Pass query string to SQL system - Translate to executable statement - Execute statement

Use ‘Prepared Statements’

(25)

7.49

SQL in Programs: Dynamic SQL

Step 1:

- String: SQL statement

- SQLvariable: assigned SQL statement - Parse and prepare statement for execution

EXEC SQL PREPARE <SQLvariable>

FROM <string>

EXEC SQL EXECUTE <SQLvariable>

Step 2:

- Execute statement SQLvariable

SQL in Programs: Dynamic SQL

Example:

void readQuery(){

char *query;

…

/* prompt user for query allocate space

make :query point to query*/

…

EXEC SQL PREPARE SQLquery FROM :query;

EXEC SQL EXECUTE SQLquery;

}

(26)

7.51

SQL in Programs: Dynamic SQL

Multiple execution:

- Prepare once - Execute many times

Single execution:

- Combination of step 1 an 2

- Example:

EXEC SQL EXECUTE IMMEDIATE <string>

…

EXEC SQL EXECUTE IMMEDIATE :query;

…

7.52

SQL in Programs: SQL & Java

SQLJ

- Embedded SQL for Java - Compiles to JDBC method call

- Defined and implemented by major DBS companies (Oracle in particular)

JDBC

- Java call-level interface (API) for SQL DBS - DB vendor independent

- Supports static and dynamic SQL - Implemented by nearly all DB vendors

(27)

7.53

SQL in Programs: SQLJ

Part 0: SQLJ Embedded SQL

- Mostly reviewed and implemented - Integrated with JDBC API

- Oracle has placed Translator source into public domain

Part 1: SQLJ Stored Procedures and UDFs

- Using Java static methods as SQL stored procedures &

functions

- Leverages JDBC API

Part 2: SQLJ Data Types

- Pure Java Classes as SQL ADTs

- Alternative to SQL:1999 Abstract Data Types

SQL in Programs: SQLJ Example

// Part of a SQLJ program, one method:

public void changeMovie(int movieid, int newTape) {

string mtitle;

int tnumber;

#sql { SELECT m.title, count(t.id) INTO :mtitle, :tnumber FROM movie m, tape t WHERE m.id = :movieid AND m.id = t.movie_id };

if (tnumber < 3)

#sql {INSERT INTO tape VALUES

(:newTape, 'DVD', :movieid)};

}

(28)

7.55

SQL in Programs: SQL & Java

Java in Web context (2 tier architecture):

JDBC Java application

DBMS

Business Logic (application)

Proprietary protocol of DBMS

Database Server

7.56

SQL in Programs: SQL & Java

Java in Web context (3 tier architecture):

JDBC Application server

DBMS Java applet or

WWW Browser GUI

Proprietary protocol of DBMS

Database Server

Business Logic (application) HTTP, RMI, CORBA,…

(29)

7.57

SQL in Programs: JDBC

1. Preparation

2. Load a driver

- many vendor products

- urlJDBC-Driver and host information Class.forName(

"oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:

@<host>:<port>:<db>";

import java.sql.*;

SQL in Programs: JDBC

3. Set up connection database(s)

- Several connections at a time possible

4. Create statement object

- Similar to channel for sending queries to database

Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@<host>:<port>:<db>",

<username>,<password>);

Statement stmt = con.createStatement();

(30)

7.59

SQL in Programs: JDBC

5. Send SQL query string

- results in ResultSet object

6. Process results one after the other

- processed with "hidden cursor"

ResultSet rs = stmt.executeQuery(“<query>" );

while (rs.next()){

for (i = 1; i <= numCols; i++){

if (i > 1) System.out.print(",");

System.out.print(rs.getString(i));

} }

7.60

#import java.io.*;

#import java.sql.*;

#import java.util.*;

...

SQL in Programs: JDBC Example

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@kuh:1521:INTROKUH";

Connection con = DriverManager.getConnection ( url, “user", “passwort");

Protocol Sub-protocol Oracle-spec. Host Port 3. Connect to database

2. Load driver 1. Preparation

(31)

7.61

. . . .

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(

"SELECT id, title FROM movie");

while (rs.next()) {

String n = rs.getInt(“id");

String n = rs.getString(“title");

System.out.println(s + ": " + n);

}

5. execute statement 4. Create SQL-statement

SQL in Programs: JDBC Example

6. Process results

}

SQL in Programs: JDBC variable binding

No explicit cursor

Several methods in JDBC

- e.g.,

Access result data by position or by name

- By position:

- By name:

boolean next(), void close(),

<JavaType> get<JavaType>(), boolean wasNull()

String s = rs.getString(2);

String rs.getString ("b") ;

(32)

7.63

SQL in Programs: JDBC variable binding

Example:

java.sql.Statement stmt = con.createStatement();

ResultSet rs1 = stmt.executeQuery

("SELECT id, title FROM movie");

while (rs1.next()) {

int mid = rs1.getInt(“id");

String mt = rs1.getString(“title");

System.out.println("ROW:" + mid + " " + mt);}

ResultSet rs2 = stmt.executeQuery

("SELECT id, movie_id FROM tape");

while (rs2.next()) {

int tid = rs2.getInt(1);

int tmid = rs2.getInt(2);

System.out.println("ROW:" + tid + " " + tmid);}

7.64

SQL in Programs: Prepared statements

Pass input parameters Use prepared statement

- Statement compiled

- Missing values in query: “?”

Set value:

java.sql.PreparedStatement prepStmt = con.prepareStatement(<query>);

prepStmt.setString(<position>, <value>);

(33)

7.65

SQL in Programs: Prepared statements

String mTitle;

....

java.sql.PreparedStatement prepStmt = con.prepareStatement(

"SELECT count(*) FROM Movie m, Tape t WHERE t.movie_id = m.id AND m.title = ? );

prepStmt.setString(1, mTitle);

ResultSet rs = prepStmt.executeQuery() ; while (rs.next()){

int i = r.getInt(1);

// by position, no name available

System.out.println("Number of tapes for " + mTitle + " is: " +i)

}

SQL in Programs: Positioned update

Positioned update needs cursor name Define cursor (JDBC 1)

- Use for updates and deletes

Define cursor (JDBC2)

- more flexible (anonymous) cursor handling

- setCursorNamenot implemented in Oracle Driver public void setCursorName(String name)

throws SQLException