Microsoft Word - Grabowski.doc

Oracle 8i R2 w praktyce

:LHVáDZ

Grabowski

Oracle Polska Sp. Z o.o.

e–mail: Wieslaw.Grabowski@oracle.com

Abstrakt. Oracle

6HUYHUL5MHVWQDMQRZV]ZHUVMVHUZHUDED]\GDQ\FKOracle. Wersja ta zawiera bardzo

ZLHOH Uy*Q\FK QRZ\FK ZáDVQRFL :LHOH ] QLFK SR]QDOLP\ MHG\QLH ] OLWHUDWXU\ :\NáDG WHQ MHVW SUyE

SRGVXPRZDQLDGRZLDGF]H]HEUDQ\FKSU]H]JUXS &25( &'0']LDáX8VáXJ.RQVXOWDF\MQ\FKOracle

3ROVND Z F]DVLH Uy*Q\FK SU]HGVL Z]L ü ] Z\NRU]\VWDQLHP VHUZHUD 2UDFOH L 5 :UyG SRUXV]RQ\FK

]DJDGQLHE GRPyZLRQH]DJDGQLHQLDÄSU\ZDWQHMED]\GDQ\FKÄSDUW\FMRQRZDQLDDGPLQLVWURZDQLDED]

GDQ\FK Z\NRU]\VWDQLD LQGHNVyZ RSDUW\FK QD IXQNFMDFK LWS &HOHP FDáHJR UHIHUDWX MHVW SU]HGVWDZLHQLH

U]HF]\ZLVW\FK]DVWRVRZDSU]HGVWDZLRQ\FKZ\*HMQRZ\FKFHFKVHUZHUDED]\GDQ\FKOracle w wersji 8i.

1. Nowe cechy serwera bazy danych Oracle 8i wydanie 2

Wydanie 2 serwera bazy danych Oracle 8i wprowadza wiele nowych cech, z których wiele

znamy tylko od strony prezentacji marketingowych lub ze stron dokumentacji. Poznanie wszystkich

QRZ\FK FHFK MHVW EDUG]R WUXGQH ]ZáDV]F]D ELRUF SRG XZDJ WHPSR SRMDZLDQLD VL QRZ\FK
„lepszych” wersji

RSURJUDPRZQLD :V]\VF\ X*\WNRZQLF\ WHM ZHUVML VHUZHUD ED]\ GDQ\FK PDM

MDNLHGRZLDGF]HQLD]Z\NRU]\VWDQLDUy*Q\FKFHFKWHJRRSURJUDPRZDQLD:\PLDQDX]\VNDQ\FKZ

F]DVLH WDNLFK SUyE GRZLDGF]H PR*H QDP SR]ZROLü QD V]\EV]H SU]\VZRMHQLH VRELH NROHMQ\FK

ZHUVML Uy*Q\FK SURJUDPyZ D Z W\P L VHUZHUD ED]\ GDQ\FK 2UDFOH 6SRUyG Z\NRU]\VW\ZDQ\FK
przez pracowników grupy CORE &

&'0L FHFK ]D LQWHUHVXMFH X]QDáHP Z\EUDQH HOHPHQW\

Z\PLHQLRQ\FKSRQL*HMJUXSZáDVQRFL

Autonomiczne transakcje

Java

Fine grain security i kontekst aplikacyjny

Wyzwalacze systemowe

Kontekst aplikacyjny

Partycjonowanie

2. Autonomiczne transakcje

1LHNLHG\ PDP\ SRWU]HE DE\ ]DSLVDü OXE ]UH]\JQRZDü ] ]DSLVX F] FL ]PLDQ Z VSRVyE

QLH]DOH*Q\RGWHJRZMDNLVSRVyE]DNRF]\VL JáyZQDWUDQVDNFMD7\SRZ\PSU]\NáDGHPPR*HE\ü

]UHDOL]RZDQLHZáDVQHMUHMHVWUDFMLLQIRUPDFMLREá GDFK]DSHZQLDMFHMLFK]DSLVGRED]\QDZHWZWHG\
gdy oryginalna transakcja zostanie wycofana.

2.1. Podstawowe informacje o transakcja autonomicznych

$XWRQRPLF]QD WUDQVDNFMD MHVW WR QLH]DOH*QD WUDQVDNFMD UR]SRF] WD SU]H] LQQ W]Z *áyZQ

WUDQVDNFM 8PR*OLZLD RQD Ä]DZLHV]HQLH´ WUDQVDNFML JáyZQHM ]DWZLHUG]HQLH OXE Z\FRIDQLH

ZFKRG]F\FK Z VNáG WUDQVDNFML DXWRQRPLF]QHM ]PLDQ D QDVW SQLH Z]QRZLHQLH SU]HWZDU]DQLD

WUDQVDNFMLJáyZQHM

7UDQVDNFMD WDND PD VZyM ZáDVQ\ ]DNUHV RERZL]\ZDQLD -HVW QLP ]DNUHV SRGSURJUDPX

oznaczonego

SUDJP$872120286B75$16$&7,21

=DSRGSURJUDPZW\PNRQWHNFLHXZD*DVL

1DMZ\*V]\QLH]DJQLH*G*RQ\DQRQLPRZ\EORN3/64/

Lokalne, samodzielne i pakietowe funkcje i procedury

326

:LHVáDZGrabowski

Metody typów obiektowych

Wyzwalacze bazodanowe

$XWRQRPLF]QHWUDQVDNFMHFKDUDNWHU\]XMVL W\P*H

•

=PLDQ\ DXWRQRPLF]Q\FK WUDQVDNFML QLH ]DOH* RG VWDQX DQL RVWDWHF]QHJR ]DNRF]HQLD

WUDQVDNFMLJáyZQHM1DSU]\NáDG

