Newsgroup: it.comp.lang.visual-basic
Titolo: Aiuto!!: MDB con password

Salve,
qualcuno sa dirmi come posso fare per proteggere un DB access (.mdb)
utilizzato non in ODBC in un programma visual basic 6?
In altre parole, vorrei sapere come si fa da VB a settare una password per
un certo DB e di conseguenza come si fa ad aprirlo ed operarci sopra.
Un metodo altenativo potrebbe essere quello dell'encription del DB ma qui ne
so ancora meno.
Grazie in anticipo per l'aiuto
gigi
p.s. vi sarei grato se spediste le eventuali risposte anche a:
gigil...@iol.it

utilizzato non in ODBC in un programma visual basic 6?
se intendi che usi un data control per accedere al DB:
Puoi impostare la PW in access a livello di database da menu Strumenti ->
Sicurezza -> Imposta PW
a livello di codice durante il load del form
scrivi
Set DBase = OpenDatabase(DataBase, False, False, "; pwd=miapassword")
questa istruzione lascia funzionare anche i datacontrol collegati al
database
pero avrai qualche problema in edi in quanto ti chiede continuamente la PW
Ciao Stefano

 utilizzato non in ODBC in un programma visual basic 6?


Ti invio un articolo tratto da MSDN, tratta la sicurezza e la gestione trovi
esempi di come modificare la password sia a livello utente che password di
database, sia con DAO che con ADO (in verità usa JRO)
Ciao Luca
Security
Microsoft Jet databases can be secured in one of two ways: share-level
security or user-level security. For share-level security, the database is
secured with a password. Anyone attempting to open the database must specify
the correct database password. For user-level security, each user is given a
user name and password to open the database.
Changing a Password
The first step in securing a Microsoft Jet database is to change the
password for the Admin user, if using user-level security, or changing the
database password if using share-level security. When changing a password
for a user or database, you must supply both the existing and new passwords.
When changing the database or Admin user's password for the first time, use
an empty string ("") as the existing password.
The following code shows how to enable user level security by setting the
password for the Admin user to "password".
DAO
Sub DAOChangePassword()
   Dim wks As Workspace
   Dim usr As DAO.User
   ' Open the workspace, specifying the system database to use
   DBEngine.SystemDB = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
   Set wks = DBEngine.CreateWorkspace("", "Admin", "")
   ' Change the password for the user Admin
   wks.Users("Admin").NewPassword "", "password"
End Sub
ADOX
Sub ADOChangePassword()
   Dim cat As New ADOX.Catalog
   ' Open the catalog, specifying the system database to use
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
   ' Change the password for the user Admin
   cat.Users("Admin").ChangePassword "", "password"
End Sub
DAO and ADOX both have a method on the User object to change the user's
password. The method takes the user's current password and the new password
as parameters. In DAO this method is called NewPassword while in ADOX it is
called ChangePassword.
Note   The Microsoft Jet Provider will not error on the line of code that
opens the catalog if the system database specified is incorrect. However, it
will error when attempting to change the password or perform any other
security related operations with the following error if the system database
was not correctly specified: "The operation requested by the application is
not supported by the provider."
The following code shows how to change the database password for enabling
security at the share level.
DAO
Sub DAOChangeDatabasePassword()
   ' Make sure there isn't already a file with the
   ' name of the compacted database.
   If Dir(".\NewNorthWind.mdb") <> "" Then _
      Kill ".\NewNorthWind.mdb"
   ' Basic compact - creating new database named newnwind
   DBEngine.CompactDatabase ".\NorthWind.mdb", _
      ".\NewNorthWind.mdb", , , ";pwd=password;"
   ' Delete the original database
   Kill ".\NorthWind.mdb"
   ' Rename the file back to the original name
   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
JRO
Sub JROChangeDatabasePassword()
   Dim je As New JRO.JetEngine
   ' Make sure there isn't already a file with the
   ' name of the compacted database.
   If Dir(".\NewNorthWind.mdb") <> "" Then _
      Kill ".\NewNorthWind.mdb"
   ' Compact the database specifying the new database password
   je.CompactDatabase "Data Source=.\NorthWind.mdb;", _
      "Data Source=.\NewNorthWind.mdb;" & _
      "Jet OLEDB:Database Password=password"
   ' Delete the original database
   Kill ".\NorthWind.mdb"
   ' Rename the file back to the original name
   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"
End Sub
Note   JRO, not ADOX, is used to change a database password at share level.
Both DAO and JRO allow you to change the database password when compacting
the database. The syntax is slightly different: in DAO, specify
";pwd=password;" in the Password parameter of CompactDatabase. In JRO,
specify the provider-specific "Jet OLEDB:Database Password=password" in the
destination connection parameter of CompactDatabase.
Alternatively, the DAO code could be rewritten to use the NewPassword method
of the Database object.
Sub DAOChangeDatabasePassword2()
   Dim db As DAO.Database
   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", True)
   db.NewPassword "", "password"
   db.Close
