Solved

Need to recover read/write permissions to MsAccess database tables

Posted on 2006-07-12
27
526 Views
Last Modified: 2012-05-05
Hello,

we have an MS Access database that was created by someone who has left our firm (someone who really knew MsAccess).This database contains some product statistics that we now really need. We here dont know much about Access, we had a password written down so we thought thats all there is to it. Unfortunately now when we needed some info in the DB we found that even after supplying the pass that Access required at open, we cant open a single table :(
it says:                   Could not read deffinitions; no read definitions permission for table of query ' offer '.
when we click on a table.
Does it mean the DB is broken? Can we retrieve our data or has it become corrupt ? Someone suggested its only permisson problem and it can be solved.Is true?

Thank you enormously !
Bob
0
Comment
Question by:ghostd0g
  • 13
  • 8
  • 6
27 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Hi ghostd0g,
The message does not mean the database is faulty.
It means it is secured and you need the correct username and password to access your data.

Your best hope here is to contact the original developer and get the necessary details you need from them.  You have recourse to legal remedies if necessary.

No-one at EE can help you bypass security; it is against EE rules and a member's account could be suspended if they do so.
The issue is that no-one can verify that you are the true owner of the database and it is illegal to assist anyone in gaining access to a system they are not authorised to access.

It is true that a search of the net will provide many potential sources of software which claim to be able to be able to discover usernames and passwords from an Access Workgroup file (.mdw).  And there are some companies which will do that sort of thing for you.  Of course there are arms and legs involved in return.


Pete
0
 

Author Comment

by:ghostd0g
Comment Utility
Hi Peter,

Its great to hear that indeed the file is safe and sound, thats a big relief ! This means that our supposition that its only a permission problem is true. As you said, we did ponder going "legal" but in this godforsaken country things would get done in a few years minimum, if at all... so that is pretty much useless.

We did a lot of googleing about Access errors and just as you said there seems to be a lot of programs that find MSaccess passwords. Risking to get who knows what virus or spyware we tryied a couple of them but the result was simply the password we already knew..
I see you talk about a username? MSAccess never asked for a username, simply the pass!!! I also see you say "Access Workgroup file (.mdw) " but we only have a .mdb file ? Was there another file that got deleted ?(or not backed up?) There are supposed to be 2 files ?
Im really sorry about these questions, but almost all that me or my colleagues know about MSaccess comes from google...
We would even pay someone to do whatever (although frustrating!) but the local people that we knew are the computer type arround here seem to also be ignorant of this MSaccess program. Maybe im not searching correctly in google but MSAccess doesnt seem to be very well documented or popular... again this is just an impression formed in the last 2 days :)

Bob

ps: for people hiering other people: take the time to check with their last boss or something, it might save you a lot of anger
0
 

Author Comment

by:ghostd0g
Comment Utility
i found a post here on  www.experts-exchange.com :

" Accepted Answer from ahmedbahgat
Date: 07/29/2003 08:27AM EEST
Grade: A
      Accepted Answer       

the system.mdw is the default security file that is created when you install access for the first time, it seems that this file is missing from its default location "the windows system directory", try to search for the file on your hard disk then copy it to the windows system directory, then try starting access otherwise, use MS Access Workgroup Administrator to create a new one

cheers "

Does this mean becaus we reinstalled Windows or the Microsoft Office package this happend?
0
 
LVL 84
Comment Utility
Typically the error you received comes from insufficient Workgroup permissions, as Pete said.

However, you indicate that you've never used a Username, only a password. Access databases can be simply password protected, which may be what you have run into ...

How do you open the database? Do you use a desktop or start menu shortcut? If so, review that shortcut by rightclicking on the shortcut and click Properties ... then look at the value in the Target box ... does you see a "/wrkgrp" (without the quotes) in there? If so, then your db is probably secured through User Level Security, and, as Pete pointed out, we really can't help you break it.

