Change SQL Pass-through in code with app security?

[Kincaco]

I am using SQL pass-through queries in MS Access 97 to run reports against Oracle databases. The user enters a date range in a form (and potentially other criteria) and clicks a button to preview a report. The button runs a macro that deletes the query and runs code that rebuilds the query using input from the form (dates, etc.). I have the following code working until I apply Access security.

The user will be using their default SYSTEM.MDW so they will be logged in as the user Admin. When I try to lock down permissions for Admin, I get error 3033 '...insufficient priviledges for MSYSTABLES...'

Currently Admin has all priviledges except Administer for all queries. Admin also has all priviledges for the system tables except Administer. I could not find a system object call 'MSYSTABLES'.

My goal is to use a pass-through query for performance reasons that can accept user input from a form. My other goal is to secure the database from users changing the query. The integrity of the query design is important to protect.

The code I currently have working:

Function WC_by_Week()

Dim db As Database
Dim qd As QueryDef
Dim ID As String
Dim IDCriteria As String
Dim FormID As String

Set db = CurrentDb()

'Check the Main form to see if Employee ID is "None", Null or an ID.
If IsNull(Forms("frm Main&quot.Controls("EmployeeID&quot.Value) Then
ID = "LABOR.EMPLOYEE_ID, "
IDCriteria = ""
Else
If Forms("frm Main&quot.Controls("EmployeeID&quot.Value = "None" Then
ID = ""
IDCriteria = ""
Else
If Len(Forms("frm Main&quot.Controls("EmployeeID&quot.Value) = 7 Then
ID = "LABOR.EMPLOYEE_ID, "
IDCriteria = "AND LABOR.EMPLOYEE_ID = '" & Forms![frm Main]![EmployeeID] & "' "
Else
MsgBox "You must enter a valid Employee ID"
End If
End If
End If

' Create a QueryDef object to retrieve
' data from Labor tables.
Set qd = db.CreateQueryDef("qry Work Center Weekly&quot

With qd
.Connect = "ODBC;DSN=whatif;DBQ=WHATif;"

.SQL = "SELECT SOPN.PO_WORK_CENTER_ID, " & _
ID & _
"NEXT_DAY(LABOR.DATE,'FRIDAY') AS WEEK_END, " & _
"SUM(LABOR.MFG_HOURS) AS MFG, " & _
"SUM(LABOR.ENG_HOURS) AS ENG, " & _
"SUM(DECODE(LUNCH_BREAK_VALUE,Null,HOURS_PROCESSED,0,HOURS_PROCESSED,HOURS_PROCESSED-LUNCH_BREAK_VALUE)) AS HOURS_WORKED, " & _
"LABOR.DEPT_CODE " & _
"FROM LABOROWNER.LABOR LABOR, " & _
"CSIOWNER.SOPN SOPN " & _
"WHERE (LABOR.RECORD_CODE='L' OR LABOR.RECORD_CODE = 'S') " & _
"AND (TO_NUMBER(SOPN.MAJOR_SEQ_NBR) = LABOR.MAJOR_SEQ_NBR) " & _
"AND LABOR.ORD_NBR = SOPN.ORD_NBR " & _
IDCriteria & _
"AND LABOR.DATE >= TO_DATE('" & Forms![frm Main]![BeginDate] & "','mm/dd/yyyy') " & _
&quot;AND LABOR.DATE <= TO_DATE('&quot; & Forms![frm Main]![EndDate] & &quot;','mm/dd/yyyy') &quot; & _
&quot;GROUP BY LABOR.DEPT_CODE, &quot; & _
&quot;SOPN.PO_WORK_CENTER_ID, &quot; & _
ID & _
&quot;NEXT_DAY(LABOR.WORK_DATE,'FRIDAY'); &quot;

End With


End Function

Thanks in advance...

 

[DougP]

The first thing I would do is get it working with lax security.
Does it do that now????
then make a copy of the database
So if you get it secured so much it quits working you can go back to t agood version and start doen a different path.
Then implement security easily one piece at a time checking to make sure it still runs.

Most people spend way too much time securing something and securing it so far it does not work anymore.
There are other ways to keep people out of it.

Look at thses FAQ's
faq181-90 How do I make an .MDE file
faq181-89 How do I add data to more than one Table



DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Kincaco]

I have the code working with no security. I am in the 'hunt and peck' process of applying/removing security to try and find the right combination. I was hoping someone could spare me some trial and error.

Thanks!

 

[lameid]

The table you are having trouble with is an Access System table... I don't know what causes permissions on these tables to get funky every once in a while but importing everything into a new database will strip security... You should be logged in as your 'secured' user when you do this for ownership reasons and then set security.

If you have too much security set already and you feel lucky... go into tools options and on the view tab show hidden and system objects which will allow you to set the security on the system table. I always figured if the system table got a little weird, it is better to start with a new database file than hope.