Security issue to access database with user logons


I have a very simple vb6 app talking to a access database (97)
i am using a system dsn odbc connection,
i am running on win XP pro.
Now i have to user accounts
1. Admin account
2. Normal User account

my application runs fine under the Admin account
but when i log off and log on as the normal user, i get an odbc
error, this is the error
"-2147467259 - ODBC Microsoft Access Driver - operation must use an updateable query"
bo when i am logged on as user, i checked that the database file is not read only
Now i can read values from the database, but as soon as i try to insert or update a
record in the database, i get this error.

any ideas

Who is Participating?
DangerizConnect With a Mentor Commented:
Ok, looks like what you need to do is to modify NTFS folder permissions programmatically.
I've found out a bit of code how to set a specific folder to have WRITE permissions for EVERYONE.

Copy and paste the code at the bottom and just change the FOLDER_PATH value to match the one you'll be using.
You'll just have to clean it up a bit and implement it somewhere in your code...


Option Explicit

Private Const FOLDER_PATH = "C:\Test"

' Success status of high level access control APIs
Private Const ERROR_SUCCESS = 0&

' Type of Securable Object we are operating in this sample code
Private Const SE_FILE_OBJECT = 1&

' The Security Information constants required
Private Const SET_ACCESS = 2&

' Standard access rights extracted from WinNT.h
Private Const SYNCHRONIZE = &H100000
Private Const READ_CONTROL = &H20000
Private Const WRITE_DAC = &H40000
Private Const WRITE_OWNER = &H80000
Private Const DELETE = &H10000

' Generic access rights extracted from WinNT.h
Private Const GENERIC_ALL = &H10000000
Private Const GENERIC_EXECUTE = &H20000000
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000

' Inheritance Flags
Private Const OBJECT_INHERIT_ACE = &H1

' The TRUSTEE structure identifies the user account, group account, or logon session
' to which an ACE applies. The structure can use a name or a security identifier (SID)
' to identify the trustee.

' Access control APIs, such as SetEntriesInAcl and GetExplicitEntriesFromAcl, use this
' structure to identify the account associated with the access-control or audit-control
' information in an EXPLICIT_ACCESS structure.
Private Type TRUSTEE
    pMultipleTrustee As Long
    MultipleTrusteeOperation As Long
    TrusteeForm As Long
    TrusteeType As Long
    ptstrName As String
End Type

' EXPLICIT_ACCESS structure that specifies access-control information for a specified
' trustee such as access mask as well as inheritance flags
    grfAccessPermissions As Long
    grfAccessMode As Long
    grfInheritance As Long
End Type

' High Level access control API declarations
Private Declare Sub BuildExplicitAccessWithName Lib "Advapi32.dll" Alias _
    "BuildExplicitAccessWithNameA" _
    (ea As Any, _
    ByVal TrusteeName As String, _
    ByVal AccessPermissions As Long, _
    ByVal AccessMode As Integer, _
    ByVal Inheritance As Long)
Private Declare Function SetEntriesInAcl Lib "Advapi32.dll" Alias _
    "SetEntriesInAclA" _
    (ByVal CountofExplicitEntries As Long, _
    ea As Any, _
    ByVal OldAcl As Long, _
    NewAcl As Long) As Long

Private Declare Function GetNamedSecurityInfo Lib "Advapi32.dll" Alias _
    "GetNamedSecurityInfoA" _
    (ByVal ObjName As String, _
    ByVal SE_OBJECT_TYPE As Long, _
    ByVal SecInfo As Long, _
    ByVal pSid As Long, _
    ByVal pSidGroup As Long, _
    pDacl As Long, _
    ByVal pSacl As Long, _
    pSecurityDescriptor As Long) As Long
Private Declare Function SetNamedSecurityInfo Lib "Advapi32.dll" Alias _
    "SetNamedSecurityInfoA" _
    (ByVal ObjName As String, _
    ByVal SE_OBJECT As Long, _
    ByVal SecInfo As Long, _
    ByVal pSid As Long, _
    ByVal pSidGroup As Long, _
    ByVal pDacl As Long, _
    ByVal pSacl As Long) As Long

Private Declare Function LocalFree Lib "kernel32" (ByVal hMem As Long) As Long