End Sub
A similar mechanism is not currently available in JRO or ADOX. You must use
the CompactDatabase method in order to change the database password.
Creating Users and Groups
A User object represents a user account that has specific access permissions
while a Group object represents a group of user accounts that have common
access permissions. Creating users and groups allows you to easily control
and maintain users' access to the database and objects within the database.
The following code example shows how to create a new user.
DAO
Sub DAOCreateUser()
   Dim wks As DAO.Workspace
   ' Open a workspace
   DBEngine.SystemDB = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
   Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
   ' Create the user and append it to the Users collection
   wks.Users.Append wks.CreateUser("MyUser", "xNewUser", "password")
End Sub
ADOX
Sub ADOCreateUser()
   Dim cat As New ADOX.Catalog
   ' Open the catalog, specifying the system database to use
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
      "User Id=Admin;Password=password;"
   ' Create the new user and append it to the users collection
   cat.Users.Append "MyUser", "password"
End Sub
Unlike with DAO, with ADOX you do not have to create a User object before
adding the user to the database with the Append method. With ADOX you can
create a new user simply by passing the name and password to the Append
method of the Users collection.
Setting PID values
When creating a user or group in DAO, you must provide a value for the PID
parameter or property. This parameter is not required when creating a new
user or group in ADOX because the Microsoft Jet Provider automatically
generates PID values. This PID value is not retrievable using ADOX or DAO.
This presents problems if the workgroup information file, such as
system.mdw, becomes corrupted or destroyed. In order to recreate users and
groups and restore permissions to your databases and their objects, you must
know the PID values. Microsoft Jet combines the user or group name and the
PID value to create the identifier, called the SID, that it uses to control
the permissions of databases and objects. By recreating a user or group with
its original name and PID value, Microsoft Jet will create a SID value
identical to the original value, so all permissions set originally using
that SID will work again. Since you cannot set the PID value using ADOX, the
Microsoft Jet Provider effectively creates a new SID that does not have any
database or object permissions set for it.
There are two methods for working around this problem if you choose not to
explicitly set the PID values. One is to make frequent backups of the
workgroup information file, and restore the file from the backup when
necessary. This is usually the most efficient way to avoid the problem. The
second method is to recreate all of the permissions in the database to work
with the new users and groups that you will create. This is usually
difficult to accomplish efficiently. Both methods do not require you to keep
track of the PID values for users and groups.
Another way to avoid this problem is to use the new SQL Data Definition
Language (DDL) security commands available for Microsoft Jet 4.0. The
following code example demonstrates how to create a user and specify a PID
value using ADO and DDL.
ADO
Sub ADOCreateUser2()
   Dim cmd As New ADODB.Command
   ' Create the Command
   cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
      "User Id=Admin;Password=password;"
   ' Execute the DDL security command
   cmd.CommandText = "CREATE USER MyUser MyPW MyPID"
   cmd.Execute
End Sub
Adding a User to a Group
Adding users to a group makes maintaining permissions easier. Because users
within a group inherit the permissions of the group you can set permissions
once and have it apply to an entire group of users. For example, you can
assign update permissions for the Salary table to all managers by simply
granting the Managers group update permission.
The following code example demonstrates how to create a new group and add an
existing user to that group.
DAO
Sub DAOAddUserToNewGroup()
   Dim wks As DAO.Workspace
   ' Open the workspace
   DBEngine.SystemDB = _
      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
   Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
   ' Create a new group
   wks.Groups.Append wks.CreateGroup("MyGroup", "xMyGroup")
   ' Add the user to the new group
   wks.Users("MyUser").Groups.Append _
      wks.Users("MyUser").CreateGroup("MyGroup")
End Sub
ADOX
Sub ADOAddUserToNewGroup()
   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"
   ' Create a new group
   cat.Groups.Append "MyGroup"
   ' Add the user to the new group
   cat.Users("MyUser").Groups.Append "MyGroup"
End Sub
Both DAO and ADOX have a Groups collection on the Users object that can be
used to add the user to a group as well as to determine what groups the user
belongs to. However, note that with DAO you must create a new Group object
using the User object's CreateGroup method before appending the Group object
to the User object's Groups collection. With ADOX it is neither necessary
nor valid to create a new Group object; just append the name of the group to
the User object's Groups collection.
Note   Before using this technique to create a Group, see the comments about
setting PID values in the section "Creating Users and Groups."
Setting Permissions
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.
Determining an Object's Owner
The database, and every object in the database, has an owner. By default,
the owner is the user that created that object. The object owner has special
priveleges for that object in that he or she can always assign or revoke
permissions for that object.
The following listings demonstrate how to get the user name of the object
owner.
DAO
Sub DAOGetObjectOwner()
   Dim db As DAO.Database
   Dim wks As DAO.Workspace
   ' 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")
   ' Print the owner of the Customers table
   Debug.Print db.Containers("Tables").Documents("Customers").Owner
End Sub
ADOX
Sub ADOGetObjectOwner()
   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"
   ' Print the owner of the Customers table
   Debug.Print cat.GetObjectOwner("Customers", adPermObjTable)
End Sub
With DAO, you use the Owner property of a Document or Container object to
retrieve the user name of the object owner. With ADOX, you use the
GetObjectOwner method of a Catalog object. This method takes the object's
name and type as parameters.
