Database Reference
In-Depth Information
The specification of the package we will create is as follows:
EODA@ORA12CR1> create or replace package unloader
2 AUTHID CURRENT_USER
3 as
4 /* Function run -- unloads data from any query into a file
5 and creates a control file to reload that
6 data into another table
7
8 p_query = SQL query to "unload". May be virtually any query.
9 p_tname = Table to load into. Will be put into control file.
10 p_mode = REPLACE|APPEND|TRUNCATE -- how to reload the data
11 p_dir = directory we will write the ctl and dat file to.
12 p_filename = name of file to write to. I will add .ctl and .dat
13 to this name
14 p_separator = field delimiter. I default this to a comma.
15 p_enclosure = what each field will be wrapped in
16 p_terminator = end of line character. We use this so we can unload
17 and reload data with newlines in it. I default to
18 "|\n" (a pipe and a newline together) and "|\r\n" on NT.
19 You need only to override this if you believe your
20 data will have that default sequence of characters in it.
21 I ALWAYS add the OS "end of line" marker to this sequence, you should not
22 */
23 function run( p_query in varchar2,
24 p_tname in varchar2,
25 p_mode in varchar2 default 'REPLACE',
26 p_dir in varchar2,
27 p_filename in varchar2,
28 p_separator in varchar2 default ',',
29 p_enclosure in varchar2 default '"',
30 p_terminator in varchar2 default '|' )
31 return number;
32 end;
33 /
Package created.
Note the use of
AUTHID CURRENT_USER
. This permits this package to be installed
once
in a database and used
by anyone to unload data. All the person needs is
SELECT
privileges on the table(s) he wants to unload and
EXECUTE
privileges on this package. If we did not use
AUTHID CURRENT_USER
in this case, then the owner of this package would
need direct
SELECT
privileges on all tables to be unloaded. Additionally, since this routine would be completely
subject to “SQL Injection” attacks (it takes an arbitrary SQL statement as input), the
CREATE
statement must specify
AUTHID CURRENT_USER
. If it was a default definer's rights routine, anyone with
EXECUTE
on it would be able to execute
any
SQL statement using the owner's permissions. If you know a routine is SQL Injectable, it had better be an
invoker's rights routine!
■
the SQL will execute with the privileges of the invoker of this routine. however, all pL/SQL calls will run with
the privileges of the
definer
of the called routine; therefore, the ability to use
UTL_FILE
to write to a directory is implicitly
given to anyone with execute permission on this package.
Note
