Access rules can use user-defined Java functions. For example, you can use Java functions to write sophisticated rules using the profile of the application, the user logged in to the application, and the roles that the user is currently assigned for the application.
The following Java class uses the method GetSecVal to demonstrate how you can use Java methods that use JDBC as user-defined functions inside access rules:
import java.sql.*;
import java.util.*;
public class sec_class {
static String _url = "jdbc:sybase:asejdbc";
public static int GetSecVal(int c1)
{
try
{
PreparedStatement pstmt;
ResultSet rs = null;
Connection con = null;
int pno_val;
pstmt = null;
Class.forName("sybase.asejdbc.ASEDriver");
con = DriverManager.getConnection(_url);
if (con == null)
{
return (-1);
}
pstmt = con.prepareStatement("select classification from sec_tab where id = ?");
if (pstmt == null)
{
return (-1);
}
pstmt.setInt(1, c1);
rs = pstmt.executeQuery();
rs.next();
pno_val = rs.getInt(1);
rs.close();
pstmt.close();
con.close();
return (pno_val);
}
catch (SQLException sqe)
{
return(sqe.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println("Unexpected exception : " + e.toString());
System.out.println("\nThis error usually indicates that " + "your Java CLASSPATH environment has not been set properly.");
e.printStackTrace();
return (-1);
}
catch (Exception e)
{
System.out.println("Unexpected exception : " + e.toString());
e.printStackTrace();
return (-1);
}
}
}
import java.sql.*;
import java.util.*;
public class sec_class {
static String _url = "jdbc:sybase:asejdbc";
public static int GetSecVal(int c1)
{
try
{
PreparedStatement pstmt;
ResultSet rs = null;
Connection con = null;
int pno_val;
pstmt = null;
Class.forName("sybase.asejdbc.ASEDriver");
con = DriverManager.getConnection(_url);
if (con == null)
{
return (-1);
}
pstmt = con.prepareStatement("select classification from sec_tab where id = ?");
if (pstmt == null)
{
return (-1);
}
pstmt.setInt(1, c1);
rs = pstmt.executeQuery();
rs.next();
pno_val = rs.getInt(1);
rs.close();
pstmt.close();
con.close();
return (pno_val);
}
catch (SQLException sqe)
{
return(sqe.getErrorCode());
}
catch (ClassNotFoundException e)
{
System.out.println("Unexpected exception : " + e.toString());
System.out.println("\nThis error usually indicates that " + "your Java CLASSPATH environment has not been set properly.");
e.printStackTrace();
return (-1);
}
catch (Exception e)
{
System.out.println("Unexpected exception : " + e.toString());
e.printStackTrace();
return (-1);
}
}
}
After compiling the Java code, you can run the same program from isql, as follows.
For example:
javac sec_class.java jar cufo sec_class. jar sec_class.class installjava -Usa -Password -f/work/work/FGAC/sec_class.jar - -D testdb
From isql:
/*to create new user datatype class_level*/
sp_addtype class_level, int
/*to create the sample secure data table*/
create table sec_data (c1 varchar(30),
c2 varchar(30),
c3 varchar(30),
clevel class_level)
/*to create the classification table for each user*/
create table sec_tab (userid int, clevel class-level int)
insert into sec_tab values (1,10)
insert into sec_tab values (2,9)
insert into sec_tab values (3,7)
insert into sec_tab values (4,7)
insert into sec_tab values (5,4)
insert into sec_tab values (6,4)
insert into sec_tab values (7,4)
declare @v1 int
select @v1 = 5
while @v1 > 0
begin
insert into sec_data values('8', 'aaaaaaaaaa', 'aaaaaaaaaa', 8)
insert into sec_data values('7', 'aaaaaaaaaa', 'aaaaaaaaaa', 7)
insert into sec_data values('5', 'aaaaaaaaaa', 'aaaaaaaaaa', 5)
insert into sec_data values('5', 'aaaaaaaaaa', 'aaaaaaaaaa', 5)
insert into sec_data values('2', 'aaaaaaaaaa', 'aaaaaaaaaa', 2)
insert into sec_data values('3', 'aaaaaaaaaa', 'aaaaaaaaaa', 3)
select @v1 = @v1 -1
end
go
create access rule clevel_rule
@clevel <= sec_class.GetSecVal(suser_id())
go
create default clevel_def as sec_class.GetSecVal(suser_id())
go
sp_bindefault clevel_def, class_level
go
sp_bindrule clevel, class_level
go
grant all on sec_data to public
go
grant all on sec_tab to public
go
