TCP/IP Networking (I)

TCP/IP Architecture

Gateways

IP Addresses

TCP/IP Architecture

• TCP/IP protocol has a four-layer structure linking an application to the physical network.
• Each layer has its own independent data structures.
• Conceptually, each layer is speaking directly to its counterpart on the other machine. In this sense, it is ignorant of what goes one after the data is sent.
• For example, in the Application layer, a NFS Client talks to a NFS Server and knows only the details of the NFS protocol they both use.
• As data packets are transported from the application to the physical network, each layer adds some control information in the form of a header.
• Once the packet reaches its destination in the physical network, each layer reads and removes its corresponding header before passing the package up in the stack until it is received by the application.

• This layer contains all application protocols (often providing user services) that use the Transport layer.
• Examples of application protocols include FTP, HTTP, DNS, NFS, SMTP, Telnet
• To send data, the application calls up a Transport layer protocol, such as TCP.
• Application Layer protocols usually treat transport and lower layer protocols as "black boxes." In this sense, they assume a stable network connection exist across which to communicate.

• TCP and UDP are the most importan protocols in this layer, delivering data between application and internet layers.
• TCP provides reliable data delivery service with error detection and error correction. It delivers data received from IP to the correct application (identified by a port number).
• UPD provides a connectionless delivery service.
• When called by an application, TCP wraps the data into a TCP packet.
• A TCP packet (also called TCP segment) contains a TCP header followed by the application data (including header).
• TCP then hands the packet to IP.
• TCP keeps track of what data belongs to what process.
• It is also responsible for ensuring that the packets are delivered with the correct contents and put in the right order before handing them off to the receiving application.

• The layer above the Network Access layer, and it provides the packet delivery service on which TCP/IP networks are built.
• It provides a routing mechanism allowing for packets to be transmitted across one or more different networks.

• The Internet Protocol (IP) runs in this layer and provides a way to transport datagrams across the network.
• It is a connectionless protocol and does not provide error control, relying on protocols in the other layers to provide error detection and recovery.
• Source and destination may be in the same or different networks.
• The IP protocol performs the functions of (a) host addressing and identification, and (b) packet routing (transporting packets from source to destination).
• After receiving a TCP packet, IP wraps it up and prepends an IP header, creating an IP datagram.
• Moving the data down the stack, IP hands it off to the hardware driver, that runs in the Network Access Layer.

• The IP layer has to figure out how to send the packet.
• Destination on a different physical network ?
• Then IP needs to find and send it to the appropriate gateway.
• Destination on the local ethernet network ?
• IP uses the Address Resolution Protocol (ARP) to determine what Ethernet card's MAC address is associated with the datagram IP address.
• How does it work?
• ARP broadcasts an ARP packet across the entire network asking which MAC address belongs to a particular IP address.
• Although every machines gets this broadcast, only the one out there that matches will respond. This is then stored by the IP layer in its internal ARP table.

You can look at the ARP table at any time by running the command:

jdoe@quark:~$ arp -a
home (194.113.47.147) at 98:0:bd:bd:8c:d2 [ether] on eth0
jdoe@quark:~$ 

• Protocols in this layer are designed to move packets (IP datagrams) between the internet layer interface of two different hosts on the same physical link.
• The actual process of moving packets at this level is usually controlled by device drivers of the network cards, which must know the details of the underlying network in order to format the data appropriately.
• At this level IP addresses are translated to physical addresses used by the network cards (i.e. Media Access Control (MAC) addresses)
• The network access layer (also called link layer) can be represented by different kinds of physical connections: Ethernet, token-ring, fiber-optics, ISDN, RS-232, etc.

Network Interfaces

• TCP/IP defines an abstract interface for hardware access.
• The interface, offering a set of operations that is used to access all types of hardware, hides the implementation details of operations necessary to access each particular equipment. Each vendor is responsible for provinding a driver that translates the commands of the TCP/IP interface to those of the particular piece of hardware.
• Each networking device has a corresponding interface in the kernel.
• When configured, each physical device is assigned an interface name.
• Each interface must also be assigned an IP address. Some interface names include:
• Ethernet interfaces: eth0, eth1
• PPP interfaces: ppp0, ppp1
• FDDI interfaces: fddi0, fddi1
• A computer having more than one logical or physical network interface is usually called a Multihomed host.

• An Ethernet network works like a bus system, where a host may send packets (or frames) of up to 1,500 bytes to another host on the same Ethernet.
• Hosts are identified by a six-byte address hardcoded into the firmware of its Ethernet network interface card (NIC).
• Ethernet addresses are usually written as a sequence of two-digit hex numbers separated
  by colons, as in aa:bb:cc:dd:ee:ff.

