Re: Proposal for new pgsqlODBC feature - hiding tables inaccessible to the current user

[Mark Cave-Ayland <mark(dot)cave-ayland(at)ilande(dot)co(dot)uk>]

Hi everyone,

I've just completed the patch for only listing tables accessible by the
current user for psqlodbc as detailed in my email here:
http://archives.postgresql.org/pgsql-odbc/2007-05/msg00068.php. As
suggested in the original post, I have implemented a tick box called
"Only list user-accessible tables" on page 2 of the Datasource Advanced
Options dialog. When this box is ticked, only tables on which the user
has one of SELECT, INSERT, DELETE or UPDATE privileges will appear in
the table list for the given DSN.

I think that this would be a really useful feature to add to the
psqlodbc driver, and so I have attached a diff from CVS HEAD. As far as
I know, the only part I have missed is adding the tickbox and its
corresponding translation to the Japanese version of the dialog in
psqlodbc.rc. Please let me know if there is any more work I need to do
in order to get this applied.


Kind regards,

Mark.


Index: connection.c
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/connection.c,v
retrieving revision 1.161
diff -u -r1.161 connection.c
--- connection.c	4 Jun 2007 11:49:49 -0000	1.161
+++ connection.c	7 Jun 2007 14:34:03 -0000
@@ -286,6 +286,7 @@
 		conninfo->fake_mss = -1;
 		conninfo->cvt_null_date_string = -1;
 		conninfo->autocommit_public = SQL_AUTOCOMMIT_ON;
+		conninfo->list_usertables_only = -1;
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 		conninfo->xa_opt = -1;
 #endif /* _HANDLE_ENLIST_IN_DTC_ */
Index: connection.h
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/connection.h,v
retrieving revision 1.85
diff -u -r1.85 connection.h
--- connection.h	2 Jun 2007 05:32:18 -0000	1.85
+++ connection.h	5 Jun 2007 05:18:57 -0000
@@ -306,6 +306,7 @@
 	signed char	fake_mss;
 	signed char	cvt_null_date_string;
 	signed char	autocommit_public;
+	signed char list_usertables_only;
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 	signed char	xa_opt;
 #endif /* _HANDLE_ENLIST_IN_DTC_ */
Index: dlg_specific.c
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_specific.c,v
retrieving revision 1.80
diff -u -r1.80 dlg_specific.c
--- dlg_specific.c	2 Jun 2007 05:32:18 -0000	1.80
+++ dlg_specific.c	7 Jun 2007 14:38:22 -0000
@@ -188,6 +188,7 @@
 			INI_BYTEAASLONGVARBINARY "=%d;"
 			INI_USESERVERSIDEPREPARE "=%d;"
 			INI_LOWERCASEIDENTIFIER "=%d;"
+			INI_LISTUSERTABLESONLY "=%d;"
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 			INI_XAOPT "=%d"	/* XAOPT */
 #endif /* _HANDLE_ENLIST_IN_DTC_ */
@@ -223,6 +224,7 @@
 			,ci->bytea_as_longvarbinary
 			,ci->use_server_side_prepare
 			,ci->lower_case_identifier
+			,ci->list_usertables_only
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 			,ci->xa_opt
 #endif /* _HANDLE_ENLIST_IN_DTC_ */
@@ -293,6 +295,8 @@
 			flag |= BIT_USESERVERSIDEPREPARE;
 		if (ci->lower_case_identifier)
 			flag |= BIT_LOWERCASEIDENTIFIER;
+		if (ci->list_usertables_only)
+			flag |= BIT_LISTUSERTABLESONLY;
 
 		if (ci->sslmode[0])
 			olen = snprintf(&connect_string[hlen], nlen, ";"
@@ -407,10 +411,12 @@
 	sprintf(ci->show_system_tables, "%d", (char)((flag & BIT_SHOWSYSTEMTABLES) != 0));
 	sprintf(ci->show_oid_column, "%d", (char)((flag & BIT_SHOWOIDCOLUMN) != 0));
 	sprintf(ci->fake_oid_index, "%d", (char)((flag & BIT_FAKEOIDINDEX) != 0));
+	ci->list_usertables_only = (char)((flag & BIT_LISTUSERTABLESONLY) != 0);
 	ci->true_is_minus1 = (char)((flag & BIT_TRUEISMINUS1) != 0);
 	ci->bytea_as_longvarbinary = (char)((flag & BIT_BYTEAASLONGVARBINARY) != 0);
 	ci->use_server_side_prepare = (char)((flag & BIT_USESERVERSIDEPREPARE) != 0);
 	ci->lower_case_identifier = (char)((flag & BIT_LOWERCASEIDENTIFIER) != 0);
+	
 }
 BOOL
 copyAttributes(ConnInfo *ci, const char *attribute, const char *value)
