Question

Access 2000 DAO Security

Asked by: Gudare

Short version: Does Jet Engine 4.0 automatically reset the User's dbSecCreate property on the Tables Container, in contrast to Jet 3.5? If so, I need documentation of this, do you know where to find any?

(I currently have a blurb from the Sybex Access 2000 Developer's Handbook Volume:2 stating it can't be done, but it doesn't say a) Why? and b) What changed?, which would be preferable)

Long Version to recreate:
Secure your database, then create 'TestUser'. Leave the TestUser in the Users group only.

Strip all permissions to all tables from TestUser and Users group, just to be sure (not entirely necessary for test but you'll see where I'm going shortly).

Head to a module and turn on DAO 3.6.

Drop the following code in:
Sub Main()

     Dim db as DAO.Database
     Dim cnt as DAO.Container

     Set db = CurrentDB()
     Set cnt = db.Containers("Tables")

     cnt.UserName = "Users"
     Debug.Print "Users Before Change: " & cnt.Permissions
     cnt.Permissions = cnt.Permissions AND Not dbSecCreate
     Debug.Print "Users After Change: " & cnt.Permissions
     db.Containers.Refresh
     Debug.Print "Users After Refresh: " & cnt.Permissions

     cnt.UserName = "TestUser"
     Debug.Print "TestUser Before Change: " & cnt.Permissions
     cnt.Permissions = cnt.Permissions AND Not dbSecCreate
     Debug.Print "TestUser After Change: " & cnt.Permissions
     db.Containers.Refresh
     Debug.Print "TestUser After Refresh: " & cnt.Permissions

     Set cnt = Nothing
     Set db = Nothing

End Sub

If you review the Debug Window, it appears to be turned off, successfully, as you'd expect.

Now, head to your database window and attempt to create a new table or query.

You will be Successful.

With the old Jet 3.5 (Access 97) Engine, you were locked out of the ability to create these. Forms/Reports/Modules/Macros were not locked in 97, but that was dealt with via usage of .mde.

Can I remove the ability to open exclusive and not save the query/table? Yes. My issue is protecting the data in 'With Owner Access' queries that can be bypassed by creating a temporary query (unsaved) allowing modification of that data *by the user*, which I wish to disallow.

Immediate assistance on this issue would be extremely helpful, I'm staring at a project release date that this needs to be dealt with for.

Thanks in advance.

-Craig

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2001-11-20 at 16:45:14ID20237671
Tags

dbseccreate

Topic

Microsoft Access Database

Participating Experts
3
Points
0
Comments
12

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. query in DAO
    How to use query to find the data from the database. Let assume, in abc table, I have : name, id, age, address, phone number and I want to select : 1. name. 2. age and id and phone number. How to do that ? p/s : I have data control for DAO on the form and I don't use ODB...
  2. DAO and the DataReport
    Hi All, This is a multi-part question, so I'll classify it as 'hard.' (VB6 w/ the latest service packs installed) I'm writing an app that uses an MS Access database. For now, I'm just using the DAO 3.6 library and doing everything in code in order to keep my dependencies ...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: devthaPosted on 2001-11-21 at 11:24:59ID: 6648416

Try this

'Call the function (true will give you permissions)
Call setPermissions("SysUserLastUpdate", True)


Public Function setPermissions(tablename$, bYesNo As Boolean) As Boolean

Dim db As Database
Dim doc As Document

Set db = CurrentDb()
'Set doc = db.Containers!tables.Documents!SysUserLastUpdate
Set doc = db.Containers!tables.Documents(tablename)
doc.UserName = "Admin"
If bYesNo Then
    doc.Permissions = dbSecWriteDef Or dbSecRetrieveData Or dbSecInsertData Or dbSecReplaceData Or dbSecDeleteData
Else
    doc.Permissions = dbSecRetrieveData
End If



 

by: GudarePosted on 2001-11-21 at 11:59:49ID: 6648493

devtha,

Agreed, if I was concerned with locking tables, I could lock each one. They are locked. The user has no direct access to the table.

They have access through Run With Owner's Permissions queries. I need the mde to be able to run items against these queries, so the user has 'snapshot' settings for the recordset returns, but if you use this, and build a query off a snapshot query, it acts like a normal query (it's a quirk of using qdf's).

I need to *stop the user from making new queries*. The rest is secured properly, only the creation of new queries is the issue.

What you gave me above will lock down permissions on specific *existing* objects. :)

