asked on

How to work with a SQL user that has only access to one record at a time in MS Access?

Hi Experts,

I'm trying to setup an application that should allow users adding/editing/deleting a single row at a time.

Now for security purposes, I would like to have these users not being able to view more then one record at a time.

Was thinking of designing a stored procedure that will accept a parameter and display just that one record selected.

However I'm stuck with the following
A- A stored procedure is probably read only in Access.
B- In order to have users select a record, I need a drop down displaying all records, how can I have it just avail to the combo box record source while in database container it should not appear?

PS. for deleting single record I came up with this function that will perform it, so I dont give this user access to delete anything in SQL BE, and just have a button in Access calling the following

Public Function DeleteRecord(sTable As String, lID As Long) As Integer

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmdString As String
    Dim i As Integer
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "driver={SQL Server};" & _
          "server=tcp:ServerName\Instance;uid=MyUser;pwd=MyPWD;database=MyDB"
    cnn.Open


    cmdString = "delete from " & sTable & " where id = " & lID
 
    cnn.Execute (cmdString), i
    cnn.Close
    DeleteRecord = i

End Function

Open in new window

So basically I'm looking to deploy the same idea for adding/editing records.

Would like to know what is the simplest way to accomplish it.

Thanks in Advance.

PatHartman

Do users know the unique ID of the record they want to edit? If they know it, just use a text box. If they need to search using something more generic, limiting them to a single record could be difficult. Some apps have search forms with multiple options that can be used alone or in combination. The code behind the form constructs a WHERE clause based on what criteria the user enters. You can run a count query using the entered criteria and depending on how many rows the criteria returns, you can force the user to enter additional criteria or if the number of rows returned is small enough, you can open a list form with just a few columns. Then they click on a row in the list form to open the single record maintenance form. My single record maintenance forms always open empty. They are all bound to queries with criteria that references textboxes or combos on the form..

bfuchs

ASKER

Hi Pat,

Guess you're referring to issue B.

So you are saying that I should give this SQL user access to a stored procedure that accepts a @sWhere string param and would return the ID and some other field/s (in this case EmployeesTbl.FirstName and EmployeesTbl.LastName) matching the criteria?

And what about editing records (Issue A), how can I have updatable record source with one record only?

Do you have sample app to demonstrate similar functionality?

Thanks,
Ben

PatHartman

Users should never have access to anything other than forms and reports. Your form should be doing the filtering for them. All my databases link to SQL Server, et al. None of them are ADP's so I don't have an example.

Here's a sample. The buttons View, Export, and List build a where clause and display it in the query box. It is also displayed at the top of the print list report. If only a single record is selected, the single record edit form opens. Otherwise a list form opens.

bfuchs

ASKER

Users should never have access to anything other than forms and reports.

Right, now my question is, since this DB will contain highly sensitive data and would be accessed by some users outside the country, our manager would like to build extra layers of security.
Would you think just hiding the DB container and disabling the special keys (like shift open) is enough?

Since I'm linking those tables thru a DSN, where the user name and pwd its stored there, I would prefer to have the least rights granted to this user.

Therefore my plan is to grant access for stored proc only where those SP return the least amt of records necessary.

As you see in OP, I was able to figure out how to handle deletes without granting this user deletion rights.

Now I am looking for help how to apply similar technique to additions and edits in the same manner?

None of them are ADP's so I don't have an example.

You probably remember me from previous posts where I use ADP-:)
However this is a small app just started to design and was not going to do in ADP...

So any sample app with these specs would be helpful.

Thanks,
Ben

PatHartman

Hi Ben,
Nothing is enough where Access is concerned. It is relatively easy to crack for a determined technical person. If you are worried about security, you are using the wrong tool. Whatever security you need will have to be implemented by SQL Server. If the users are view only, then bind the forms to stored procedures and don't link the tables. If they need to be able to update data, that is a whole other problem because you would need to use VBA and even in an .accde a cracker can view your code. He just has to use a decompile tool to get it back to plain text. Some of them even reconstruct the code using your original variable names rather than tokens. Notice that your UID and password are in your code.

I don't use stored procedures for forms so I don't have any examples.

Why does it matter where in the world your users are? If they are so untrustworthy that you have to worry about this, you really need to use a different platform.

