Microsoft KB Archive/190064

= How To Build SELECT Statements Based on User Column Privileges =

Article ID: 190064

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q190064



SUMMARY
When writing SELECT statements that will be executed by users with varying levels of column privileges on a SQL Server table, the SELECT statements may not work for all users. For instance, if a user executes a "SELECT * FROM..." statement and does not have privileges on all columns, the statement will return an error and no data.



Sample Code
*-- Code begins here. *-- First, connect as administrator to set the column privileges for *-- user "mike". *-- This is strictly for demonstration purposes. Production code would *-- likely assume that the user login exists and that privileges are *-- already set. *--  *-- Change the SQLCONNECT function as needed to connect to   *-- the database. hConnect = SQLCONNECT("MyDSN", "sa", "password") IF hConnect <= 0 =MESSAGEBOX("Connection failed with return code" + STR(hConnect),;       0, "Connection Error") RETURN ENDIF

*-- Grant privileges on three columns to user "mike." *-- Code assumes that user "mike" already exists in the pubs database. cSQLCommand="GRANT SELECT ON pubs.dbo.authors " + ; "(au_id, au_lname, au_fname) TO mike" gnExec = SQLEXEC(hConnect, cSQLCommand)

*-- Disconnect and reconnect as a less privileged user, "mike". =SQLDISCONNECT(hConnect)

*-- Change the SQLCONNECT function as needed to connect to  *-- the database. hConnect = SQLCONNECT("MyDSN", "mike", "password") IF hConnect <= 0 =MESSAGEBOX("Connection failed with return code" + STR(hConnect),;       0, "Connection Error") RETURN ENDIF

*-- Select the pubs database. gnExec = SQLEXEC(hConnect, 'use pubs')

*-- Get the column privileges for the table. gnExec = SQLEXEC(hConnect, 'EXEC sp_column_privileges ;    "authors"', 'privileges') SELECT privileges

*-- Narrow it down to the user we want. lsUser = UPPER('mike') SELECT Column_Name FROM privileges ; WHERE UPPER(Grantee) = lsUser AND Privilege = 'SELECT'; INTO CURSOR MyPrivileges

*-- Step through the table and build our SELECT statement. SELECT MyPrivileges GOTO TOP IF NOT EOF && Assuming user actually has privileges on the table. && If the user has no privileges, the table is empty. lsColumns = "" SCAN lsColumns = lsColumns + ALLTRIM(Column_Name) + ", " ENDSCAN

*-- Trim off the last comma and space. lsColumns = SUBSTR(lsColumns, 1, LEN(lsColumns)-2) ENDIF

*-- Now issue the select statement. gnExec = SQLEXEC(hConnect, 'SELECT ' + lsColumns + ' FROM authors') BROWSE CLOSE DATA SQLDISCONNECT(hConnect) *-- Code ends here.

