/**********************************************************************
* 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;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.