What Are a Table's Privileges? in JDBC

A database table’s privileges refer to finding a description of the access rights for each table available in a catalog or schema. DatabaseMetaData provides a method, getTablePrivileges(), to do just that. This method returns the result as a ResultSet where each row is a table privilege description. In production applications, returning the result as a ResultSet is not quite useful. It is better to return the result as an XML object so that the client can extract the required information and display it in a desired format. It would be wrong to assume that this privilege applies to all columns; while this may be true for some systems, it is not true for all.

getTablePrivileges() returns only privileges that match the schema and table name criteria. They are ordered by TABLE_SCHEM, TABLE_NAME, and PRIVILEGE. Each privilege description has the columns shown in Table.

Table. Columns for Result of getTablePrivileges()

The getTablePrivileges() method has the following signature:
public java.sql.ResultSet getTablePrivileges(String catalog,
String schemaPattern,
String tableNamePattern)
throws java.sql.SQLException

where

  • catalog: A catalog name; "" retrieves those without a catalog.
  • schemaPattern: A schema name pattern; "" retrieves those without a schema.
  • tableNamePattern: A table name pattern.

The Solution: Get Table Privileges

/**
* Get Table Privileges: retrieves a description of the access
* rights for each table available in a catalog. Note that a
* table privilege applies to one or more columns in the table.
* It would be wrong to assume that this privilege applies to
* all columns (this may be true for some systems but is not
* true for all.) The result is returned as XML (as a string
* object); if table name is null/empty it returns null.
*
* In JDBC, Each privilege description has the following columns:
*
* TABLE_CAT String => table catalog (may be null)
* TABLE_SCHEM String => table schema (may be null)
* TABLE_NAME String => table name
* GRANTOR => grantor of access (may be null)
* GRANTEE String => grantee of access
* PRIVILEGE String => name of access (SELECT, INSERT,
* UPDATE, REFERENCES, ...)
* IS_GRANTABLE String => "YES" if grantee is permitted to grant
* to others; "NO" if not; null if unknown
*
*
* @param conn the Connection object
* @param catalogPattern a catalog pattern.
* @param schemaPattern a schema pattern.
* @param tableNamePattern a table name pattern; must match
* the table name as it is stored in the database .
* @return an XML.
* @exception Failed to get the Get Table Privileges.
*/
public static String getTablePrivileges(java.sql.Connection conn,
String catalogPattern,
String schemaPattern,
String tableNamePattern)
throws Exception {
ResultSet privileges = null;
StringBuffer sb = new StringBuffer();
try {
if ((tableNamePattern == null) ||
(tableNamePattern.length() == 0)) {
return null;
} DatabaseMetaData meta = conn.getMetaData();
if (meta == null) {
return null;
}
// The '_' character represents any single character.
// The '%' character represents any sequence of zero
// or more characters.
privileges = meta.getTablePrivileges(catalogPattern,
schemaPattern,
tableNamePattern);
sb.append("<privileges>");
while (privileges.next()) {
String catalog = privileges.getString(COLUMN_NAME_TABLE_CATALOG);
String schema = privileges.getString(COLUMN_NAME_TABLE_SCHEMA);
String tableName = privileges.getString(COLUMN_NAME_TABLE_NAME);
String privilege = privileges.getString(COLUMN_NAME_PRIVILEGE);
String grantor = privileges.getString(COLUMN_NAME_GRANTOR);
String grantee = privileges.getString(COLUMN_NAME_GRANTEE);
String isGrantable = privileges.getString(COLUMN_NAME_IS_GRANTABLE);
sb.append("<table name=\"");
sb.append(tableName);
sb.append("\"><catalog>");
sb.append(catalog);
sb.append("</catalog><schema>");
sb.append(schema);
sb.append("</schema><privilege>");
sb.append(privilege);
sb.append("</privilege><grantor>");
sb.append(grantor);
sb.append("</grantor><isGrantable>");
sb.append(isGrantable);
sb.append("</isGrantable><grantee>");
sb.append(grantee);
sb.append("</grantee></table>");
}
sb.append("</privileges>");
return sb.toString();
}
catch(Exception e) {
throw new Exception("Error: could not get table privileges:"+
e.toString());
}
finally {
DatabaseUtil.close(privileges);
}
}

Oracle: Client Call: Get Table Privileges

String tablePrivileges = DatabaseMetaDataTool.getTablePrivileges
(conn, // connection
conn.getCatalog(), // catalog
"%", // schema
"EMP%"); // table name pattern
System.out.println("-------- TablePrivileges -------------");
System.out.println(tablePrivileges);
System.out.println("------------------------------------");

And here’s the output:

-------- TablePrivileges -------------
<privileges>
<table name="EMPLOYEE_PHOTOS">
<catalog>null</catalog>
<schema>SYS</schema>
<privilege>READ</privilege>
<grantor>SYS</grantor>
<isGrantable>YES</isGrantable>
<grantee>OCTOPUS</grantee>
</table>
<table name="EMPLOYEE_PHOTOS">
<catalog>null</catalog>
<schema>SYS</schema>
<privilege>WRITE</privilege>
<grantor>SYS</grantor>
<isGrantable>YES</isGrantable>
<grantee>OCTOPUS</grantee>
</table>
</privileges>
-----------------------------------

MySQL: Client Call: Get Table Privileges

The MySQL database stores table privileges in the tables_priv table. Here is the description of that table (note that the MySQL database uses the mysql database to manage users and privileges):

mysql> use mysql;
mysql> desc tables_priv;

Client Call

String tablePrivileges = DatabaseMetaDataTool.getTablePrivileges
(conn, // connection
conn.getCatalog(), // catalog
null, //"%", // schema
"%");
System.out.println("-------- TablePrivileges -------------");
System.out.println(tablePrivileges);
System.out.println("------------------------------------");

Output

-------- TablePrivileges -------------
<privileges>
<table name="artist">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>SELECT</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</table>
<table name="artist_exhibit">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>SELECT</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</table>
<table name="artist_exhibit">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>INSERT</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</table>
<table name="artist_exhibit">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>DROP</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</table>
</privileges>
------------------------------------


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics