How To Build SELECT Statements Based on User Column Privileges (190064)



The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 3.0b
  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
  • Microsoft Visual FoxPro for Windows 6.0

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.

MORE INFORMATION

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.
				

REFERENCES


Modification Type:MinorLast Reviewed:6/29/2004
Keywords:kbhowto KB190064