7UDQVDNFMD DXWRQRPLF]QD QLH ZLG]L *DGQ\FK ]PLDQ ZSURZDG]RQ\FK SU]H] WUDQVDNFM

JáyZQ

=DWZLHUG]HQLH OXE Z\FRIDQLH ]PLDQ Z WUDQVDNFML DXWRQRPLF]QHM QLH ZSá\ZD Z *DGHQ

VSRVyEQDZ\QLNWUDQVDNFMLJáyZQHM

•

:\QLNL ]PLDQ ZSURZDG]RQ\FK SU]H] WUDQVDNFM DXWRQRPLF]Q V ZLGRF]QH GOD LQQ\FK

WUDQVDNFMLQDW\FKPLDVWSRLFK]DWZLHUG]HQLX2]QDF]DWR*HLQQHWUDQVDNFMHPRJVNRU]\VWDü

]H ]PRG\ILNRZDQ\FK Z WHQ VSRVyE LQIRUPDFML QLH F]HNDMF QD ]DNRF]HQLH WUDQVDNFML

JáyZQHM

•

7UDQVDNFMDDXWRQRPLF]QDPR*HUR]SRF]\QDüLQQHWUDQVDNFMHDXWRQRPLF]QH

•

7UDQVDNFMDDXWRQRPLF]QDWRFDáNRZLFLHQLH]DOH*QDWUDQVDNFMDDQLHWUDQVDNFMD]DJQLH*G*RQD

2.2. Wykorzystanie

:QDV]HMSUDNW\FH]DLVWQLDáDSRWU]HED]DLPSOHPHQWRZDQLDPR*OLZRFLOHG]HQLDZ\NRQ\ZDQ\FK

SU]H] X*\WNRZQLNyZ SROHFH select z GRNáDGQRFLD GR SU]HJOGDQ\FK ZLHUV]\ : W\P FHOX

Z\NRU]\VWDOLP\ SRáF]HQLH fine grained security z autonomicznymi transakcjami. W ramach
mechanizmów fine grained

VHFXULW\ GR ND*GHJR Z\NRQ\ZDQHJR SROHFHQLD select doklejany jest

QLHZLGRF]Q\ SUHG\NDW ]DZLHUDMF\ Z\ZRáDQLH IXQNFML ]DSLVXMFHM LQIRUPDFMH R SU]HJOGDQ\FK

ZLHUV]DFKGRRGSRZLHGQLHMWDEHOLODGX:\ZRáXZDQDIXQNFMDG]LDáDMDNRDXWRQRPLF]QDWUDQVDNFMD

3. Java

3RF]\QDMF RG ZHUVML L PDP\ PR*OLZRü NRU]\VWDQLD ] WZRU]HQLD SURFHGXU L IXQNFML

VNáDGRZDQ\FKZ-DYLH3HáQHSU]HGVWDZLHQLHZV]HONLFKQRZ\FKPR*OLZRFLZ\QLNDMF\FK]WHJR

IDNWX Z\NUDF]D SR]D UDP\ WHJR UHIHUDWX 7XWDM SU]HGVWDZLP\ GRü QLHW\SRZH ]DVWRVRZDQLH

]ZL]DQH ] SUyE LPSOHPHQWDFML W]Z fine grained VHFXULW\ ]DOH*QHJR RG WUHFL Z\NRQ\ZDQHJR
polecenia SQL.

3RGVWDZRZHLQIRUPDFMHRIXQNFMDFKVNáDGRZDQ\FKZ

Javie

3URFHGXU\VNáDGRZDQHZJavie to publikowane do SQL metody klas Javy. Publikowanie klasy

SROHJDQDVWZRU]HQLXVSHF\ILNDFMLZ\ZRáDQLDNWyUDGRNRQXMHPDSRZDQLDQD]ZPHWRGJavy, typów

SDUDPHWUyZRUD]W\SyZ]ZUDFDQ\FKZDUWRFLGRLFKRGSRZLHGQLNyZZ64/

8WZRU]HQLHSURFHGXU\VNáDGRZDQHMZ-DYLHZ\PDJDZ\NRQDQLDQDVW SXMF\FKNURNyZ

7ZRU]\P\NRGNODV\ZZ\EUDQ\PQDU] G]LXnp.Jdeveloper )

public class Oscar {

// return a quotation from Oscar Wilde

public static String quote() {

return "I can resist everything except temptation.";

}

=DáDGRZDüLUR]ZL]DüQD]Z\NODVJavy

Oracle 8i R2 w praktyce

327

loadjava -user scott/tiger Oscar.class

2SXEOLNRZDüNODV Javy

SQL> connect scott/tiger

SQL> CREATE FUNCTION oscar_quote RETURN VARCHAR2

2 AS LANGUAGE JAVA

NAME 'Oscar.quote() return java.lang.String';

0R*HP\MX*Z\ZRá\ZDüVWZRU]RQIXQNFM

SQL> VARIABLE theQuote VARCHAR2(50);

SQL> CALL oscar_quote() INTO :theQuote;

SQL> PRINT theQuote;

THEQUOTE

--------------------------------------------------

I can resist everything except temptation.

7ZRU]RQDSURFHGXUDPR*HSRWU]HERZDüGRVW SXGR]DZDUW\FKZED]LHGDQ\FKLQIRUPDFML-HVW

WRPR*OLZH]Z\NRU]\VWDQLHP-'%&:FHOXGRVW SXGRED]\GDQ\FKPXVLP\XWZRU]\üRELHNW

QDOH*F\GRNODV\&RQQHFWLRQSU]\F]\PPDP\GZLHPR*OLZHV\WXDFMH
1.

wykonujemy tzw. call

RXWF]\OLSRGáF]DP\VL GRWHMVDPHMED]\GDQ\FK

:W\PSU]\SDGNXPR*HP\Z\NRU]\VWDüW]Zinternal driver czyli