References:
Bautts, Tony, Terry Dawson and Gregor Prudy. 2005. Linux Network Administratos Guide
Hunt, Craig. 2002. TCP/IP Network Administration

Oracle DML Triggers

DML Triggers

DML triggers:

• Simple
• Compound
• can fire at one, some, or all of the preceding timing points
• INSTEAD OF
• Created on a noneditioning view, or on a nested table column of a noneditioning view.
• Used to perform a DML action on the underlying tables of a view.
• The database fires the INSTEAD OF trigger instead of running the triggering DML statement.
• Crossedition
• For use only in edition-based redefinition

• Triggering event: DELETE, INSERT, and UPDATE.
• Simple DML trigger fires at exactly one of these timing points:
• Before the triggering statement runs (BEFORE trigger).
• After the triggering statement runs (AFTER trigger).
• Statement triggers are always executed, independently of whether the triggering event actually affected any rows in the underlying table.
• Statement-level triggers are also known as table-level triggers.
• You cannot use a WHEN clause in a statement-level trigger.
• You also cannot reference the new or old pseudo-records in a statement-level trigger. Doing so results in a compile-time error (ORA-04082: NEW or OLD references not allowed in table level triggers).
• Before each row that the triggering statement affects (BEFORE each row trigger).
• After each row that the triggering statement affects (AFTER each row trigger).
• If the DML statement does not affect any row the trigger is NOT executed.

DML Triggers and DCL (Commit, Rollback, Savepoint)

• You cannot use SQL Data Control Language (DCL) in them, unless you declare the trigger as autonomous.
• When triggers run inside the scope of a transaction, they disallow setting a SAVEPOINT or performing either a ROLLBACK or COMMIT statement.
• Also, No function or procedure called by a trigger can have a DCL statement in their execution path.

DML Trigger: Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
 {BEFORE | AFTER}
 {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
 ON table_name
 [FOR EACH ROW]
 [WHEN (logical_expression)]
[DECLARE]
  [PRAGMA AUTONOMOUS_TRANSACTION;]
  declaration_statements;
BEGIN
  execution_statements;
END [trigger_name];
/

Trigger firing sequence for any DML (I/U/D)
Before DML FOR EACH STATEMENT. Before DML FOR EACH ROW. DML statement executed. After DML FOR EACH ROW. After DML FOR EACH STATEMENT.

(?) How many triggers can you have for INSERT on Employees?

• No restrictions. You can create many for a single event.
• However, the ORDER of firing is not guaranteed.
• Consolidate triggers, if sequence in important.

Before UPDATE Statement-level Triggers

• Useful to ensure integrity rules (i.e. new value should be within some range) or business requirements (i.e. ensure that user has required privileges for operation).
• Can be also used to check whether the triggering event should be allowed to complete successfully. If the statement is found to be not authorized, the trigger could, for example, log it on an audit table, send notification messages, and raise an exception so that the statement fails.

SQL> CREATE or REPLACE TRIGGER audit_emp_hours
 BEFORE INSERT OR UPDATE OR DELETE 
 ON employees
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END;
/
Trigger Created.
SQL> 

SQL> update employees set salary=43000 where employee_id=210;
update employees set salary=43000 where employee_id=210
         *
ERROR at line 1:
ORA-20205: You may only make changes during normal office hours
ORA-06512: at "HR.AUDIT_EMP_HOURS", line 4
ORA-04088: error during execution of trigger 'HR.AUDIT_EMP_HOURS'

Using CONDITIONAL PREDICATES to audit actions/events:

INSERING UPDATING DELETING

{ TRUE | FALSE }

The trigger below writes into an audit table (AUDITBL) when any DML statement is executed on the EMPLOYEES table.

(a) Create the Audit table (AUDITBL)

SQL> CREATE TABLE AudiTbl ( 
     id_audit NUMBER  NOT NULL , 
     username VARCHAR2 (20) , 
     action VARCHAR2 (400) , 
     dt_action DATE , 
     table_name VARCHAR2 (100) , 
     old_value VARCHAR2 (400) , 
     new_value VARCHAR2 (400) 
    );
Table created.

SQL>ALTER TABLE AudiTbl 
    ADD CONSTRAINT "AudiTblPK" PRIMARY KEY (id_audit);
Table altered.

SQL> CREATE SEQUENCE seq_audit START WITH 1;
Sequence created.
SQL> 

(b) Create Conditional AFTER UPDATE/INSERT/DELETE Trigger on (EMPLOYEES).

SQL> Set Serveroutput on;

SQL> CCREATE OR REPLACE TRIGGER audit_employees
 AFTER
    INSERT OR
    UPDATE OF salary OR
    UPDATE OF department_id OR
    DELETE
  ON employees
  FOR EACH ROW
BEGIN
  CASE
    WHEN INSERTING THEN
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'INSERT', sysdate, 'employees', null, null);
      DBMS_OUTPUT.PUT_LINE('Inserting on employees');
    WHEN UPDATING('salary') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE salary', sysdate, 'employees', 
        :OLD.salary, :NEW.salary);
    WHEN UPDATING('department_id') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE Dept_id', sysdate, 'employees', 
        :OLD.department_id, :NEW.department_id);
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'DELETE', sysdate, 'employees', 
        :OLD.employee_id, null);
  END CASE;