Private Sub Command1_Click()

Dim result As Long
Dim pSecDesc As Long
Dim pNewDACL As Long
Dim pOldDACL As Long
' Get the DACL information of the folder using GetNamedSecurityInfo() API.
' SE_FILE_OBJECT constant says that the named securable object is a file or folder
result = GetNamedSecurityInfo(FOLDER_PATH, SE_FILE_OBJECT, DACL_SECURITY_INFORMATION, 0&, 0&, pOldDACL, 0&, pSecDesc)
If result = ERROR_SUCCESS Then
    ' Construct an EXPLICIT_ACCESS structure for Everyone with GENERIC_ALL access that will apply for c:\test1
    ' as well as subfolder and files using BuildExplicitAccessWithName() API
    ' Merge constructed EXPLICIT_ACCESS structure to the existing DACL and get an updated DACL in memory from
    ' SetEntriesInAcl() API
    result = SetEntriesInAcl(1, ea, pOldDACL, pNewDACL)
    If result = ERROR_SUCCESS Then
        MsgBox "SetEntriesInAcl succeeded"
        ' Call SetNamedSecurityInfo() API with the updated DACL in memory to change the DACL of c:\test folder
        result = SetNamedSecurityInfo(FOLDER_PATH, SE_FILE_OBJECT, DACL_SECURITY_INFORMATION, 0&, 0&, pNewDACL, 0&)
        If result = ERROR_SUCCESS Then
            MsgBox "SetNamedSecurityInfo succeeded"
            MsgBox "SetNamedSecurityInfo failed with error code : " & result
        End If
        ' Free the memory allocated for the new DACL by the SetEntriesInAcl() API, using LocalFree() API
        LocalFree pNewDACL
        MsgBox "SetEntriesInAcl failed with error code : " & result
    End If
    ' Free the memory allocated for the security descriptor by the GetNamedSecurityInfo() API, using LocalFree() API
    LocalFree pSecDesc
    MsgBox "GetNamedSecurityInfo failed with error code : " & result
End If
End Sub

This is definitely a permissions issue.

Make sure you have write permissions set on the folder/directory containing the MDB for both accounts.

In WinXP right-click on the folder/directory containing the MDB. Then click on "Sharing and Security" and go to the "Security" tab. You'll probably have to "Add" the normal user account and grant write access to that account.

This should sort the problem out...
Do the same for the security of the MDB file itself, just to make sure...
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

CraigLazarAuthor Commented:
Ok, but is there away to get around this say thru my install script, cause microsoft is testing our application - so it needs to happen seemlesly thru the install

thanksi am using wise install master as the install script tool

CraigLazarAuthor Commented:
if i set the database permissions and then reship the install script, o u think that will work ?

have a good one

thanks for the input
Yes that will work.

What you have to do is to make sure your database permissions have "full control/write access" for "Everyone" before you package your application. Then when you install your application, the database will have those permissions already. I think a general rule is change your database permissions right after you have created the database by right clicking on it, and going to security...

CraigLazarAuthor Commented:
ok cool
thanks i will give that a bash today
and let u know

have a good weekend
sure, have a gr8 weekend too :->

CraigLazarAuthor Commented:
Hi Guys

Ok i have been testing and this is what i have found
I have changed the securities on the mdb file, but i still get the same error
I then went to the directory that the database is sitting in and shared it with
full writes. Then when i loged on as a restricted user my application runs.

Now this is a problem, cause then i manually have to adjust the securitys on the folder
the app is installed in. Now microsoft is testing the application and have said i need
to allow for restricted user profiles to logon to the same machine and run the application
no problems.

i also then in the restricted profile user, mapped a drive to another pc on the network
and ran the app and it worked. So from what i can c, it has something to do with teh restricted users
access on files and folders on that pc.

Now this is what Dangeriz
suggested in the begining - but i need to sought this out automatically, otherwise i can't get the product verified by microsoft

any ideas ?

thanks again

CraigLazarAuthor Commented:
Hi Thanks for the help guys
Dangeriz it worked perfectly, thanks
i don't suppose u know how in code i can pick up if the file system is NTFS ?
I can pick up what OS is running ?

thanks Again

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.