Can I add permissions to access forms & tables in code

[Stapes]

Hi
I have set up my database with a workgroup, groups & users, and have
successfully made a form that allows certain users to add users,
delete users or add a user to a group, display all the Group / User
info or all the Permissions in the Containers collection. But can I
assign permissions to access forms and tables in code?

This is how I add a user: -

Private Sub Command1_Click()
On Error GoTo Command1_Click_Err
' Add User
MsgBox Newuser & "," & Newgroup & "," & Newpid & "," & Newpw & vbCrLf
& _
"Caution: The new Group / User will not appear in the Group / User
window" & vbCrLf & _
" until you close" & _
" the database & re-open it"
Dim wrkDefault As Workspace
Dim usrNew As User
Dim usrLoop As User
Dim grpNew As Group
Dim grpLoop As Group
Dim grpMember As Group
Dim GrpUsrInfo As String
GrpUsrInfo = ""

Set wrkDefault = DBEngine.Workspaces(0)

With wrkDefault

' Create and append new user.
Set usrNew = .CreateUser(Newuser, Newpid, Newpw)
.Users.Append usrNew

Set grpMember = usrNew.CreateGroup(Newgroup)
usrNew.Groups.Append grpMember
End With

Me!GroupUserInfo = GrpUsrInfo
usrNew.Groups.Refresh
GroupX
Exit_Command1_Click:
Exit Sub

Command1_Click_Err:
MsgBox Err.Number & Err.Description
Resume Exit_Command1_Click

End Sub

Stapes

 

[Guest]

Its some time since I've done this sort of thing so I hope my memory serves
well! You set the Permissions property of the Document object on the basis
of its UserName property. The relevant permissions are set by bitmasking
using Boolean operations, so to set insert and retrieve data permissions for
instance to a table selected from a combo box on the form for the user
identified in the NewUser control on the form:

Dim dbs As DAO.Database
Dim doc As DAO.Document

Set dbs = CurrentDb

Set doc = dbs.Containers("Tables").Documents(Me.cboTables)

With doc
.UserName = Me.NewUser
.Permissions = dbSecRetrieveData Or dbSecInsertData
End With

To take another example, say you want to give the user all permissions for a
form apart from the ability to delete the form:

Dim dbs As DAO.Database
Dim doc As DAO.Document

Set dbs = CurrentDb

Set doc = dbs.Containers("Forms").Documents(Me.cboForms)

With doc
.UserName = Me.NewUser
.Permissions = dbSecFullAccess And Not dbSecDelete
End With

Mostly, however, you'd grant permissions to groups rather than individual
users, and make the new user a member of the relevant groups, so you don't
need to grant the user individual permissions to a large number of objects.
For this you'd specify the group as the UserName property when setting the
document's permissions.

Ken Sheridan
Stafford, England

 

[Stapes]

Hi

I was hoping that I could assign permissions to the groups using the
Tools menu, then assign users to groups, and they would inherit the
permissions from the group.
It is very boring, but it does not seem to work this way. I assign my
users to groups, and they still can't get in to the database, even
though I have set up all the permissions for the group!
So do I have to individually assign each user permissions to each
specific form, table or query they might need?

Stapes

 

[Guest]

You are quite right; the usual way is to grant permissions to each group and
then make each user a member of one or more groups. The code to add a user
to a group would go along these lines, where cboGroup is a combo box from
which a group is selected and cboUser is one from which the user is selected:

Dim wks As Workspace
Dim usr As User

Set wks = DbEngine(0)
Set usr = wks.CreateUser(Me.cboUser)

wks.Groups(cboGroup).Users.Append usr

Note that the CreateUser method doesn't create a new user here; that will
have been done already. It returns a reference to the user.

The whole point of groups is that it removes the need to give individual
users permissions to every object. You can do this in addition to the
permissions they enjoy as a member of a group(s) of course where you want to
extend a particular user's permissions beyond those shared with other group
members.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

So do I have to individually assign each user permissions to each
specific form, table or query they might need?

No; I never assign permissions to individuals - only to groups, and
then make individuals members of the appropriate group. There is
something else wrong here. Have you studied (carefully!) the Access
security FAQ? Do the users get a logon prompt and enter their password
when opening the database?

John W. Vinson [MVP]

 

[Stapes]

Hi
I have assigned permissions to groups.
I get a variety of errors when I try to log in.
'Record(s) cannot be read. No read permission on '<SysyAccessObjects'

or

3112 No read permission on MSysyUserList.

Some users in both groups work - the ones I created in the Tools menu.
It is the ones I have added since that give errors.

Stapes

 

[Stapes]

No; I never assign permissions to individuals - only to groups, and
then make individuals members of the appropriate group. There is
something else wrong here. Have you studied (carefully!) the Access
security FAQ? Do the users get a logon prompt and enter their password
when opening the database?

John W. Vinson [MVP]

Hi
I created my first groups & users using the Tools menu. They all
worked fine.
Since then I have set up code to add new users and add them to groups.
It is these ones that are not working.
I get a variety of error messages - depending on which group I try to
log in as.

'Record(s) cannot be read. No read permission on 'MSysAccessObjects'

or

'3112 No read permission on MSysUserList'

Stapes

 

[Stapes]

Hi
I created my first groups & users using the Tools menu. They all
worked fine.
Since then I have set up code to add new users and add them to groups.
It is these ones that are not working.
I get a variety of error messages - depending on which group I try to
log in as.

'Record(s) cannot be read. No read permission on 'MSysAccessObjects'

or

'3112 No read permission on MSysUserList'

Stapes

Further to that. If I use the tools menu & delete the offending
username & recreate it, & assign it to the same group, it works fine.

Stapes

 

[Joan Wild]

When you create new users via code, you must ensure that you add them to the
Users Group, as well as any of your custom groups.

You'll notice when you create a user via the security dialogs, that they are
automatically put in the Users Group. That's a requirement in order to use
Access.

Also, I hope you aren't messing with the permissions on the MSys... Objects.

 

[John W. Vinson]

Further to that. If I use the tools menu & delete the offending
username & recreate it, & assign it to the same group, it works fine.

See Joan's comment. Also, be sure that the group has permissions *to
the Database* itself, not just to the forms and queries.

John W. Vinson [MVP]