< MSAccess doesnt seem to be very well documented or popular>

Access is most likely the most popular desktop database program available, and the userbase is enormous. What isn't well documented is User Level Security, which is what your problem revolves around. ULS is the security model used by Access and it is, by necessity, not extremely well documented.

<Does this mean becaus we reinstalled Windows or the Microsoft Office package this happend?>

No ... the system.mdw file is exactly the same across all Access installations for the same version (i.e. my sysytem.mdw file for Access 2002 will work on your machine). Much poor information is parried about here in regards to ULS, so take any postings you find with a grain of salt.

Do a search for any files ending in .mdw ... you'll probably find several named system.mdw, by the way, but if you find others, try "joining" that workgroup to see if that's your correct workgroup. To Join the workgroup, open Access (don't open a database) then click Tools - Security - Workgroup Administrator ... click the Join button, then try to open your database.
0
 
LVL 84
Comment Utility
Note also: you cannot create a "new" system.mdw file, since you don't know the correct Workgroup info to do so ... you can, however, delete the system.mdw file and Access 2000 or later will create a new file for you ...
0
 

Author Comment

by:ghostd0g
Comment Utility
OK, this is a lot of information to take in at once for me.Ill try to respond punctually.

<  How do you open the database? Do you use a desktop or start menu shortcut? If so, review that shortcut by rightclicking on the shortcut and click Properties ... then look at the value in the Target box ... does you see a "/wrkgrp" (without the quotes) in there? If so, then your db is probably secured through User Level Security, and, as Pete pointed out, we really can't help you break it.  >

To open it i simply click on it. It is a file on my desktop called  " old-offer.mdb " (without the quotes). I rightclicked on properties and me and a colleague both looked for 5 minutes but could not find Target or "/wrkgrp". Does this mean we are wasting our time and the file is to complexly encripted?

I only found one System.mdw ( I am to understand from you guys that this new file is the problem and not the actual database?).
I went to:  Tools -> Security -> WorkGroup Administrator
There are      Name:   computername   Company:  myname    Workgroup: C:\path\to\System.mdw

IF i click   Create button i get:     Name:        
                                                Organization:
                                               WorkgroupID:

Question: For it to "work"  we need  the exact 3 things that were typed in when the database was encripted?  Name:  Organization: and WorkgroupID: ? Or 5 things including Name and Company like above?
These "things" cannot be somehow/magically/wishfull thinking  deducted from the .mdb file ?

Thank you I hope i havent mixed up the terms together
Bob  
alexandru.octavianus(at)gmail.com
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
It sounds to me that your situation is not good.

Access has 2 types of security that can be used.
First, there a simple database password.
Secondly, there is User Level Security or Workgroup security.

It sounds to me that the developer has used both of these.
You might be able remove the database password by opening the database using the 'Open Exclusive' option, while holding down the shift key.
The Open Exclusive option is found if you start Access (NOT double-click the database) and then use File>Open. Then instead of just clicking the Open button, you use the drop-down list next to the open button to choose Open Exclusive.  As I say, hold the shift key down as you click the OpenExclusive option.
If you now have the normal Access menus (you might not ) then you can go to Tools>Security>Unset Database Password.
However, this will get you very little, other than not having to provide the password when you start the database. So it doesn't really matter if you can't do this.

The message in your original post indicates that workgroup security has been applied.  Now it is possible to set up this security such that you do not need to log in using a username and password and this appears to be the case here.  The permissions you have will be the permissions available to the default user who is called Admin.  When this type of setup is used, the mdb file can be distributed to anyone in the world and it will be secure but usable in the same way that you can use yours.
When you set up security using this technique, you use a specific mdw file while you are setting permissions, but then you do not distribute that mdw file with the application. When you run the application, the default system.mdw is used, which always contains the default Admin user.
Therefore, and this is now how serious your situation is, unless you can get the mdw file that was used to set permissions, you have no possibility of gaining access to the database beyond the level that the developer has set for the default user. Ever. Not even using password cracker programs.
So you need to search your drives for any .mdw files you can.  Well, specifically, you need to search the drives the developer had access to, because that's where the correct mdw file will be if it is anywhere.

To get any further, you need the username and password of the user who has been designated as Administrator AND you need the workgroup file (mdw file) that contains that user.  But without the mdw file even knowing the correct login details will get you nowhere.  

Sorry to be so gloomy about it all.

Pete














0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 63 total points
Comment Utility
Your database may or my not be encrypted, but that's not part of the problem here.

Stepping back - it appears that you can, indeed, open the database file (since you stated "we cant open a single table" in your original table, I'm assuming that you can get to the database window where you see the Tables, Forms, etc).

Open the Immediate window (Ctrl + G) and type this:

?currentuser

What does this return? Most likely Admin (the default user of an Access database), unless you also must supply a Username and Password to enter the db.

Pete is most likely right in that the db was secured using the system.mdw file, but unless your employee was VERY good with Access, there is the strong possibility that the was not secured properly ... have you tried moving the db to a different machine and trying to run it there?

Since you can (apparently) actually open Access, can you review the security settings? To do this, open Access, click Tools - Security, User and Group Accounts. See what Users and Groups are listed there, and note the group membership of each user.

Next, open the User and Group Permissions menu item and review the Owner for your objects (specifically your Database object) ... if the owner is Admin, then you may be able to recover ... if it's <unknown> then most likely Pete is correct and you are pretty much at a loss to recover your data.

Finally - have you tried to import all the objects from this db into a new, blank container? I doubt it'll work, since you don't seem to have read permissions, but it's worth a shot. Open Access, then open a new, blank database file, click File - Get External Data - Import, and find your problem database and click Open. If the import allows you to do so, select ALL your objects (note: on your Tables listing, do NOT import any tables beginning with MSys - those are system tables and Access will handle those as needed) and import them. Again, I doubt this will work.

More info:

The .mdw file is the "workgroup" file, which is basically the security file for Access. Security is enabled by default in Access, and all new installations use the system.mdw file for security. The .mdw file stores Users, Groups, Groupmemberships for Users, and passwords (acutal permissions for objects are stored in each database).

Typically this file contains two groups - Users and Admins - and one user - Admin, who is a member of both the Users group and the Admins group. If you review a typical Access database and review the Security settings, you'll find that the Admin user "owns" all objects (with the exception of the system objects). The System.mdw file, along with the Admin user, are identical across all installations of Access, at least for identical versions. That's why I can build a database on my machine, ship it to you, and you can open it - we BOTH do this as the default Admin user, assuming that I haven't implemented my own security file (the .mdw file).

When you secure a database you bascally (a)create a new workgroup file and (b) take over ownerships of all Objects and (c) remove permissions from the Users group and (d) set permissions on your custom groups. This is highly condensed, of course, and doesn't take into consideration many factors but that's basically what you do.





0
 

Author Comment

by:ghostd0g
Comment Utility
Hi again,

First, thank you for replying

Ok, so as Pete said i removed the password, things are still the same but now at least there is no need to type a password every time.

I did a Control+G  and there was a panel in the down of the page and i typed ?current user and Admin appeared underneath  it.
Yes we tryied many machines with access 97, XP, 2003. (later we have established that the file is in 2000 MSAccess version)
I tryed to import but the same happens, i select the table i want (not the ones that start with SYS_..) but no luck.

I doubleclick on the database, i go to : Tools -> Security -> User And GROup Accounts   and there is only Admins and Users Groups
and i can only find the Admin user.
I am not sure if this helps or not but god might have sent us a little luck, someone here downloaded a program from the internet that somehow analisez  the file (the mdb file) (this is after we removed the passsword) and sais that the owner of the table is "SomeName/noun". Becaus MSAccess never asked for a username we never typed in one ( like one would do when logging in to yahoo or someplace, it was just one password field ).
Logically I try to create THIS username and add it to Admins group, shouldnt that work or its way more complex than that ?

Best regards,
Bob
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
It's not quite that simple.
When you create a user you have to provide a username and another value called a PID.
It is a combination of these which establish the user in the database.
Unless you know the PID that was used to create the user originally you cannot create the identical user.
(When you go through the standard procedure to implement securty, Access issues a warning telling you to record the username and pid values so that you can re-create the users if you lose the mdw file).

As for your inspection of the Security settings, you can only find the Admin user because that's all there is in the mdw file you are using.  The somename/noun user would have been set up in the developer's mdw file when security was implemented fully.

I assume that Admin is NOT a member of the Admins group?

Pete
0
 

Author Comment

by:ghostd0g
Comment Utility
Hi
It feels like the more this exploration into this science called MSAccess goes the more variables appear.
OK, I read your message more times so that I can understand, my question is:
This " PID " thing that you say, is it like a password? (another password than that we had)

IF we had this " PID " of the user which we discovered created the tables COULD we then finally at least read the darn tables ?

Yours gratefull,
Bob

ps: im not sure if admin is or no member of Admins, someone may have clicked on remove unintentionally...
0
 
LVL 77

Accepted Solution

by:
peter57r earned 62 total points
Comment Utility
When you create a user you have to supply two pieces of information:
Username - self explanatory
PID - this is 'like' a password in the sense that it a value that is permanently tied to the username in the workgroup you are currently joined to when you add the user.

Access then derives a user identity from these two bits of information and the userid is established in the database using this derived value.  At this stage the user has no password.
When that user first logs in they just supply the username.  They can create their own password once logged in and from that point, the login requires username and password to gain access.
The username, PID and password are stored in the mdw file (not in readable form). So initial user authentication is done by matching to values in the workgroup the user belongs to when they start Access.  If they provide a correct username and password then the username/pid derived value is confirmed to the database.  So long as the same derived value is supplied as was derived when the user was created the user acquires the permissions that were set up for them by the Administrator.

So coming round to your q.
If you know the username and pid for a user you can recreate that user identity in any workgroup file.  The password is not required because THIS new workgroup file has not been used by that user to create a password and the database itself knows nothing about passwords.

But if you don't know the pid value that was used to create the user in the first place, you cannot set up a user with the same credentials that the database knows about.

This why almost all service cos and 'password cracker' programs require you to have the mdw file that was used to create the users.
(and why my responses to this q have been in the 'doom and gloom' category(:-))

Pete

 



0
 

Author Comment

by:ghostd0g
Comment Utility
Hi,

Thank you for reply Pete !

So, am I to understand that: yes, if the same exact PID ( alphanumeric "word"? ) and Username were supplyed than our tables would be human readable?      or    no, pid and username are not enough and more things are needed in "my" specific situation.

By the way: can I post a URL to another page or is it against the rules?

Looking forward to an reply,
Bob
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:ghostd0g
Comment Utility
ps: not i understand what "gloomy"-word means. (i didnt find edit button,sorry)
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Specifically, you need to be able to re-create the user that has been set as administrator.  This wil almost certainly be the user who is designated as the Owner of all the objects.

It should be OK to post a link if it is directly relevant to the Q.  

I didn't realise you were translating!

Pete
0
 

Author Comment

by:ghostd0g
Comment Utility
sorry i mistyped, it was: noW i understand what gloomy meant :)

ok, here is a link to what we found: http://www.thegrideon.com/accesspass.html

I know its not any of your responsability but could you please take a short look at that page/ program?
Is that true what it says there? Or did i not understand correctly and that piece of program is not relevant/helpfull for this specific problem that we have here.

Thank you very much
Bob
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Sorry, you will have to make your own mind up about that.

Pete
0
 

Author Comment

by:ghostd0g
Comment Utility
I know I know dont worry about that, but im just asking if that is what we need or not need ( or not enough).


Thanks,
Bob
0
 

Author Comment

by:ghostd0g
Comment Utility
Hi again,

Ok so now i know the PID+username, do i have a chance ?

Thanks
0
 
LVL 84
Comment Utility
You can obviously login to the db, otherwise you couldn't get to the VB Immediate window, or view the database listing.

When you click Tools - Security - User and Group Accounts, is the New button greyed out? If it is, then you can't add a new user and you're stuck - time to get an outside party involved, or use one of the tools you can locate via a search engine. If not, read below.

If it's not greyed out, then you've obviously logged in with an account with sufficient permissions to add users, which typically means that the user is a member of the Admins group. If that's the case, then you don't need to worry about all this - just change the ownership of all the objects to the current login (whatever that name is) and you should be done. To do that, open your database and click Tools - Security - User and Group Permissions, then click the Change Owner tab. Try to change the ownership of all objects (including the database object) to Admin (or whatever login you're using that's a member of the Admins group) ... assuming that you haven't changed any permissions for the Admins group, you should be able to take ownership of all objects.

If you can do this, then I'd also immediately build a new, blank database and import all objects from your old db into your new one.
0
 

Author Comment

by:ghostd0g
Comment Utility
OK, its no greyed out, but its still not working.

Here is what i did:

Created the owner username with his PID.
Open Access using the commandline  :   C:\Program Files\Microsoft Office\Office10>MSACCESS.EXE /user
Access opens and ASKS FOR A USER + PASS: i type the owneruser and i leave the pass empty ( i have tryed also with the oldpass)
I enter the db same as before and ctrl+g ?currentuser:  owneruser
I try to open any table (actually its only one with real data but that doesnt matter) but i get the same error, I try to switch permissions to Admin in the User and ZGroup Permissions but i get the same error ( that i dont have administer rights )
0
 
LVL 84
Comment Utility
Make sure to add your new user to the Admins group.

0
 
LVL 84
Comment Utility
Login with the Admin username again ... use your commandline, enter Admin with no password at the prompt ... do a Ctrl + G and type this:

?Currentuser

?DBEngine.SystemDB

What do you get?
0
 

Author Comment

by:ghostd0g
Comment Utility
?Currentuser
Admin
?DBEngine.SystemDB
C:\Documents and Settings\Administrator\Application Data\Microsoft\Access\System.mdw

(its the only system.mdw in the directory + i clicked on it and there is the newuser in there )


.......

Shit something just happend :) .. in User and Group Permissions  being logged in with the owneruser i ticked 'read data"
and I think it worked, i can see our table !!!! I hope this isnt a fluke or anything...
Its 4 am in the morning... i may actually get some good sleep !

Thanks you guys ! Youve been marvelous !

ps: i really hope im not dreaming or its just some random fluke ( or fate tortureing me )
0
 
LVL 84
Comment Utility
I think you've got it ... best of luck, and please post back if this works or not ... you can buy me and Pete a beer the next time you see us <g> ...
0
 

Author Comment

by:ghostd0g
Comment Utility
yep i think by all appearances its working, hope i dont reboot the pc and it goes evil on me :)

As many beers for you guys as you want !!!!

i got lucky with that program there, smart guys those who did it, wonder how it works..... ah the joy !

ps: how I accept answer from you guys both ?

best regards
Bob
0
 
LVL 84
Comment Utility
Here's how to accept and split points:

http://www.experts-exchange.com/help.jsp#hi68

FWIW: I'd do this, so you don't have troubles down the line (note: make a copy of your current db first).

Open the database with the default system.mdw file (which is what you're doing now). Login with the Admin user.
Change the ownership of all objects to Admin.
Close that database
Build a new, blank database.
Import all objects from the old database into the new database (note: don't import any tables beginning with MSys).
Use the new database for your reports and such
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now