What Are a Table Column's Privileges? in JDBC

To retrieve a description of a given table column’s privileges—that is, its access rights—you can use the DatabaseMetaData interface’s getColumnPrivileges() method. This method returns a list of columns and associated privileges for the specified table. The getColumnPrivileges() method returns the result as a ResultSet, where each row is a column privilege description. In production applications, returning the result as a ResultSet is not that useful. It is better to return the result as XML so that the client application can extract the required information and display it in a desired fashion. Note that this privilege does not apply to all columns—this may be true for some systems, but it is not true for all.

The getColumnPrivileges() method retrieves a description of the access rights for a table’s columns. Only privileges matching the column name criteria are returned. They are ordered by COLUMN_NAME and PRIVILEGE.

Each privilege description has the columns shown in Table .

Table . Columns for Result of getColumnPrivileges()

getColumnPrivileges

public ResultSet getColumnPrivileges(String catalog,
String schema,
String table,
String pattern)
throws SQLException.

Here are the parameters for the getColumnPrivileges() method:

  • catalog: A catalog name; it must match the catalog name as it is stored in the database."" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search.
  • schema: A schema name; it must match the schema name as it is stored in the database. "" retrieves those without a schema; null means that the schema name should not be used to narrow the search.
  • table: A table name; it must match the table name as it is stored in the database.
  • pattern: A column name pattern; it must match the column name as it is stored in the database. getColumnPrivileges returns a ResultSet, where each row is a column privilege description. It throws a SQLException if a database access error occurs.

The Solution: Get Table Column Privileges

/**
* Get Table Column Privileges: retrieves a description
* of the access rights for a table's columns available in
* a catalog. 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
* COLUMN_NAME String => column 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 catalog a catalog.
* @param schema a schema.
* @param tableName a table name; must match
* the table name as it is stored in the database .
* @param columnNamePattern a column name pattern.
* @return an XML.
* @exception Failed to get the Get Table Column Privileges.
*/
public static String getColumnPrivileges(java.sql.Connection conn,
String catalog,
String schema,
String tableName,
String columnNamePattern)
throws Exception {
ResultSet privileges = null;
StringBuffer sb = new StringBuffer();
try {
if ((tableName == null) ||
(tableName.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.
// NOTE: if you pass a null to schema/tableName, then you might get
// an exception or you might get an empty ResultSet object
privileges = meta.getColumnPrivileges(catalog,
schema,
tableName,
columnNamePattern);
sb.append("<privileges>");
while (privileges.next()) {
String dbCatalog = privileges.getString(COLUMN_NAME_TABLE_CATALOG);
String dbSchema = privileges.getString(COLUMN_NAME_TABLE_SCHEMA);
String dbTable = privileges.getString(COLUMN_NAME_TABLE_NAME);
String dbColumn = privileges.getString(COLUMN_NAME_COLUMN_NAME);
String dbPrivilege = privileges.getString(COLUMN_NAME_PRIVILEGE);
String dbGrantor = privileges.getString(COLUMN_NAME_GRANTOR);
String dbGrantee = privileges.getString(COLUMN_NAME_GRANTEE);
String dbIsGrantable = privileges.getString(COLUMN_NAME_IS_GRANTABLE);
sb.append("<column name=\"");
sb.append(dbColumn);
sb.append("\" table=\"");
sb.append(tableName);
sb.append("\"><catalog>");
sb.append(dbCatalog);
sb.append("</catalog><schema>");
sb.append(dbSchema);
sb.append("</schema><privilege>");
sb.append(dbPrivilege);
sb.append("</privilege><grantor>");
sb.append(dbGrantor);
sb.append("</grantor><isGrantable>");
sb.append(dbIsGrantable);
sb.append("</isGrantable><grantee>");
sb.append(dbGrantee);
sb.append("</grantee></column>");
}
sb.append("</privileges>");
return sb.toString();
}
catch(Exception e) {
hrow new Exception("Error: could not get table column privileges: "+
e.toString());

finally {
DatabaseUtil.close(privileges);
}
}

Oracle:Test the Solution: Get Table Column Privileges

I tested getColumnPrivileges() for several Oracle tables, but I could not get any results (it seems that the Oracle driver does not support this feature at all).

The client call looks like this:

String columnPrivileges = DatabaseMetaDataTool.getColumnPrivileges
(conn, // connection
conn.getCatalog(), // catalog
"SYSTEM", // schema
"HELP", // the help table
"%");
System.out.println("---- Table's Columns Privileges ----");
System.out.println(columnPrivileges);
System.out.println("------------------------------------");
The output is
---- Table Column Privileges ----
<privileges>
</privileges>
------------------------------------

MySQL: Test the Solution: Get Table Column Privileges

The client call looks like this:
String columnPrivileges = DatabaseMetaDataTool.getColumnPrivileges
(conn, // connection
conn.getCatalog(), // catalog
null, // schema
"artist",
"%");
System.out.println("---- Table Column Privileges ----");
System.out.println(columnPrivileges);
System.out.println("------------------------------------");

The output is

---- Table Column Privileges ----
<privileges>
<column name="ARTIST_ID" table="artist">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>SELECT</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</column>
<column name="ARTIST_NAME" table="artist">
<catalog>octopus</catalog>
<schema>null</schema>
<privilege>SELECT</privilege>
<grantor>[email protected]</grantor>
<isGrantable>null</isGrantable>
<grantee>%@%</grantee>
</column>
</privileges>
------------------------------------


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

JDBC Topics