@@ -494,6 +500,8 @@
 		ci->use_server_side_prepare = atoi(value);
 	else if (stricmp(attribute, INI_LOWERCASEIDENTIFIER) == 0 || stricmp(attribute, ABBR_LOWERCASEIDENTIFIER) == 0)
 		ci->lower_case_identifier = atoi(value);
+	else if (stricmp(attribute, INI_LISTUSERTABLESONLY) == 0 || stricmp(attribute, ABBR_LISTUSERTABLESONLY) == 0)
+		ci->list_usertables_only = atoi(value);
 	else if (stricmp(attribute, INI_SSLMODE) == 0 || stricmp(attribute, ABBR_SSLMODE) == 0)
 	{
 		switch (value[0])
@@ -670,6 +678,9 @@
 		ci->bde_environment = 0;
 	if (ci->cvt_null_date_string < 0)
 		ci->cvt_null_date_string = 0;
+	if (ci->list_usertables_only < 0)
+		ci->list_usertables_only = DEFAULT_LISTUSERTABLESONLY;
+
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 	if (ci->xa_opt < 0)
 		ci->xa_opt = DEFAULT_XAOPT;
@@ -835,6 +846,14 @@
 	if (ci->sslmode[0] == '\0' || overwrite)
 		SQLGetPrivateProfileString(DSN, INI_SSLMODE, "", ci->sslmode, sizeof(ci->sslmode), ODBC_INI);
 
+	if (ci->list_usertables_only < 0 || overwrite)
+	{
+		SQLGetPrivateProfileString(DSN, INI_LISTUSERTABLESONLY, "", temp, sizeof(temp), ODBC_INI);
+		if (temp[0])
+			ci->list_usertables_only = atoi(temp);
+	}
+
+
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 	if (ci->xa_opt < 0 || overwrite)
 	{
@@ -1117,6 +1136,11 @@
 								 INI_SSLMODE,
 								 ci->sslmode,
 								 ODBC_INI);
+	sprintf(temp, "%d", ci->list_usertables_only);
+	SQLWritePrivateProfileString(DSN,
+								 INI_LISTUSERTABLESONLY,
+								 temp,
+								 ODBC_INI);
 #ifdef	_HANDLE_ENLIST_IN_DTC_
 	sprintf(temp, "%d", ci->xa_opt);
 	SQLWritePrivateProfileString(DSN, INI_XAOPT, temp, ODBC_INI);
Index: dlg_specific.h
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_specific.h,v
retrieving revision 1.55
diff -u -r1.55 dlg_specific.h
--- dlg_specific.h	11 Apr 2007 15:22:23 -0000	1.55
+++ dlg_specific.h	7 Jun 2007 13:20:46 -0000
@@ -141,6 +141,8 @@
 #define ABBR_LOWERCASEIDENTIFIER	"C9"
 #define INI_SSLMODE			"SSLmode"
 #define ABBR_SSLMODE			"CA"
+#define INI_LISTUSERTABLESONLY		"ListUserTablesOnly"
+#define ABBR_LISTUSERTABLESONLY		"CB"
 #define INI_EXTRAOPTIONS		"AB"
 
 #define	SSLMODE_DISABLE		"disable"
@@ -180,8 +182,9 @@
 #define BIT_BYTEAASLONGVARBINARY		(1L<<24)
 #define BIT_USESERVERSIDEPREPARE		(1L<<25)
 #define BIT_LOWERCASEIDENTIFIER			(1L<<26)
+#define BIT_LISTUSERTABLESONLY			(1L<<27)
 
-#define EFFECTIVE_BIT_COUNT			27
+#define EFFECTIVE_BIT_COUNT			28
 
 /*	Mask for extra options	*/
 #define	BIT_FORCEABBREVCONNSTR			1L
@@ -203,6 +206,7 @@
 #define DEFAULT_UNIQUEINDEX			1		/* dont recognize */
 #define DEFAULT_COMMLOG				0		/* dont log */
 #define DEFAULT_DEBUG				0
+#define DEFAULT_LISTUSERTABLESONLY	0		/* show all tables by default */
 #define DEFAULT_UNKNOWNSIZES			UNKNOWNS_AS_MAX
 
 
Index: dlg_wingui.c
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/dlg_wingui.c,v
retrieving revision 1.18
diff -u -r1.18 dlg_wingui.c
--- dlg_wingui.c	2 Jun 2007 05:32:18 -0000	1.18
+++ dlg_wingui.c	7 Jun 2007 14:43:51 -0000
@@ -566,6 +566,7 @@
 			CheckDlgButton(hdlg, DS_UPDATABLECURSORS, ci->allow_keyset);
 			CheckDlgButton(hdlg, DS_SERVERSIDEPREPARE, ci->use_server_side_prepare);
 			CheckDlgButton(hdlg, DS_BYTEAASLONGVARBINARY, ci->bytea_as_longvarbinary);
+			CheckDlgButton(hdlg, DS_LISTUSERTABLESONLY, ci->list_usertables_only);
 			/*CheckDlgButton(hdlg, DS_LOWERCASEIDENTIFIER, ci->lower_case_identifier);*/
 
 			EnableWindow(GetDlgItem(hdlg, DS_FAKEOIDINDEX), atoi(ci->show_oid_column));
@@ -638,6 +639,7 @@
 					ci->allow_keyset = IsDlgButtonChecked(hdlg, DS_UPDATABLECURSORS);
 					ci->use_server_side_prepare = IsDlgButtonChecked(hdlg, DS_SERVERSIDEPREPARE);
 					ci->bytea_as_longvarbinary = IsDlgButtonChecked(hdlg, DS_BYTEAASLONGVARBINARY);
+					ci->list_usertables_only = IsDlgButtonChecked(hdlg, DS_LISTUSERTABLESONLY);
 					/*ci->lower_case_identifier = IsDlgButtonChecked(hdlg, DS_LOWERCASEIDENTIFIER);*/
 
 					/* OID Options */
Index: info.c
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.145
diff -u -r1.145 info.c
--- info.c	2 Jun 2007 05:32:18 -0000	1.145
+++ info.c	7 Jun 2007 12:38:04 -0000
@@ -1709,6 +1709,18 @@
 		}
 	}
 
+	/*
+	 * Check to see if we are only interested in listing tables upon
+	 * which the user has one of SELECT, INSERT, DELETE or UPDATE
+	 * privileges
+	 */
+	if (!list_some)
+	{
+		if (ci->list_usertables_only)
+			strcat(tables_query, " and (has_table_privilege(current_user, c.oid, 'select') or has_table_privilege(current_user, c.oid, 'insert') or" 
+			" has_table_privilege(current_user, c.oid, 'delete') or has_table_privilege(current_user, c.oid, 'update'))"); 
+	}
+
 	if (list_schemas)
 		strcat(tables_query, " order by nspname");
 	else if (list_some)
Index: psqlodbc.rc
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/psqlodbc.rc,v
retrieving revision 1.87
diff -u -r1.87 psqlodbc.rc
--- psqlodbc.rc	23 Oct 2006 15:58:29 -0000	1.87
+++ psqlodbc.rc	4 Jun 2007 20:59:01 -0000
@@ -542,6 +542,8 @@
                     BS_AUTOCHECKBOX | WS_TABSTOP,163,71,90,10
     CONTROL         "bytea as LO",DS_BYTEAASLONGVARBINARY,"Button",
                     BS_AUTOCHECKBOX | WS_TABSTOP,16,84,87,10
+    CONTROL         "Only list user-accessible tables",DS_LISTUSERTABLESONLY,"Button",
+                    BS_AUTOCHECKBOX | WS_TABSTOP,163,84,147,10
     GROUPBOX        "Int8 As",IDC_STATIC,5,97,256,25
     CONTROL         "default",DS_INT8_AS_DEFAULT,"Button",BS_AUTORADIOBUTTON | 
                     WS_GROUP,12,107,40,10
Index: resource.h
===================================================================
RCS file: /cvsroot/psqlodbc/psqlodbc/resource.h,v
retrieving revision 1.40
diff -u -r1.40 resource.h
--- resource.h	23 Oct 2006 15:58:29 -0000	1.40
+++ resource.h	4 Jun 2007 20:58:51 -0000
@@ -94,6 +94,7 @@
 #define DRV_DTCLOG			1083
 #define DS_EXTRA_OPTIONS		1084
 #define IDC_TEST			1085
+#define DS_LISTUSERTABLESONLY	1086
 
 // Next default values for new objects
 //