<< The 08006 exception | Select and insert privileges >>

Create, update, and query table

Derby Developer's Guide

148

"ERROR: Unexpectedly allowed to connect to database " +
dbName);
cleanUpAndShutDown(conn);
} catch (SQLException e) {
if (e.getSQLState().equals("08004")) {
System.out.println("Correct behavior: SQLException: " +
e.getMessage());
} else {
errorPrintAndExit(e);
}
}

// Log in as a user with read-only access
try {
// connection should succeed, but create table should fail
String newURL = connectionURL +
";user=guest;password=java5w6x";
System.out.println("Trying to connect to " + newURL);
conn = DriverManager.getConnection(newURL);
System.out.println("Connected to database " + dbName +
" with read-only access");

Statement s = conn.createStatement();
s.executeUpdate(
"CREATE TABLE accessibletbl(textcol VARCHAR(6))");
System.out.println(
"ERROR: Unexpectedly allowed to modify database " +
dbName);
cleanUpAndShutDown(conn);
} catch (SQLException e) {
if (e.getSQLState().equals("25503")) {
System.out.println("Correct behavior: SQLException: " +
e.getMessage());
try {
conn.close();
} catch (SQLException ee) {
errorPrintAndExit(ee);
}
} else {
errorPrintAndExit(e);
}
}

// Log in as a user with full access
// Create, update, and query table
// Grant select and insert privileges to another user
try {
// this should succeed
String newURL = connectionURL +
";user=mary;password=little7xylamb";
System.out.println("Trying to connect to " + newURL);
conn = DriverManager.getConnection(newURL);
System.out.println("Connected to database " + dbName);

Statement s = conn.createStatement();

s.executeUpdate(
"CREATE TABLE accessibletbl(textcol VARCHAR(6))");
System.out.println("Created table accessibletbl");
s.executeUpdate("INSERT INTO accessibletbl VALUES('hello')");

ResultSet rs = s.executeQuery("SELECT * FROM accessibletbl");
rs.next();
System.out.println("Value of accessibletbl/textcol is " +
rs.getString(1));

// grant insert privileges on table to user sqlsam
s.executeUpdate(
"GRANT SELECT, INSERT ON accessibletbl TO sqlsam");
System.out.println(