printing with Oracle PL/SQL utility UTL_FILE, using /etc/lp files

[Stuart Abramson_2]

One of our users is trying to use the /etc/lp printer definition files to print from Oracle PL/SQL using the UTL_FILE utility:


/etc/lp/cinterface/wablxm001 Cancel interface
/etc/lp/interface/wablxm001 Interface script (210 lines)
/etc/lp/member/wablxm001 device file name
/etc/lp/sinterface/wablxm001 Status interface

He says that UTL_FILE is designed to use these files.

I don't want to have to support this. I told him to format the file and just:

lp -d wablxm001 file_name

Is he correct here? Can UTL_FILE use these /etc/lp files?

[Stuart Abramson_2]

What should the correct permissions for these files be? Ours are executable by users:

# find /etc -name wablxm001 -exec ll {} \;

-rwxr-xr-x 1 lp lp 112 Jul 2 09:30 /etc/lp/cinterface/wablxm001
-rwxr-xr-x 1 lp lp 5263 Jul 2 09:30 /etc/lp/interface/wablxm001
-rw-r--r-- 1 lp lp 10 Jul 2 09:30 /etc/lp/member/wablxm001
-rwxr-xr-x 1 lp lp 112 Jul 2 09:30 /etc/lp/sinterface/wablxm001

[A. Clay Stephenson]

This is Looney Tunes. The interface files are for the EXCLUSIVE use of the lp system and are never intended for use with applications. An interface file expects 7 args:
1) printer name
2) id - lp spooler request id
3) user
4) title
5) number of copies
6) options (the -o stuff)
7) name of file

Applications use terminfo, termcap, and/or printcap (if available) to handle printer specific stuff. A few applications have other methods using their own printer definition files but output formatting in UNIX is the sole responsibility of the application.

Your instructions about using lp (possibly with some custom -o options added to an existing interface) is the correct method.

If you need a bit more ammunition consider this problem: Lp is currently printing to a parallel or directly attached printer. Your user then starts sending to the same device - it's perfectly legal. You now have output data interleaved from two sources which makes for some rather tricky reading by us dumb humans. This normally wouldn't happen with network printers because they block until the port is agian free.

[Graham Cameron_1]

To echo what Clay has said.
UTL_FILE is not designed to write direct to interface files. Absolutely not.

It is to write (and read) flat files.
The only directories which are accessible to the UTL_FILE package are those specified in the UTL_FILE_DIR parameter in the init.ora file (or SPFILE if you're on 9i).

This parameter (there can be several entries) should be used to clamp down access to just a few, controlled directories, and not to system files such as your user suggests.

If you have
UTL_FILE_DIR = *
then you are asking for trouble, Oracle spefically recommend against this.

I see from the 9i docs that Oracle now recommend the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#ARPLS069

Whatever, you cannot and should not allow access to your system files except through the supported interface (in this case the lp system).

-- Graham

[Yogeeraj_1]

hi,

If you want to execute a script from procedure PLSQL, here is how to do this in java.

STEP1: granting some priviledges.
here, to demonstrate the execution of the program /usr/bin/ps, hence only granting as little as possible.
As SYS or some appropriately priveleged user, we need to execute:

begin
dbms_java.grant_permission
('YD_TEST',
'java.io.FilePermission',
'/usr/bin/ps',
'execute');

dbms_java.grant_permission
('YD_TEST',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/

That allows user YD_TEST to successfully execute that program. We could have allowed it to execute /usr/bin/* or * or whatever -- Here it is restricted to only one program.

STEP2: Now, YD_TEST would create in its schema:

create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;

public class Util extends Object
{
public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args[0]);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];

// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);

rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

create or replace
function RUN_CMD( p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String[]) return integer';
/


STEP3: To make this callable as a procedure (ignoring the return code), we need to create a procedure:

create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/


STEP4: And now to run it:
set serveroutput on size 1000000
exec dbms_java.set_output(1000000)
exec rc('/usr/bin/ps -ef');


Hope this helps!

regards
Yogeeraj