Connection conn = new oracle.jdbc.driver.OracleDriver().defaultConnection();

SRGáF]DP\VL GRLQQHMED]\GDQ\FKOXEFKFHP\RWZRU]\üLQQVHVM

Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@host:1521:SID","system","manager")

;

1DVW SQLH PXVLP\ XWZRU]\ü RELHNW\ NODV\ QS PreparedStatement, gdy korzystamy ze

]PLHQQ\FKZL]DQLD

PreparedStatement stmt = conn.prepareStatement(sqlStmt);

lub

&DOODEOH6WDWHPHQWJG\FKFHP\Z\ZRáDüEORN3/64/OXESURFHGXU VNáDGRZDQ

CallableStatement stmt = conn.prepareCall(" begin zapisz_slad(?); end ;");

:NROHMQ\PNURNXPXVLP\QDGDüZDUWRFLIXQNFMRPZL]DQLD

stmt.setString(1,sqlStmt);

SRWHPZ\NRQDüSROHFHQLHWZRU]FResultSet

ResultSet result = stmt.executeQuery();

328

:LHVáDZGrabowski

,ZNRFXSU]HWZDU]DüZLHUV]HZFKRG]FHZResultSet

while (rset.next()) {

sqlStmt = sqlStmt.append(rset.getString(1));

}

3.2. Wykorzystanie

3UyEXMF RSUDFRZDü SURFHGXU\ fine grained VHFXULW\ UHDJXMFH Uy*QLH Z ]DOH*QRFL RG WUHFL

Z\NRQ\ZDQHJRSROHFHQLHSRMDZLáVL SUREOHPMDNZF]DVLHZ\NRQ\ZDQLDSROHFHQLD]QDOH(üMHJR

WUHü2ND]DáRVL *HZ3/64/XZIXQNFMLGRáF]RQHMSU]H]PHFKDQL]Pfine grained security do
predykatów klauzuli

ZKHUH QLH GDMH PDP\ WDNLHM PR*OLZRFL %LH*FH ]DS\WDQLH GOD GDQHM sessji

PR*HE\ü]QDOH]LRQHNRU]\VWDMF]NROXPQsql_address, sql_hash_value perspektywy V$SESSION i

Z\ELHUDMFRGSRZLHGQLHZLHUV]H]SHUVSHNW\Z\YVTOWH[W3RQLHZD*ZV]\VWNLHNROHMQH]DS\WDQLD

RGE\ZDMVL ZUDPDFKWHMVDPHMVHVMLGODWHJRND*GHNROHMQH]DS\WDQLH]PLHQLDZDUWRFLNROXPQ
sql_address,

VTOBKDVKBYDOXHSRMDZLDMFHVL ZSHUVSHNW\ZLH96(66,21LQLHMHVWHP\ZVWDQLH

]QDOH(üLQWHUHVXMFHJRQDV]DS\WDQLD*G\E\P\Z\NRQDOLRGSRZLHGQLH]DS\WDQLD]RGU EQHMVHVML

]QDOH]LHQLH SRWU]HEQHJR QDP ]GDQLD MHVW EDUG]R SURVWH $E\ WHJR GRNRQDü PXVLP\ VNRU]\VWDü ]

SURFHGXU\]HZQ WU]QHMexternal procedure). Do wyboru mamy programowanie w C lub Javie. W
tym przypadku wybrano

-DY $E\ ]QDOH(ü DNWXDOQLH Z\NRQ\ZDQH SROHFHQLH PXVLP\ RWZRU]\ü

SRáF]HQLH ]ZURWQH GR ED]\ GDQ\FK QLH NRU]\VWDMFH ] GRP\OQHJR ZHZQ WU]QHJR VWHURZQLND
(internal driver).

3RQL*HM]QDMGXMHVL NRGSURJUDPXQDSLVDQHJRZFHOXVSUDZG]HQLDPR*OLZRFLUHDOL]RZDOQRFL

(proof of

FRQFHSW WDNLHJR ]DGDQLD 3RQL*V]\ NRG QLH E\á MHV]F]H RSW\PDOL]RZDQ\ SRG NWHP

Z\GDMQRFLLQDOH*\JRWUDNWRZDüMHG\QLHMDNRSU]\NáDGRZ\

package sql;

import java.lang.*;

import java.sql.*;

import java.util.*;

import oracle.jdbc.driver.*;

/*import oracle.sql.*;

import sqlj.runtime.ref.DefaultContext;*/

//import oracle.sqlj.runtime.Oracle;

