MS Access with Linked tables Security Issue

[FA]

Hi, I have a question which i can not figure out how to solve. I have
..mde file with the linked tables with SQL Server with ODBC connection.
I have six users each has user id and password to access SQL Server.
There is no authentication on the front end (.mde file). I am wondering
if they have access to SQL Server tables, any user can create an mdb
file and link tables from SQL Server and do anything they want to do.
In my .mde file they can only update certain data. Users are not
allowed to delete any record in only one table but they are not allowed
to delete any record in any other table.
If they can make a ODBC connection since they has user id and password
to sql server, they can do anything they want in sql tables. I do not
think it would help if i add another layer of authentication in my .mde
file since they can create a mdb file and have access to all the data.

Please let me know what is the solution to this problem.

Your replies would be greatly appreciated.

Thanks
Moe

 

[Nikos Yannacopoulos]

Moe,

To begin with, you could set the SQL user security at table level, so
each user would only have access to the tables they need, as opposed to all.
That said, you could take an altogether different approach (something
I've been using for a while):

The front end .mde does not have permanent links to the BE on SQL
server; the tables are linked upon loading the main form. This is done
through code, using a unique SQL Server, the same for all users. The
user name and password are hardcoded in the .mde code, so the users
don't even know it. That way, they cannot connect to the SQL Server on
their own. Furthermore, the code that links to the BE tables executes
only if the .mde database window is not visible, so if the user opens
the .mde while holding the Shift key pressed, the connection will not be
carried out. Thus, the user cannot gain access to the tables though the
FE either, they can only ever access data through the forms.
Additionally, you could use a table in your app to designate user rights
(forms they are allowed to open, read-only or modify etc) simply by
reading their Windows logon ID, without the user having to logon to the app.

HTH,
Nikos

 

[FA]

That is exactly what i had in my mind but i did not know where to start
with. I am not a heavy duty VBA programmer, if it possible, would you
mind sharing the snipet of your codes for this particular function. If
it possible you can email me the codes at (e-mail address removed)

Your help would be greatly greatly appreciated.

Thanks

Moe

 

[FA]

I forgot to mention that i wanted to go with the second approach that
takes some coding at the front end to perform such function. It would
be great if you see your some codes as an example to start with.
my email address didnt come right in my previous post. its
october.april at gmail dot com

I would be greatfull if you help me out.

Thanks alot

Moe

 

[FA]

Hi Nikos, I have come up with some codes that are allowing me to
connect to SQL Server database with one Super ID that has all the super
permissions .
All of the six users have Windows (System) User IDs. lets say if the
IDs are
1) xyz123 2) abcd123 3) mnop124 4) plop4521 5) kmno1235 6)
tclm1478

How do i code in VBA so that xyz123 can have access to all the objects
in .mde file but abcd123 can have access to only table1 and table2 and
form1 and form2.
I am gona have to embed these System IDs into a module and call it in
my startup form. MS Access should compare each ID and grant the
appropriate permission.

Can you help me out with the VBA Code please ??

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables
Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String
If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN &
";UID=Admin;PWD=test123!;DATABASE=" & strDatabase & ";"
End If
SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."
Call DeleteODBCTableNames
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)
Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename],
dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing
Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub
Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source _
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables
End Sub

Thanks alot

 

[Nikos Yannacopoulos]

Moe,

First of all on procedure: I won't be mailing any code in private, all
is done in the NG in public so others with a similar situation can
benefit from it.

Now, to the issues at hand: congratulations on cracking connection code
on your own. A good trigger for this code is the Open event of your
switchboard or main form. Don't forget to use the close event το unlink!

Now, on user security: forget about tables, they are not visible to
users anyway (the database window is hidden, and F11 is disabled in the
startup options). S, concentrating on forms, I use command buttons on
the main form (or switchboard, if you like those - I don't) to open the
forms, so some Open code retrieves the current user login, and looks up
the security settings in my users table, then enables or disables
command buttons accordingly - that simple, no rocket science here.

The only bit that puzzled me and made me post here for help, was how to
check for database window status (visible or not, so I don't allow the
connection to the BE in the former case). I got lucky and Access MVP
Graham Search saw it and posted back some code that checks this,
originally written by MVP Dev Ashish, according to Graham - at any rate,
I'm grateful to both. You can find the code at:

http://www.pacificdb.com.au/MVP/Code/DatabaseWindow.htm

HTH,
Nikos