By setting permissions you can control a user's
access to an object. For example, you can allow one user to read an object's
contents, but not change them. Permissions can be set for a specific user or an
entire group of users. When permissions are set for a group, every user in that
group inherits those permissions.
In the example below, the user created in the
section, "Creating Users and Groups" is granted permissions to read,
insert, update, and delete data.
DAO
Sub DAOSetUserObjectPermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim doc As DAO.Document
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the Customers table
Set doc = db.Containers("Tables").Documents("Customers")
doc.UserName = "MyUser"
doc.Permissions = dbSecRetrieveData Or dbSecInsertData _
Or dbSecReplaceData Or dbSecDeleteData
End Sub
ADOX
Sub ADOSetUserObjectPermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the Customers table
cat.Users("MyUser").SetPermissions "Customers", adPermObjTable, _
adAccessSet, adRightRead Or adRightInsert Or adRightUpdate _
Or adRightDelete
End Sub
The process for setting permissions with ADOX is
essentially the inverse of the DAO process. With DAO, you first select the
object and then indicate the user for whom to set permissions. With ADOX, you
first select the user and then specify the object on which to set permissions.
In addition, with DAO you set a series of
properties in order to set permissions on an object. In the example above, you
set the UserName property followed by the Permissions property. With ADOX, a single method, SetPermissions, is used to set permissions on an object. The SetPermissions method has parameters that map to the
properties used in DAO.
With the DAO Permissions
property, which maps to the Rights parameter of the
ADOX SetPermissions method, you supply a constant or
combination of constants that represent the permissions to set. The table below
shows how the DAO Security constants map to the ADOX Rights constants.
DAO
|
ADOX
|
dbSecNoAccess
|
adRightNone
|
dbSecFullAccess
|
adRightFull
|
dbSecDelete
|
adRightDrop
|
dbSecReadSec
|
adRightReadPermissions
|
dbSecWriteSec
|
adRightWritePermissions
|
dbSecWriteOwner
|
adRightWriteOwner
|
dbSecCreate
|
adRightCreate
|
dbSecReadDef
|
adRightReadDesign
|
dbSecWriteDef
|
adRightWriteDesign
|
dbSecRetrieveData
|
adRightRead
|
dbSecInsertData
|
adRightInsert
|
dbSecReplaceData
|
adRightUpdate
|
dbSecDeleteData
|
adRightDelete
|
dbSecDBAdmin
|
adRightFull
|
dbSecDBCreate
|
adRightCreate
|
dbSecDBExclusive
|
adRightExclusive
|
dbSecDBOpen
|
adRightRead
|
As shown in the table above, DAO has specific
security constants for setting permissions on a database. These constants are
used with the Databases container or a database object. In the following
listings, you can see how to use both DAO and ADOX to set permissions for a
user on a database object.
DAO
Sub DAOSetDatabasePermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim doc As DAO.Document
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the current database
Set doc = db.Containers("Databases").Documents("MSysDB")
doc.UserName = "MyUser"
doc.Permissions = dbSecDBExclusive
End Sub
ADOX
Sub ADOSetDatabasePermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the current database
cat.Users("MyUser").SetPermissions "", adPermObjDatabase, _
adAccessSet, adRightExclusive
End Sub
Setting permissions for a database differs
slightly from other objects. When using DAO, you must specify
"MSysDb" as the document name when you want to specify permissions
for the current database. To do the equivalent in ADOX, specify an empty string
("") as the name of the database.
In addition to granting permissions to a user on
specific objects you may also want to specify permissions for a class/container
of objects such as Tables. When specifying permissions on a container, you can
indicate whether new objects of that class created by the user should inherit
those permissions by default.
DAO
Sub DAOSetUserContainerPermissions()
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim ctr As DAO.Container
' Open the database
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
Set db = wks.OpenDatabase(".\NorthWind.mdb")
' Set permissions for MyUser on the Tables Container
Set ctr = db.Containers("Tables")
ctr.UserName = "MyUser"
ctr.Inherit = True
ctr.Permissions = dbSecRetrieveData Or dbSecInsertData _
Or dbSecReplaceData Or dbSecDeleteData
End Sub
ADOX
Sub ADOSetUserContainerPermissions()
Dim cat As New ADOX.Catalog
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;User Id=Admin;" & _
"Password=password;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
' Set permissions for MyUser on the Tables Container
cat.Users("MyUser").SetPermissions Null, adPermObjTable, _
adAccessSet, adRightRead Or adRightInsert Or adRightUpdate _
Or adRightDelete, adInheritNone
End Sub
With DAO, the Container
object was used to specify permissions for a class of objects. With ADOX,
setting the Name parameter of the SetPermissions
object to Null sets permissions for the class of objects specified by the ObjectType parameter. The InheritType
parameter of the ADOX SetPermissions method indicates
whether new objects should inherit the permissions. This is equivalent to
setting the DAO Inherit property. In the ADOX example,
the InheritType parameter must be set to adInheritNone since it is setting the permissions for an
existing object instead of a new object.
Use the Containers and Documents collections in DAO to set permissions on
Access-specific objects, such as Forms, Reports, and Macros. Although ADOX
supports setting permissions for Access-specific objects, the Microsoft Jet 4.0
Provider doesn't properly map the ADOX permissions flags to Microsoft Jet
permissions flags. If you need to set permissions for Access-specific objects,
you must use DAO until this problem is corrected.
EmoticonEmoticon