/**

* A Class class.

* <P>

* @

DXWKRU:LHVáDZGrabowski

class Zdanie extends Object {

long sqlStmtId ;

boolean sledzic ;

public Zdanie(long id, boolean sledz){

sqlStmtId = id;

sledzic = sledz;

}

public class Daj_zdanie extends Object {

static Vector zdania = new Vector();

Oracle 8i R2 w praktyce

329

/**

* Constructor

static int juzBylo (long sqlStmtId) {

for ( int i = 0; i< zdania.size();i++) {

if (((Zdanie) zdania.elementAt(i)).sqlStmtId==sqlStmtId) return i;

}

return -1;

}

static public String daj_zdanie(int numerSesji, long sqlStmtId )

throws SQLException {

System.out.println("dajzdanie");

Zdanie zdanie;

int nr;

boolean sledzic = false;

StringBuffer sqlStmt = new StringBuffer();

if ( (nr=juzBylo(sqlStmtId))<0 ) {
RWZLHUDP\RVREQVHVM
Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@host:1521:SID","system","manager")

;

try {
SROHFHQLH]QDMGXMFHDNWXDOQHSROHFHQLHGODVHVMLR]DGDQ\Pnumrze AUDSID
PreparedStatement pstmt

= conn.prepareStatement ("SELECT sql_text FROM v$sqltext where

(address,hash_value) = (select sql_address,sql_hash_value from v$session where

audsid = (?)) order by piece");

pstmt.setInt(1, numerSesji);

ResultSet rset = pstmt.executeQuery ();

while (rset.next()) {

sqlStmt = sqlStmt.append(rset.getString(1));

}

rset.close();

} catch (Exception e) {

return e.getMessage();

}

// zdanie wystepuje po raz pierwszy

zdanie = new

Zdanie(sqlStmtId,czySledzic(sqlStmt.toString().toLowerCase(),conn));

conn.close();

} else {
]GDQLHMX*E\áRVSUDZG]DQH
zdanie = ((Zdanie)zdania.elementAt(nr));

}

if(zdanie.sledzic) sledz(sqlStmt.toString());

return (zdanie.sledzic?"true":"false");

}

static boolean czySledzic(String stmt, Connection conn)

throws SQLException {

// do zanalizowania polecenia korzystamy z metody parseStatement

// w wyniku uzyskujemy

5HVXOW6HW0HWD'DWDRSLVXMF\OLVW

ResultSetMetaData metaData = parseStatement(stmt,(OracleConnection)conn);

for(int i = 1 ; i<= metaData.getColumnCount(); i++) {

330

:LHVáDZGrabowski

if (

kolumnaSledzona(metaData.getColumnName(i),metaData.getTableName(i), conn)){

return true;

}

return false;

}

static boolean kolumnaSledzona( String kolumna,String tabela, Connection

conn)

throws SQLException {
VSUDZG]HQLHF]\NROXPQD]QDMGXMHVL ]HVWDZLHNROXPQNWyUHnalezy obserwowac
PreparedStatement stmt = conn.prepareStatement("select count(*) from bla

where k1 = ?");

stmt.setString(1,kolumna);

ResultSet result = stmt.executeQuery();

result.next();

if (result.getInt(1)>0 ) return true;

return false;

}

static ResultSetMetaData parseStatement(String sqlStmt, OracleConnection

conn)

throws SQLException {

PreparedStatement stmt = conn.prepareStatement(sqlStmt);

System.out.println("parseStatement 2"+sqlStmt+" ");

ResultSetMetaData metaData = stmt.executeQuery().getMetaData() ;

// ResultSetMetaData metaData = null ;

return metaData;

}

static void sledz (String sqlStmt) throws SQLException{

Connection conn = new

oracle.jdbc.driver.OracleDriver().defaultConnection();

CallableStatement stmt = conn.prepareCall(" begin zapisz_slad(?); end

;");

stmt.setString(1,sqlStmt);

stmt.executeUpdate();

}

3R]DáDGRZDQLXNODV\GRVHUZHUDSU]\SRPRF\loadjava
1DOH*\MHV]F]HRSXEOLNRZDü*GDQHPHWRG\

CREATE FUNCTION my_sql (p_ses_audid number, p_stmt_id number) RETURN VARCHAR2

2 AS LANGUAGE JAVA

NAME 'Daj_zdanie.daj_zdanie(int , long ) return java.lang.String';

2SLVDQ\ Z\*HM PHFKDQL]P Z SDU]H ] DXWRQRPLF]\PL WUDQVDNFMDPL PyJáE\ E\ü Z\NRU]\VWDQ\

QS GR XWZRU]HQLD WDEHOL ]DSDPL WXMFHM Z\EUDQH SU]H] SROHFHQLH ZLHUV]H 0RJáRE\ WR E\ü

X*\WHF]QH MH*HOL SR SU]HDQDOL]RZDQLX Z\QLNX SROHFHQLD VHOHFW FKFLHOLE\P\ Z NROHMQ\FK

Oracle 8i R2 w praktyce

331

]DS\WDQLDFKRJUDQLF]\üVL GRZ\EUDQHJRMX*]ELRUXZLHUV]\6\WXDFMDWDNDZ\VW SXMHF]DVDPLZ

QDU] G]LXDiscoverer.

4. Fine grained security , kontekst aplikacyjny oraz wyzwalacze

systemowe

Fine grained

VHFXULW\ XPR*OLZLD ZSURZDG]HQLH QRZHJR SR]LRPX EH]SLHF]HVWZD SRSU]H]

G\QDPLF]QHRJUDQLF]DQLHZLGRF]Q\FKGODX*\WNRZQLNyZZLHUV]\

4.1. Podstawowe informacje

Implementacja fine grained security wykorzystuje

QDVWHSXMFHHOHPHQW\SDNLHW'%06B5/6

dostepny tylko w wersji Enterprise Edition ), definiowanie kontekstu aplikacyjnego oraz
wyzwalacza systemowego ON LOGON.

'HILQLRZDQLH SURFHGXU EH]SLHF]HVWZD QD SR]LRPLH WDEHO OXE SHUVSHNW\Z ]DPLDVW

ZEXGRZ\ZDQLD LFK Z NRG DSOLNDFML ]DSHZQLD Z\*V]\ SR]LRP EH]SLHF]HQVWZD SURVWRW

LPSOHPHQWDFMLLHODVW\F]QRü

%H]SLHF]HVWZR
,PSOHPHQWDFMDSURFHGXUEH]SLHF]HVWZDQDSR]LRPLHWDEHOLEG(SHUVSHNW\Z\SRZRGXMH

*H QLHLVWRWQ\ VWDMH VL VSRVyE SRGáF]HQLD GR ED]\ SRSU]H] DSOLNDFMH F]\ WH* QS 64/3/86

:\PXV]DQHUHJXá\EH]SLHF]HVWZDRERZL]XM]DZV]H

Prostota

,PSOHPHQWDFMD WDND XSUDV]F]D WDN*H ]DU]G]DQLH SRQLHZD* PRG\ILNXMHP\ W\ONR

SRMHG\QF]\RELHNWDVNXWNLE GZLGRF]QHGODZV]\VWNLFKPHWRGGRVW SXLDSOLNDFML

(ODVW\F]QRü
'OD MHGQHJR RELHNWX MDN WDEHOD F]\ SHUVSHNW\ZD PR*QD PLHü ZLHOH SURFHGXU

EH]SLHF]HVWZDQSMHGQDGODSROHFH6(/(&7LQQGOD,16(57DMHV]F]HLQQDGOD83'$7(L

'(/(7( 'OD SU]\NáDGX PR*HP\ FKFLHü XPR*OLZLü SUDFRZQLNRZL NDGU Z\NRQ\ZDQLH SROHFH

6(/(&7 GOD ZV]\VWNLFK SUDFRZQLNyZ Z REVáXJLZDQHM SU]H] QLHJR NRPyUFH RUJDQL]DF\MQHM OHF]

RJUDQLF]\üPR*OLZRüPRG\ILNRZDQLDSU]H]QLHJRSáDFW\ONRGRSUDFRZQLNyZNWyU\FKQD]ZLVND

]DF]\QDMVL QDOLWHU\RGÄ$´GRÄ)´0R*HP\WH*GHILQLRZDüZLHOHSURFHGXUEH]SLHF]HVWZDGOD

GDQHMWDEHOLOXESHUVSHNW\Z\LZWHG\E GRQHGRáF]DQH]XG]LDáHPRSHUDWRUD$1'

:GUR*HQLHZáDVQHMSROLW\NLEH]SLHF]HVWZDZ\PDJDZ\NRQDQLDQDVW SXMF\FKNURNyZ

WZRU]\P\ IXQNFM ]ZUDFDMF Z\PDJDQ\ SU]H] QDV GRGDWNRZ\ SUHG\NDW GRáF]DQ\ GR

ND*GHJRSROHFHQLD

UHMHVWUXMHP\XWZRU]RQDIXQNFM SU]\SRPRF\SDNLHWX

DBMS_RLS.ADD_POLICY (

object_schema IN VARCHAR2 := NULL,

object_name IN VARCHAR2,

policy_name IN VARCHAR2,

function_schema IN VARCHAR2 := NULL,

policy_function IN VARCHAR2,

statement_types IN VARCHAR2 := NULL,

update_check IN BOOLEAN := FALSE,

enable IN BOOLEAN := TRUE);

3RGDQH Z\*HM GZD QLH]E GQH NURNL WR SU]\SDGHN QDMSURVWV]\ 1DMF] FLHM SU]\GDWQH MHVW

Z\NRU]\VWDQLH GRGDWNRZ\FK LQIRUPDFML ]DSLV\ZDQ\FK Z NRQWHNFLH DSOLNDFML 8PR*OLZLD WR

EDUG]LHM HODVW\F]QH UHDJRZDQLH SU]H] SURFHGXU EH]SLHF]HVWZD QD Z\NRQ\ZDQH polecenia.Do

Z\NRU]\VWDQLD PDP\ SR]LRP\ NRQWHNVWX V\VWHPRZ\ L GHILQLRZDQ\ SU]H] X*\WNRZQLND

332

:LHVáDZGrabowski

3LHUZV]\MHVWWRJUXSDLQIRUPDFMLGRVW SQDZJUXSLH86(5(19,QIRUPDFMHWDNLHPR*HP\W\ONR

F]\WDü QLH PR*QD QDWRPLDVW ]PLHQLDü LFK ZDUWRFL : EDUG]LHM ]DDZDQVRZDQ\FK SU]\SDGNDFK

LQWHUHVXMFDE G]LHPR*OLZRüWZRU]HQLDZáDVQ\FKJUXS:W\PFHOXQDOH*\
1.

8WZRU]\üSURFHGXU VáX*FGRXVWDZLDQLDNRQWHNVWXQS

CREATE OR REPLACE PACKAGE empsec AS

PROCEDURE setempctx;

END empsec;

CREATE OR REPLACE PACKAGE BODY empsec AS

PROCEDURE setempctx AS

the_role VARCHAR2(10);

me VARCHAR2(10);

myename VARCHAR2(30);

myempno NUMBER;

myboss NUMBER;

subs NUMBER;

BEGIN

dbms_session.set_context('empctx','role','EMP');

-- By default I am an EMP

me:= SYS_CONTEXT('userenv','session_user');

-- get my information from emp

SELECT empno, mgr , ename

INTO myempno, myboss, myename

FROM emp

WHERE ename=me;

-- If I have any subordinates I am an MGR

SELECT COUNT(*)

INTO subs

FROM emp

WHERE mgr=myempno;

-- @ do i really still need this ?

IF NVL(subs,0) > 0 THEN

dbms_session.set_context('empctx','role','MGR');

END IF;

-- If I do NOT have a manager then I am CEO

IF myboss IS NULL THEN

dbms_session.set_context('empctx','role','CEO');

END IF;

EXCEPTION

WHEN OTHERS THEN NULL;

END setempctx;

END empsec;

8WZRU]\üZáDVQ\NRQWHNVWLSRZL]DüJR]SURFHGXUXVWDZLDMFNRQWHNVW

CREATE CONTEXT emp_sec USING empsec;

Oracle 8i R2 w praktyce

333

8VWDZLüSRWU]HEQH]PLHQQHZFKRG]FHZVNáDGNRQWHNVWXZZ\]ZDODF]X21/2*21

create or replace trigger ajh_security_trg

after logon on database

begin

if (user in ('SYS','SYSTEM','ORDSYS','DBSNMP','TRACESVR','CTXSYS',

'ORDPLUGINS','MDSYS','OUTLN','HST','RAD','OAS_PUBLIC','WEBDB') )

then

return;

else

scott.empsec.setempctx;

end if;

end;

:\NRU]\VWDü XVWDZLRQH ]PLHQQH Z SDNLHFLH EH]SLHF]HVWZD : SRQL*V]\P SDNLHFLH

wykorzystano tez autonomiczne transakcje do zapisania w tabeli slad pewnych informacji o
stanie zmiennych pakietowych i kontekstowych.

CREATE OR REPLACE PACKAGE hr_access AS

FUNCTION secure_access( obj_schema VARCHAR2, obj_name VARCHAR2 )

RETURN VARCHAR2;

END hr_access;

CREATE OR REPLACE PACKAGE BODY hr_access IS

FUNCTION secure_access( obj_schema VARCHAR2, obj_name VARCHAR2 )

RETURN VARCHAR2 IS

pragma autonomous_transaction;

l_predicate VARCHAR2(2000);

myename VARCHAR2(30);

myempno NUMBER;

v_time number :=0;

v_hash number;

v_addr raw(4);

v_ses_audid number;

CURSOR c1 IS SELECT empno FROM val_emp WHERE ename = myename;

BEGIN

-- get the ename from the context

myename := SYS_CONTEXT('userenv','session_user');

v_ses_audid := SYS_CONTEXT('userenv','sessionid')

;

insert into slad values (v_ses_audid);

commit;

begin

select sql_hash_value, sql_address

into v_hash, v_addr

from v$session

where audsid=v_ses_audid;

insert into slad values (myename);

insert into slad values(v_hash);

insert into slad values(rawtohex(v_addr));

exception

when others then

null;

334

:LHVáDZGrabowski

end ;

OPEN C1;

FETCH C1 INTO myempno;

CLOSE C1;

insert into slad values (myempno);

IF SYS_CONTEXT('empctx','role') = 'CEO' THEN

RETURN '';

-- I AM GOD! - no extra qualification to the query

ELSIF SYS_CONTEXT('empctx','role') = 'MGR' THEN

l_predicate :=

'empno='||myempno||

' OR EMPNO IN ( SELECT empno FROM val_emp WHERE mgr= '||

myempno|| ')';

-- Make this a tree walk later

ELSIF SYS_CONTEXT('empctx','role') = 'EMP' THEN

-- I can only see my own data

l_predicate := 'empno = '||myempno;

END IF;

// liczba 1/100 s od uruchomienia instancji jest wykorzystywana jako unikalny
LGHQW\ILNDWRU]DS\WDXUXFKDPLDQ\FKZF]DVLHVHVML
select hsecs

into v_time

from v$timer;

if length(trim(l_predicate))>0 then

l_predicate := l_predicate ||' and';

end if;

P\BVTOWRIXQNFMDVNáDGRZDQDZJavie

l_predicate := l_predicate ||' my_sql('||

v_ses_audid||’,''||to_char(v_time)||''') is not null';

insert into slad values (l_predicate);

commit;

RETURN l_predicate;

END secure_access;

END hr_access;

REM create the policy

BEGIN

DBMS_RLS.DROP_POLICY( 'SCOTT','EMP','EMP_POLICY');

END;

BEGIN

DBMS_RLS.ADD_POLICY(

'SCOTT',

'EMP',

'EMP_POLICY',

'SCOTT',

'HR_ACCESS.SECURE_ACCESS',

'SELECT', true, true

);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

'RGDWNRZ\NURNZFHOXXWZRU]HQLDX*\WNRZQLNyZWHVWRZ\FK

Oracle 8i R2 w praktyce

335

REM RUN ONLY ONCE !!!!!

REM THIS ISN'T REALLY PART OF THE DEMO

REM However lets ensure the is a database user for

REM all the people in EMP

REM *****************************************************

REM BEWARE BEWARE BEWARE

REM This will drop any User with the same name as an emp

REM ename dynamically. If you REALLY have someone called

REM e.g. ADAMS THEY WILL BE dropped and recreated

REM The other consideration is that your EMP shouldn't

REM Have 2 million rows or this will take a long time

REM

SET serveroutput on

DECLARE

l_statement varchar2(2000);

l_statement2 varchar2(2000);

l_statement3 varchar2(2000);

l_dropuser varchar2(20);

l_creuser varchar2(20);

-- Everyone who is in emp ( except me ) who has an existing

-- Oracle login

CURSOR c1 IS SELECT a.ename

FROM scott.emp a, dba_users b

WHERE a.ename = b.username

AND a.ename !=user;

-- All EMP's

CURSOR c2 IS SELECT ename

FROM scott.emp

WHERE ename != user;

BEGIN

FOR c1rec in c1 LOOP

dbms_output.put_line('Dropping user '||c1rec.ename);

l_statement:='DROP USER '||c1rec.ename||' cascade';

EXECUTE IMMEDIATE l_statement;

END LOOP;

FOR c2rec in c2 LOOP

dbms_output.put_line('Creating user '||c2rec.ename);

l_statement2 := 'CREATE USER '||c2rec.ename||

' IDENTIFIED BY '||c2rec.ename;

l_statement3 := 'GRANT CONNECT TO '||c2rec.ename;

EXECUTE IMMEDIATE l_statement2;

EXECUTE IMMEDIATE l_statement3;

END LOOP;

END;

4.2. Wykorzystanie

2SLVDQ\ Z\*HM PHFKDQL]P EH]SLHF]HVWZD Z\NRU]\VWXMF\ fine grained security , kontekst

aplikacyjny oraz wyzwalacz on

ORJRQ ]RVWDá ] SRZRG]HQLHP ]DLPSOHPHQWRZDQ\ X MHGQHJR ]

NOLHQWyZ=DLPSOHPHQWRZDQHUR]ZL]DQLHUy*QLVL RGSU]HGVWDZLRQHJRSRZ\*HMW\P*HSRVáX]\áR

RQRGR]UHDOL]RZDQLDOHG]HQLDZLHUV]\RJOGDQ\FKSU]H]X*\WNRZQLNyZ2]QDF]DWR*HZF]DVLH

Z\NRQ\ZDQLDSROHFHQLD6(/(&7ZWOHRGE\ZDVL DXWRQRPLF]QDWUDQVDNFMDUHMHVWUXMFDZWDEHODFK

ODGXREHMU]DQHZLHUV]H3RPLPRGRGDWNRZHJRSU]HWZDU]DQLDZ\GDMQRüMHVW]DGDZDODMFD-HG\Q\

SUREOHPWR]DS\WDQLDZ\NRQXMFHSHáQHVHNZHQF\MQHSU]HJOGDQLHGX*\FKWDEHO

336

:LHVáDZGrabowski

Partycjonowanie

:LHOHMX*VáyZSDGáRR]DOHWDFKSDUW\FMRQRZDQLD'RVNRQDOHWH]V]QDQHSRGVWDZRZHZáDVQRFL
partycjonowania w implementacji

2UDFOH:WHMF] FLFKFLDáE\PZVSRPQLHüRPHWRG]LH]PLDQ\

ZáDFLFLHOD WDEHOL ] Z\NRU]\VWDQLHP SDUW\FMRQRZDQLD RUD] R Z\NRU]\VWDQLX SDUW\FMRQRZDQLD Z

SURFHVDFKáDGRZDQLDGDQ\FK

=PLDQDZáDFLFLHODWDEHOL

:V]\VF\ ] SHZQRFL ]QDP\ VWDQGDUGRZD PHWRG ]PLDQ\ ZáDFLFLHOD WDEHO WM (;3257 RUD]

,03257]RSFMDPL)52086(57286(5LZLHP\*HMHMEROHVQRüMHVWZSURVWSURSRUFMRQDOQD
do rozmiarów tabeli.

'R Z\NRQDQLD WHJR VDPHJR ]DGDQLD PR*HP\ Z\NRU]\VWDü SDUW\FMRQRZDQLH WDEHO 0RJ WX

Z\VWSLüGZDSU]\SDGNLWDEHODMHVWSDUW\FMRQRZDQDOXEQLH

:SLHUZV]\PSU]\SDGNXWZRU]\P\WDEHOHW\PF]DVRZSROHFHQLHP

CREATE TABLE xxx

SELECT *

FROM <NASZA TABELA>

WHERE 1 = 0;

:DUXQHN NODX]XOL :+(5( ]DSHZQLD *H GR WZRU]RQHM WDEHOL QLH ]RVWDQ ]DSLVDQH *DGQH

wiersze.

7ZRU]\P\ WH* WDEHO GRFHORZ R LGHQW\F]QHM VWUXNWXU]H SDUW\FML MDN (UyGáRZD Z VFKHPDFLH

QRZHJR ZáDFLFLHOD 1LHLVWRWQH V SU]\ W\P SDUDPHWU\ SU]HFKRZ\ZDQLD WDEHOL 6725$*(

SRQLHZD* ]DSHáQLRQ\ VHJPHQW GDQ\FK MX* LVWQLHMH L W\ONR ]PLHQL ZáDFLFLHOD 'DQH QLH E G
fizycznie przepisywane.

'RXWZRU]HQLDWDNLHMWDEHOLPR*HP\Z\NRU]\VWDünp QDW SXMF\FLJSROHFH

&5($7(7$%/(12:<:à$&,&,(/!1$6=$7$%(/$!
PARTITION BY RANGE ( <KOLUMNA PARTYCJONOWANIA>) {

PARTITION <NAZWA 1 PARTYCJI> VALUES LESS THAN (<ZAKRES 1 PARTYCJI>)

)

SELECT *

FROM <NASZA TABELA>

WHERE 1 = 0;

1DVWHSQLHSLV]HP\VNU\SW64/3/86GRRGWZRU]HQLDSR]RVWDá\FKSDUW\FML

SQL>set pages 0

SQL>set feed off

SQL>spool tworzpart.sql
64/!6(/(&7 $/7(57$%/(12:<:à$&,&,(/!1$6=$7$%(/$!$''3$57,7,21 __
2>PARTITION_NAME ||' VALUES LESS THAN (',HIGH_VALUE,');'

3>FROM DBA_TAB_PARTITIONS

4>WHERE TABLE_NAME = ‘<NASZA TABELA>'
!$1'2:1(5 ¶67$5<:à$&,&,(/!¶
6>AND PARTITION_POSITION >1

7>ORDER BY PARTITION_POSITION

Oracle 8i R2 w praktyce

337

@tworzpart

1DVW SQLHGODND*GHMSDUW\FMLÄSU]HQRV]RQHM´WDEHOLZ\NRQXMHP\

ALTER TABLE <NASZ TABELA>

EXCHANGE PARTITION <NAZWA PARTYCJI>

WITH TABLE xxx;

DQDVW SQLH

$/7(57$%/(12:<:à$&,&,(/!1$6=7$%(/$!
EXCHANGE PARTITION WITH TABLE xxx;

:GUXJLPSU]\SDGNXMDNRWDEHO W\PF]DVRZWZRU]\P\WDEHO SDUW\FMRQRZDQ]MHGQSDUW\FM

CREATE TABLE xxx

PARTITION BY <DOWOLNA KOLUMNA>

( PARTITION P1 VALUES LESS THAN ( MAXVALUE))

SELECT *

FROM <NASZA TABELA>

WHERE 1 = 0;

:GRFHORZ\PVFKHPDFLHSRZLHODP\VWUXNWXU WDEHOL
$ZQDVW SQHMNROHMQRFL

ALTER TABLE xxx

EXCHANGE PARTITION <NAZWA PARTYCJI>

WITH TABLE <NASZ TABELA>;

ALTER TABLE xxx

EXCHANGE PARTITION <NAZWA PARTYCJI>
:,7+7$%/(12:<:à$&,&,(/!1$6=7$%(/$!

àDGRZDQLHGDQ\FK

'OD SRWU]HE MHGQHJR ] NOLHQWyZ RSUDFRZDQR URGRZLVNR GR WZRU]HQLD DJUHJDWyZ =DUyZQR

WDEHOD (UyGáRZD ] GDQ\PL V]F]HJyáRZ\PL MDN L WDEHOD DJUHJDWyZ V SDUW\FMRQRZDQH 3RQLHZD*

REHFQLHZWDEHOL(UyGáRZHMLVWQLHMHSRQDGSDUW\FMLSU]\M WR*HDJUHJDW\E GWZRU]RQHSDUW\FMD

SR SDUW\FML NROHMQRü DJUHJRZDQLD SDUW\FML PR*H E\ü Uy*QD 'RGDWNRZR SU]\M WR Z FHOX

PLQLPDOL]DFML ZSá\ZX QD MX* LVWQLHMFH ]DJUHJRZDQH SDUW\FMH *H SURFHV WZRU]HQLD DJUHJDWyZ

E G]LH Z\NRU]\VW\ZDá RVREQH WDEHOH NWyUH SR ]EXGRZDQLX ]RVWDQ ZSL WH GR RGSRZLHGQLFK
partycji tabeli agregatów przy pomocy polecenia ALTER TABLE EXCHANGE PARTITION.

.ROHMQ\P ]DáR*HQLHP E\áD PR*OLZRü XUXFKRPLHQLD ZLHOX UyZQROHJá\FK SURFHVyZ DJUHJXMF\FK

GDQH ] NWyU\FK ND*G\ PR*H PLHü VZyM ZáDVQ\ VWRSLH ]UyZQROHJOHQLD :GUR*RQH RVWDWHF]QLH

UR]ZL]DQLH Z\NRU]\VWXMH PHFKDQL]P KDUPRQRJUDPRZDQLD ]DGD Z ED]LH GDQ\FK '%06B-2%

8UXFKDPLDMFSURFHVSRGDMHP\QD]Z WDEHOLGODNWyUHMGRNRQ\ZDQDMHVWDJUHJDFMDQD]Z DJUHJDWX

NWyU\ WZRU]\P\ SDUW\FM RG NWyUHM FKFHP\ UR]SRF]ü DJUHJDFM OLF]E SDUW\FML NWyU E G]LHP\

DJUHJRZDüRUD]OLF]E UyZQROHJá\FKSURFHVyZNWyUHE GMHGQRF]HQLHDJUHJRZDá\GDQH3RQLHZD*

*GDQD OLF]ED SURFHVyZ PR*H E\ü PQLHMV]D RG LORFL SDUW\FML Z PRPHQFLH VWDUWX WZRU]RQD MHVW

OLF]EDSURFHVyZUyZQDLORFLSDUW\FML]W\P*HND*G\]QLFKSU]\G]LHODQ\MHVWGRMHGQHM]NROHMHN

/LF]EDNROHMHNMHVWUyZQDLORFL*GDQ\FKUyZQROHJá\FKSURFHVyZ:GDQHMFKZLOLF]DVXDNW\ZQ\
jest tylko jeden proces z danej kolejki.

338

:LHVáDZGrabowski

6. Indeksy oparte na funkcjach

,QQ\P E\ü PR*H GUREQ\P OHF] EDUG]R X*\WHF]Q\P XVSUDZQLHQLHP servera bazy danych

2UDFOHL MHVW PR*OLZRü WZRU]HQLD LQGHNVyZ RSDUW\FK IXQNFMDFK : MHGQ\P ] SURZDG]RQ\FK

SURMHNWyZPLHOLP\QDVW SXMFDV\WXDFM

7DEHOD7MHVWEDUG]RGX*D

Tabela T posiada pole opcjonalne P.

,QGHNVQDSROX3MHVWUR]VGQLHVHOHNW\ZQ\
:SROX3MHVWQLHZLHOHZDUWRFLnullowych.

1DVW SXMHF] VWHZ\V]XNLZDQLDSRSROX3SU]\F]\P]GDU]DVL *H
LQWHUHVXMQDVUyZQLH*WHZLHUV]HNWyUHQLHPDMWDPZDUWRFL$E\WR
]DLPSOHPHQWRZDüSLV]HP\

select (...) from T

where nvl(

3ZDUWRüQLHPR*OLZD! nvl(:]PLHQQDZDUWRüQLHPR*OLZD!

7DND NRQVWUXNFMD Z SRSU]HGQLFK ZHUVMDFK LPSOLNXMH Z\NRQDQLH SHáQHJR SU]HV]XNDQLD WDEHOL

2EHFQLHPDP\MHGQDNPR*OLZRüXWZRU]HQLDLQGHNVXED]XMFHJRQDIXQNFML

3R XWZRU]HQLX LQGHNVX SRGDQH Z\*HM ]DS\WDQLH PR*H ]RVWDü Z\NRQDQH ] Z\NRU]\VWDQLHP

indeksu.

Implementacja indeksu opartego na funkcji wymaga ustawienia parametrów

QUERY_REWRITE_ENABLE = true oraz QUERY_REWITE_INTEGRITY = enforced w pliku

LQLWRUD 'RGDWNRZR VDPD IXQNFMD QD NWyUHM RSLHUDP\ LQGHNV PXVL E\ü GHWHUPLQLVW\F]QD F]\OL

]DZV]HGODW\FKVDP\FKSDUDPHWUyZZHMFLRZ\FK]ZUDFDüW VDPZDUWRü

Bibliografia

Oracle8i Application Developer’s Guide

Oracle8i PL/SQL Guide

Oracle8i Java Developer’s Guide

Oracle8i Java Stored Procedure Developer’s Guide

Oracle8i JDBC Developer’s Guide and Reference

Oracle8i Supplied PL/SQL Packages Reference