本文介绍如何在Oracle数据库中使用Java存储过程，包括角色权限管理、类型定义、Java源代码实现文件目录列表功能，并展示了如何调用及查询结果。
我们现在把JAVASYSPRIV和JAVAUSERPRIV给revoke掉：
SQL> revoke JAVASYSPRIV from VMITH;
Revoke succeeded.
SQL> revoke JAVAUSERPRIV from VMITH;
Revoke succeeded.
SQL> GRANT JAVASYSPRIV to vmith;
1、JAVASYSPRIV（JAVA系统权限）包含JAVAUSERPRIV（JAVA用户权限）。
2、要想正常的执行java存储过程，只需要JAVASYSPRIV这个role就可以了。
3、在赋予JAVASYSPRIV这个role后，如果要马上看到效果，需要断开当前session、再重新连接一下。
4、oracle存储过程的默认authid为definer，这种情况下oracle是看不到role里的权限的，但是从上面结果里我们可以看到对java存储过程，没有这个限制。
create or replace type dir_entry as object (
file_type varchar2(1),
readable varchar2(1),
writeable varchar2(1),
hidden varchar2(1),
file_size number,
modified date,
name varchar2(4000)
);
/
create or replace type dir_array as table of dir_entry;
/
create or replace and compile java source named "Util" as
import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;
import oracle.jdbc.driver.OracleDriver;
public class Util {
private static Connection conn;
static {
try {
conn = (new OracleDriver()).defaultConnection();
} catch (SQLException e) {
System.out.println(e);
}
}
/**
* List the files in the directory represented by the given Oracle DIRECTORY
* object.
*
* @param dirname The name of the DIRECTORY object for which we want to list
* the files (case sensitive).
* @throws IOException
* @throws SQLException
*/
public static ARRAY listFiles(String dirname)
throws IOException, SQLException {
String dirpath = getDirectoryPath(dirname);
File directory = getDirectory(dirpath);
STRUCT[] ret = fileList(directory);
// Create an array descriptor and return it.
ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
"DIR_ARRAY", conn);
return new ARRAY(desc, conn, ret);
}
/**
* Create a File object with the abstract pathname given by the parameter.
*
* @param dirpath The filesystem path of the directory
* @throws IOException If the directory represented by this pathname does
* not exist, or if it is a file.
*/
private static File getDirectory(String dirpath) throws IOException {
File directory = new File(dirpath);
if(!directory.exists()) {
throw new IOException("Directory: "+dirpath+" does not exist.");
}
if(!directory.isDirectory()) {
throw new IOException("Path: "+dirpath+" is not a directory.");
}
return directory;
}
/**
* Get the filesystem path for the Oracle DIRECTORY object given by the
* input parameter.
*
* @param dir The name of the DIRECTORY object for which we want the path.
* @throws IOException If there is no DIRECTORY object with the given name.
*/
private static String getDirectoryPath(String dir)
throws SQLException, IOException {
String sql = "select directory_path from all_directories where " +
"directory_name = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setString(1, dir);
ResultSet rs = s.executeQuery();
// There should be one row and one only.
if(rs.next()) {
return rs.getString(1);
} else {
throw new IOException("Directory object "+dir+" does not exist.");
}
}
/**
* Create an array of STRUCT objects representing the files in the given
* directory.
*
* @param directory The File object representing the directory.
* @throws SQLException
*/
private static STRUCT[] fileList(File directory) throws SQLException {
// Create the array of files to add.
File[] files = directory.listFiles (
new FilenameFilter() {
// Accept all files
public boolean accept(File dirpath, String name) {
return true;
}
}
);
// No files in directory
if(files == null) {
return null;
}
STRUCT[] ret = new STRUCT[files.length];
// Create the struct entry for each file.
for(int i=0; i<files.length; i++) {
File f = files;
StructDescriptor sd = StructDescriptor.createDescriptor (
"DIR_ENTRY", conn);
Object[] attributes = {
f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
f.canRead() ? "Y" : "N",
f.canWrite() ? "Y" : "N",
f.isHidden() ? "Y" : "N",
new Long(f.length()),
new Timestamp(f.lastModified()),
f.getName()
};
STRUCT s = new STRUCT(sd, conn, attributes);
ret = s;
}
return ret;
}
}
/
create or replace package util as
function ls(p_dirname in varchar2) return dir_array;
end;
/
create or replace package body util as
function ls(p_dirname in varchar2) return dir_array is
language java
name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
end;
/
Then you can run queries like the following:
最后查询得到结果：
select * from table(util.ls('UCE_DIR'))
1 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081020124144.txt
2 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081020124242.txt
3 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081020135737.txt
4 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081020135759.txt
5 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS2008102014515.txt
6 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081124101331.txt
7 F Y Y N 1439 2008/11/20 12:43:08 PICKTRS20081124101339.txt
8 F Y Y N 3004 2009/05/11 14:37:10 test.txt
9 F Y Y N 2817 2009/05/11 18:30:21 test007.txt
10 F Y Y N 1171 2009/05/08 18:31:27 test1.txt
11 F Y Y N 1603 2009/05/08 17:18:57 test2.txt
12 F Y Y N 33 2008/11/17 17:54:05 test20081017184440.txt
13 F Y Y N 1604 2009/05/11 13:51:27 test3.txt
*******************************************************************
举个例子说明：
比如有两个用户test和user1，test用户下有个表user1table，用户user1只有这个表的select权限。test创建了一个存储过程testproc，使用默认的authid调用方式，存储过程内容为“insert into user1table ....”，然后使用grant execute on testproc to user1将执行权限赋予user1，那么user1就可以调用这个存储过程向user1table添加数据。注意：此时user1并没有对表user1table的insert权限，但由于testproc执行的时候，是根据test用户的权限来验证的，因此insert语句不会出现权限不足的提示。
假如创建testproc是添加了authid current_user，则当user1调用test.testproc存储过程时，就会报两个错误：一是user1table未定义，二是对user1table没有insert权限。对于第一个问题，原因是user1table仅在test用户模式下，user1用户不能直接引用，可以在定义testproc时使用test.user1table方式；对于第二个问题，由于user1没有对user1table的insert权限，因此无法执行这个存储过程，除非给user1用户增加user1table表的insert权限。
扫一扫
1720
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