END;
/

Notes:

• OLD, NEW and PARENT are correlation names. :OLD and :NEW qualifiers can only be used in row level triggers. These reference the old and new values of a column, respectively.

(c) Test the INSERT trigger on (EMPLOYEES).

SQL> insert into employees (employee_id, first_name, last_name, email, 
         phone_number, hire_date, job_id, salary, commission_pct, 
         manager_id, department_id)
values (employees_seq.nextval, 'John', 'Fergunson', 'email@email.com', 
        null, sysdate, 'IT_PROG', 20000, null, null, null);
Inserting on employees

1 row created.
SQL>
SQL> Select * from auditbl;

ID_AUDIT	USERNAME	ACTION	DT_ACTION	TABLE_NAME	OLD_VALUE	NEW_VALUE
1	JOHNF	INSERT	18-AUG-10	employees

(d) Test the UPDATE (salary) trigger

SQL> update employees set salary=salary*2 where employee_id=210;
Updating salary

1 row updated.
SQL>
SQL> Select * from auditbl;

ID_AUDIT	USERNAME	ACTION	DT_ACTION	TABLE_NAME	OLD_VALUE	NEW_VALUE
1	JOHNF	INSERT	18-AUG-10	employees
2	MARCJ	UPDATE	11-Sep-10	employees	15000	30000

Row-Level Update Trigger

• The trigger below is fired when an employee is assigned to the department 10.
• It fires AFTER an UPDATE on emploees, but ONLY WHEN the update was on the department_id COLUMN AND the new value for that column is set to 10.
• Note that in the WHEN clause you use NEW. In the trigger bodyyou use :NEW.
• Inside a trigger body, you preface the pseudo-records with a colon (:). The colon let you reference the externally scoped pseudo-records in the trigger body.

SQL> CREATE OR REPLACE TRIGGER audit_dept10
  AFTER UPDATE OF department_id
  ON employees
  FOR EACH ROW
  WHEN (new.department_id = 10)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Update on employees');
  INSERT into AudiTbl VALUES
    (seq_audit.nextval, user, 'UPDATE', sysdate, 'employees', 
        :old.department_id, :new.department_id);
END;
/

Trigger created.
SQL> set serveroutput on
SQL> update employees 
        set department_id = 10 
        where employee_id in (201, 202, 203, 210);

4 rows updated.
Update on employees
Update on employees
Update on employees
Update on employees

 
SQL> select * from auditbl;

ID_AUDIT	USERNAME	ACTION	DT_ACTION	TABLE_NAME	OLD_VALUE	NEW_VALUE
18	JAFFN	UPDATE	26-AUG-11	employees	70	10
18	JAFFN	UPDATE	26-AUG-11	employees	20	10
18	JAFFN	UPDATE	26-AUG-11	employees	20	10
18	JAFFN	UPDATE	26-AUG-11	employees	40	10

Exception handling in Triggers

(ref) Oracle DDL Triggers

DDL Triggers

Can be used to:

• Monitor significant events in the database.
• Monitor errant code that can that can corrupt or destabilize your database.
• Use these in development, test, and stage systems to understand and monitor the dynamics of database activities.
• Also useful when you patch your application code. They can let you find
  potential changes between releases.
• During an upgrade: use instead-of create trigger to enforce table creation storage clauses or partitioning rules.
• Track the creation and modification of tables by application programs that lead to database fragmentation.
• Effective security tools: monitor GRANT and REVOKE privilege statements.

When can they fire? DDL Events
ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DDL (means: ANY DDL event), DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE

System-defined event attribute functions
ORA_CLIENT_IP_ADDRESS	Returns the client IP address as a VARCHAR2. DECLARE ip_address VARCHAR2(11); BEGIN IF ora_sysevent = 'LOGON' THEN ip_address := ora_client_ip_address; END IF; END;
ORA_DATABASE_NAME	Returns the database name. BEGIN (...) db_name := ora_database_name; (...) END;
ORA_DES_ENCRYPTED_PASSWORD	- Returns the DES-encrypted password as VARCHAR2. - Equivalent to the value in the SYS.USER$ table PASSWORD col (11g). - Passwds are no longer accessible in DBA_USERS or ALL_USERS BEGIN IF ora_dict_obj_type = 'USER' THEN password := ora_des_encrypted_password; END IF; END;
ORA_DICT_OBJ_NAME	Returns the name of the object target of the DDL statement. DECLARE database VARCHAR2(50); BEGIN database := ora_obj_name; END;
ORA_DICT_OBJ_NAME_LIST	The function returns the number of elements in the list as a PLS_INTEGER datatype. The name_list contains the list of object names touched by the triggering event. DECLARE name_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_name_list(name_list); END IF; END;
ORA_DICT_OBJ_OWNER	returns an owner of the object acted upon by the event. database := ora_dict_obj_owner;
ORA_DICT_OBJ_OWNER_LIST	formal parameter datatype is defined in the DBMS_STANDARD package as ORA_NAME_LIST_T (table of varchar2(64)). The function returns the number of elements in the list. The owner_list contains the list of owners of objects affected by the event. DECLARE owner_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_owner_list(owner_list); END IF; END;
ORA_DICT_OBJ_TYPE
ORA_GRANTEE
ORA_INSTANCE_NUM
ORA_IS_ALTER_COLUMN
ORA_IS_CREATING_NESTED_TABLE	BEGIN IF ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table THEN INSERT INTO logging_table VALUES (ora_dict_obj_name||'.'||' created with nested table.'); END IF; END;
ORA_IS_DROP_COLUMN
ORA_IS_SERVERERROR
ORA_LOGIN_USER>
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause. This is only available in an INSTEAD OF CREATE trigger. DECLARE sql_text ORA_NAME_LIST_T; sql_stmt VARCHAR2(32767); partition VARCHAR2(32767) := 'partitioning_clause'; BEGIN FOR i IN 1..ora_sql_txt(sql_text) LOOP sql_stmt := sql_stmt || sql_text(i); END LOOP; sql_stmt := SUBSTR(sql_text,1,ora_partition_pos – 1) ||' ' || partition||' '|| SUBSTR(sql_test,ora_partition_pos); -- Add logic to prepend schema because -- this runs under SYSTEM. sql_stmt := REPLACE(UPPER(sql_stmt),'CREATE TABLE ' ,'CREATE TABLE '||ora_login_user||'.'); EXECUTE IMMEDIATE sql_stmt; END;
ORA_PRIVILEGE_LIST	Returns list of privileges granted/revoked.
ORA_REVOKEE
ORA_SERVER_ERROR
ORA_SERVER_ERROR_DEPTH
ORA_SERVER_ERROR_MSG
ORA_SERVER_ERROR_NUM_PARAMS
ORA_SERVER_ERROR_PARAM
ORA_SQL_TXT	Returns the substring of the processed SQL statement that triggered the event.
ORA_SYSEVENT
ORA_WITH_GRANT_OPTION
SPACE_ERROR_INFO

DDL Triggers: Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA}
[WHEN (logical_expression)]
[DECLARE]
 declaration_statements;
BEGIN
 execution_statements;
END [trigger_name];
/

DDL Trigger on Creation statements

(a) create table audit_creation
(b) create sequence audit_creation_s1
(c) Create trigger audit_creation
(d) create a synonym to fire the trigger
(e) check the inserted row on audit_creation

CREATE TABLE audit_creation
( audit_creation_id NUMBER PRIMARY KEY,
  audit_owner_name VARCHAR2(30) NOT NULL,
  audit_obj_name   VARCHAR2(30) NOT NULL,
  audit_date       DATE NOT NULL);

CREATE SEQUENCE audit_creation_s1;



CREATE OR REPLACE TRIGGER audit_creation
 BEFORE CREATE ON SCHEMA
BEGIN
  insert into audit_creation values
  (audit_creation_s1.nextval, 
   ORA_DICT_OBJ_OWNER,
   ORA_DICT_OBJ_NAME,
   sysdate);
END audit_creation;

SQL> Create synonym empsym for hr.employees;

synonym created.
SQL> Select * from audit_creation;

AUDIT_CREATION_ID    AUDIT_OWNER_NAME       AUDIT_OBJ_NAME      AUDIT_DATE
-------------------  ---------------------- ------------------- ---------------
1                    HR                     EMPSYN              22-AUG-10