What Are a Table Column's Privileges? 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()

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>
------------------------------------


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

JDBC Topics