/**********************************************************************
* FILENAME : ListTablePrivileges.c
*
* DESCRIPTION :
* Example finds tables starting with 'TestTBL' and for each one
* outputs the table privileges.
*
* ODBC USAGE :
* SQLGetInfo() to get the DB and USER names
* SQLBindCol and SQLTables to obtain list of tables like 'TestTBL'
* For each table
* SQLTablePrivileges() to get table privileges
* SQLBindCol() to bind the 7 columns of the result set (those
* being Catalog, Schema, Table, Grantor, Grantee, Privilege
* and Is Grantable)
* SQLFetch() to get the values
* Display
*/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include "util.c"
#define STR_LEN 128 + 1
#define REM_LEN 254 + 1
// Arbitary sizes for number of tables and
#define MAX_TABLES 100
// Number of columns in SQLTables
#define NUMCOLS 5
// Declare buffers for result set data
SQLCHAR strCatalog[STR_LEN];
SQLCHAR strSchema[STR_LEN];
SQLCHAR strTableName[STR_LEN];
SQLCHAR strGrantor[STR_LEN];
SQLCHAR strGrantee[REM_LEN];
SQLCHAR strPrivilege[STR_LEN];
SQLCHAR strIsGrantable[STR_LEN];
SQLINTEGER BufferLength;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenGrantor;
SQLLEN lenGrantee;
SQLLEN lenPrivilege;
SQLLEN lenIsGrantable;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
void Cleanup(SQLHSTMT henv, SQLHSTMT hdbc, SQLHSTMT hstmt) {
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// Gets list of tables based on iTableStrPtr
int getTheseTables (char *pTableName, SQLCHAR *tableNames[]) {
int bufferSize = 1024, i, j, count = 1, numCols = 5;
SQLCHAR * dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
SQLCHAR * userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
SQLCHAR connStrbuffer[1024];
SQLSMALLINT connStrBufferLen, bufferLen;
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
struct DataBinding* catalogResult = (struct DataBinding*)
malloc( numCols * sizeof(struct DataBinding) );
SQLCHAR* selectAllQuery = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
// Connect to database
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLCHAR *)(void*)SQL_OV_ODBC3, -1);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT,
(SQLPOINTER)10, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Display the database information
retcode = SQLGetInfo(hdbc, SQL_DATABASE_NAME, dbName,
(SQLSMALLINT)bufferSize,
(SQLSMALLINT *)&bufferLen);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_DATABASE_NAME)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLGetInfo(hdbc, SQL_USER_NAME, userName,
(SQLSMALLINT)bufferSize,
&bufferLen);
CHECK_ERROR(retcode, "SQLGetInfo(SQL_USER_NAME)",
hdbc, SQL_HANDLE_DBC);
printf ("Current DB Name : %s\n", dbName);
printf ("Current User Name : %s\n", userName);
for ( i = 0 ; i < numCols ; i++ ) {
catalogResult[i].TargetType = SQL_C_CHAR;
catalogResult[i].BufferLength = (bufferSize + 1);
catalogResult[i].TargetValuePtr =
malloc( sizeof(unsigned char)*catalogResult[i].BufferLength );
}
// Set up the binding.
for ( i = 0 ; i < numCols ; i++ ) {
//printf ("Binding Column %i\n", i+1);
retcode = SQLBindCol(hstmt,
(SQLUSMALLINT)i+1,
catalogResult[i].TargetType,
catalogResult[i].TargetValuePtr,
catalogResult[i].BufferLength,
&(catalogResult[i].StrLen_or_Ind));
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
hstmt, SQL_HANDLE_STMT);
}
retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
SQL_NTS, "TABLE", SQL_NTS );
CHECK_ERROR(retcode, "SQLTables(dbName)", hstmt, SQL_HANDLE_STMT);
// Create array of my tables starting with 'TestTBL'.
i=0;
for ( retcode = SQLFetch(hstmt) ;
retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO ;
retcode = SQLFetch(hstmt), ++count ) {
if ( strstr (catalogResult[2].TargetValuePtr, pTableName) != 0 ) {
tableNames[i]=(char *)
malloc((strlen(catalogResult[2].TargetValuePtr)+1)*sizeof(char));
strcpy (tableNames[i], catalogResult[2].TargetValuePtr);
printf( "Found Table %s\n", tableNames[i++] );
}
}
exit:
Cleanup(henv, hdbc, hstmt);
return i;
}
int main () {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
char padding[] = " ";
SQLCHAR *tableNames[MAX_TABLES];
SQLINTEGER tableCount, i, max=strlen(padding);
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER*)SQL_OV_ODBC3, 0);
CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
henv, SQL_HANDLE_ENV);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLConnect(DATASOURCE)", hdbc, SQL_HANDLE_DBC);
tableCount=getTheseTables ("TestTBL", tableNames);
for (i=0; i<tableCount; i++) {
printf ("\nTable : %s\n", tableNames[i]);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
retcode = SQLTablePrivileges(hstmt, NULL, 0, NULL, 0,
(SQLCHAR*)tableNames[i], SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Bind columns in result set to buffers
SQLBindCol(hstmt, 1, SQL_C_CHAR,
strCatalog, STR_LEN, &lenCatalog);
SQLBindCol(hstmt, 2, SQL_C_CHAR,
strSchema, STR_LEN, &lenSchema);
SQLBindCol(hstmt, 3, SQL_C_CHAR,
strTableName, STR_LEN,&lenTableName);
SQLBindCol(hstmt, 4, SQL_C_CHAR,
strGrantor, STR_LEN, &lenGrantor);
SQLBindCol(hstmt, 5, SQL_C_CHAR,
strGrantee, STR_LEN, &lenGrantee);
SQLBindCol(hstmt, 6, SQL_C_CHAR,
strPrivilege, STR_LEN, &lenPrivilege);
SQLBindCol(hstmt, 7, SQL_C_CHAR,
strIsGrantable, STR_LEN, &lenIsGrantable);
max=strlen(padding);
printf ("Catalog Schema Table Grantor ");
printf ("Grantee Is Grantable\n");
printf ("----------- ----------- ----------- -----------");
printf ("----------- ------------\n");
while (SQL_SUCCESS == retcode) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
extract_error("xSQLTablePrivileges Error : ",
hstmt, SQL_HANDLE_STMT);
}
if (retcode == SQL_SUCCESS ||
retcode == SQL_SUCCESS_WITH_INFO) {
padOut (strCatalog, padding, max);
printf ("%s,%s", strCatalog, padding);
padOut (strSchema, padding, max);
printf ("%s,%s", strSchema, padding);
padOut (strTableName, padding, max);
printf ("%s,%s", strTableName, padding);
padOut (strGrantor, padding, max);
printf ("%s,%s", strGrantor, padding);
padOut (strGrantee, padding, max);
printf ("%s,%s", strGrantee, padding);
padOut (strPrivilege, padding, max);
printf ("%s,%s", strPrivilege, padding);
padOut ("", padding, max);
printf ("%s\n", strIsGrantable);
}
}
} else {
CHECK_ERROR(retcode, "SQLStatistics()", hstmt, SQL_HANDLE_STMT);
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
exit:
printf ("\nComplete.\n");
// Free statement, connection and environment handle
Cleanup(henv, hdbc, hstmt);
return 0;
}
Further information