Just as an aside. One thing I sometimes do if I am developing an app that technical people will use and I'm afraid that if they know their SQL Server credentials, they can just create an empty database and link directly to the tables themselves, is to obfuscate their password. I create a piece of code that will take a userID and generate a password. The user is given his UID and a password to log in to the access app. The access app then calculates his real SQL Server password and uses that to link to tables on the server. A simpler way is to use a hard-coded password but you and the DBA need to coordinate so you change it frequently.

bfuchs

ASKER

Hi,

If you are worried about security, you are using the wrong tool.

Perhaps you right with that as well..however we have to deal with what we currently have, and for now I only have experience with SQL and MS Access.

With that said, I believe will have to focus first on how to prevent regular users from copying data, and will worry about the "determined technical person" at a later time.

So I think the next step towards security will be get rid of the DSN file and use the following example to get all tables linked thru code.

http://etutorials.org/Microsoft+Products/access/Chapter+14.+SQL+Server/Recipe+14.1+Dynamically+Link+SQL+Server+Tables+at+Runtime/

Whats your opinion?

Thanks,
Ben

PatHartman

I use dynamic links in some apps. The startup can be a little slow depending on how many tables need to get linked but otherwise they're fine.

bfuchs

ASKER

Hi Pat,

First excuse me for the delay, was not in on FRI..

So back to the OP question, is there a way I can have a link to either a view/UDF/SP that will only display one record and still be updatable?

BTW, I was under impression that there is way way to disable all Access menus, including the option to bypass the startup settings by opening with the shift key.

However while testing it I see that's not the case, as I tried un-checking all options and still seeing some menus avail, and worse of all they can all be bypassed by the shift button.
See attached.

Any idea how can I get all those option completely removed from my app

fyi- Using A2003 for design, but if necessary can try a later version to setup options..

Thanks,
Ben
Untitled.png
Capture.PNG

PatHartman

is there a way I can have a link to either a view/UDF/SP that will only display one record and still be updatable?

No. Stored procedures are not updateable.

Any idea how can I get all those option completely removed from my app

You can block shift/bypass with code. I've never tried to completely remove the Access container but I have seen articles that say it can be done. If you stick with A2003, you can use ULS which gives you some control over access to objects It is extremely difficult to actually secure a database using ULS (which was ultimately the deciding factor in its being deprecated. If people couldn't use it correctly, there was no point in keeping it.). I've attached a copy of the security FAQ. It is very old but I think still applies to A2003 although there might be a newer version somewhere. One thing to check is how newer versions of Access work with the .mda. New versions of Access no longer support the creation of ULS but I don't know if they respect it or ignore it when it exists.
SECFAQ.doc

bfuchs

ASKER

No.  Stored procedures are not updateable.

Open in new window

OK, And whats about the other options, a UDF or having a view that will return just the desired record?

I understand that SQL its not like Access where you can just modify the SQL property of a query, and even if you do this may affect other users..
However wondering if there is a way to trick the system in order to accomplish that..?

FYI- As you mentioned Access its not the most reliable tool when it comes to build security, and therefore I'm putting most those restrictions on the server side, however will still remain some concerns on the Access side, like preventing users from copying whats do get displayed in the DB window..

Thanks,
Ben

PatHartman

You can only use UDF's in pass-through queries and those are not updateable. Views are updateable as long as you define a unique index for them when you link them. A view will only help if you can use it to join to a temp table where the temp table holds the IDs of the records you want to select. That might be a viable solution. Use a query to select the desired wrcord, write the id to the temp table and use the view as the bound recordsource of the edit form.

You cannot prevent people from doing a print screen so even if they can' get the data easily in a machine readable format, they can still print it. There are things that can be done by Windows but you don't really have control over the user's PC so you cannot implement any of those Windows level printing and file saving measures.

bfuchs

ASKER

Use a query to select the desired wrcord, write the id to the temp table and use the view as the bound recordsource of the edit form.

Was thinking of something similar, to add two fields to the original table, a bit field and a UserName field, and update that to false/true before selecting and having the UserName to distinguish between different users of the app..not sure which is way is better for performance?

you don't really have control over the user's PC..

From what I understand, the plan here is to give them access to a special terminal server that will have the Access app on it...and we can restrict some functionality from them, its our IT guy department to set that up, however would prefer do first in Access/SQL the most possible, and the rest leave for them.

Thanks,
Ben
.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Thank you!

PatHartman

You're welcome. Hope that got you closer to what you want.