/**********************************************************************
* FILENAME : SQLColumnPrivileges.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 starting with
* 'TestTBL'.
*
* For each table, call :
*
* SQLColumnPrivileges() to get column privileges
* SQLBindCol() to bind the 8 columns of the result set (those being
* Catalog, Schema, Table, Column, Grantor, Grantee and Is Grantable)
* SQLFetch() to get the values
* Displays information returned
*
*/
#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 strColumnName[STR_LEN];
SQLCHAR strGrantor[STR_LEN];
SQLCHAR strGrantee[REM_LEN];
SQLCHAR strPrivilege[STR_LEN];
SQLCHAR strIsGrantable[STR_LEN];
SQLINTEGER ColumnSize;
SQLINTEGER BufferLength;
SQLINTEGER CharOctetLength;
SQLINTEGER OrdinalPosition;
SQLSMALLINT DataType;
SQLSMALLINT DecimalDigits;
SQLSMALLINT NumPrecRadix;
SQLSMALLINT Nullable;
SQLSMALLINT SQLDataType;
SQLSMALLINT DatetimeSubtypeCode;
SQLHSTMT hstmt = NULL;
// Declare buffers for bytes available to return
SQLLEN lenCatalog;
SQLLEN lenSchema;
SQLLEN lenTableName;
SQLLEN lenColumnName;
SQLLEN lenGrantor;
SQLLEN lenGrantee;
SQLLEN lenPrivilege;
SQLLEN lenIsGrantable;
struct DataBinding {
SQLSMALLINT TargetType;
SQLPOINTER TargetValuePtr;
SQLINTEGER BufferLength;
SQLLEN StrLen_or_Ind;
};
// Gets list of tables based on iTableStrPtr
int getTheseTables (char *iTableName, SQLCHAR *tableNames[]) {
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode;
int bufferSize = 1024, i, j, count = 1;
SQLCHAR * dbName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
SQLCHAR * userName = (SQLCHAR *)malloc( sizeof(SQLCHAR)*bufferSize );
SQLCHAR connStrbuffer[1024];
SQLSMALLINT connStrBufferLen, bufferLen;
struct DataBinding* catalogResult = (struct DataBinding*)
malloc( NUMCOLS * sizeof(struct DataBinding) );
SQLCHAR* selectAllQuery = (SQLCHAR *)
malloc( sizeof(SQLCHAR) * bufferSize );
// Allocate Environment Handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
henv, SQL_HANDLE_ENV);
// Set ODBC version
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);
// Allocate Connection Handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Set timeout
retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
hdbc, SQL_HANDLE_DBC);
// Connect to a datasource
retcode = SQLConnect(hdbc, (SQLCHAR*) "DATASOURCE", SQL_NTS,
(SQLCHAR*) NULL, 0, NULL, 0);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
hdbc, SQL_HANDLE_DBC);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
// Get and 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 column bindings
for ( i = 0 ; i < NUMCOLS ; i++ ) {
retcode = SQLBindCol(hstmt, (SQLUSMALLINT)i+1,
catalogResult[i].TargetType,
catalogResult[i].TargetValuePtr,
catalogResult[i].BufferLength,
&(catalogResult[i].StrLen_or_Ind));
CHECK_ERROR(retcode, "SQLBindCol(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
}
retcode = SQLTables( hstmt, dbName, SQL_NTS, userName, SQL_NTS, "%",
SQL_NTS, "TABLE", SQL_NTS );
CHECK_ERROR(retcode, "SQLTables(SQL_HANDLE_STMT)",
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, "TestTBL") != 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:
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);
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; // OSBC function return status
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);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
hstmt, SQL_HANDLE_STMT);
tableCount=getTheseTables ("TestTBL", tableNames);
for (i=0; i<tableCount; i++) {
printf ("\nTable : %s\n", tableNames[i]);
retcode = SQLColumnPrivileges(hstmt, NULL, 0, NULL, 0,
(SQLCHAR*)tableNames[i],
SQL_NTS, NULL, 0);
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, strColumnName,
STR_LEN, &lenColumnName);
SQLBindCol(hstmt, 5, SQL_C_CHAR, strGrantor,
STR_LEN, &lenGrantor);
SQLBindCol(hstmt, 6, SQL_C_CHAR, strGrantee,
STR_LEN, &lenGrantee);
SQLBindCol(hstmt, 7, SQL_C_CHAR, strPrivilege,
STR_LEN, &lenPrivilege);
SQLBindCol(hstmt, 8, SQL_C_CHAR, strIsGrantable,
STR_LEN, &lenIsGrantable);
max=strlen(padding);
printf ("Catalog Schema Table Column Grantor"
"Grantee Is Grantable\n");
printf ("---------- ---------- ---------- ---------- ----------"
"---------- --------------\n");
while (SQL_SUCCESS == retcode) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
extract_error("Error : ", hstmt, SQL_HANDLE_STMT);
}
// Display results in tabular form
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 (strColumnName, padding, max);
printf ("%s%s", strColumnName, 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);
}
}
}
retcode = SQLFreeStmt(hstmt, SQL_CLOSE);
}
exit:
printf ("\nComplete.\n");
// Free handles
// Statement
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
// Connection
if (hdbc != SQL_NULL_HDBC) {
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
// Environment
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return 0;
}
See Also