New objects are *supposed* to be controlled via the container, not a document, since the container controls all new objects. Destroying the dbSecCreate permission on the 'Tables' container in 97 removed the ability to create new queries/tables. This is no longer working in Access 2000. I need to know either why, or (preferably) I need a workaround that *will* halt the creation of new queries.

Thanks for the thought, but not what I'm looking for. :(

-Craig

 

by: devthaPosted on 2001-11-21 at 12:03:00ID: 6648499

Craig this is a list of values that you can use instead..

dbSecCreate     The user can create new documents (not valid for Document objects).
dbSecReadDef     The user can read the table definition, including column and index information.
dbSecWriteDef     The user can modify or delete the table definition, including column and index information.
dbSecRetrieveData     The user can retrieve data from the Document object.
dbSecInsertData     The user can add records.
dbSecReplaceData     The user can modify records.
dbSecDeleteData     The user can delete records.

 

by: devthaPosted on 2001-11-21 at 12:04:17ID: 6648503

you can loop thru the containers collection and set it with your selective options...

 

by: devthaPosted on 2001-11-21 at 12:07:51ID: 6648514

Sorry that was for table container
here is for all other container

dbSecNoAccess     The user doesn't have access to the object (not valid for Document objects).
dbSecFullAccess     The user has full access to the object.
dbSecDelete     The user can delete the object.
dbSecReadSec     The user can read the object's security-related information.
dbSecWriteSec     The user can alter access permissions.
dbSecWriteOwner     The user can change the Owner property setting.

I think you can use dbSecNoAccess

 

by: GudarePosted on 2001-11-21 at 12:29:52ID: 6648574

devtha,

Have you tested this in Access 2000?  The code I posted above modifies those settings.

It *doesn't* work after testing.

You'll note you listed the dbSecCreate item that I've mentioned. Yes, I'm aware of these properties that are supposed to be available via DAO. Yes, they work in 97.

Yes, I have my own copy of the Developer's Handbook. :)

What I need to know is WHY these will not work in 2000 and an alternative method to stopping creation of queries, not the old way DAO intended to do it, cause this doesn't WORK in 2000 anymore.  

Please test it yourself to prove I'm not nuts (Code is in first item on question). It turns off the permission on the container, yet you still get to create! :)

Thanks. :)

-Craig

 

by: devthaPosted on 2001-11-21 at 12:36:10ID: 6648591

Look if you need answers you have to be patient or you do not belong here. As this is not fetching money and no one is doing it on a full time basis. It is just fun to help.
Sometimes there is not even enough time to concentrate.
I think you can browse the Developers handbook and look for answers...
I shall try it on 2k though for my study.

 

by: GudarePosted on 2001-11-21 at 12:44:43ID: 6648616

I'm sorry you feel I have no patience, and yes, I'm well aware there's no money being transferred here, I've poked my nose around and answered a few (nowhere near as many as yourself) questions as well.

My initial question was longwinded, yes, but I felt it was required for the depth of this question and to show what I had tried. Specifically to your comment on the Developer's Handbook, Pg 361 in Volume 2 for Ac2K states you can't. *shrugs* They've been wrong (albeit rarely) before, I'm hoping they are this time.

Sorry for wasting your time and making you annoyed. Hopefully another will have more patience with me in assisting me with this issue and will review the clarifications we have made for an alternative.

Thank you, as I have said and apparently you didn't believe me, for your assistance thus far.

-Craig

 

by: devthaPosted on 2001-11-21 at 12:49:28ID: 6648629

The reason of putting the function was to show a different method of setting these properties and not to show what is available. That list came at a later stage.
Cheers..

 

by: GudarePosted on 2002-01-07 at 16:57:04ID: 6716555

I'd like to delete this question as I've solved it via a different method and have not gotten any alternative information since discussions with devtha.

If no experts have any disagreements, I'll mark it for deletion in a few days.

-Craig

 

by: nico5038Posted on 2002-05-11 at 05:50:55ID: 7003096

for Gudare

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded when solution is posted
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)

 

by: NetminderPosted on 2002-05-22 at 11:22:29ID: 7027821

Per recommendation, points NOT refunded and question closed by
Netminder
CS Moderator

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...