How To Build SELECT Statements Based on User Column Privileges

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.