Encrypting data and networking question
Salad
Currently they use A2003 but want their main app ported to A2007.
The Access app uses mdw security. The Excel file is in a folder on the
H: drive. Only 3 people in the company can "supposedly" view.
I pointed out that I, and others, have super-user rights and we could
set ourselves up to view any table in the database.
I also pointed out that the network admins have rights to that H: drive
and could view that Excel file at any time. My recommendation is to
password protect the Excel file so that it is encrypted and people like
network admins won't be able to view it.
They have a form with several tabs on it; each tab containing a subform.
A new tab will be created to present the data contained on the H:
drive to the the user.
Q1) If I create an MDB on the H: drive and link it to my front end,
would it be best to check and when the form is initially opened see if
the H: drive is available to the user and if it is make the page tab
visible or hide it if the H: drive is unavailable? I was thinking about
this since if the user tabs to that page I don't want him/her to see a
bunch of #Names in that tab or get error messages. What would you do to
present data if they have or haven't rights to a folder?
Q2) A2007 permits one to encrypt data. The main back end is on the P:
drive and I would want to encrypt the mdb on the H: drive. If there are
some users that use A2007 and others with A2003, would this pose a conflict?
Q3) Does one have to enter a password to unencrypt the backend database?
If you have some insight with encrypted backend tables I'd appreciate
your POV.
Rich P
Excel File (or a Word file, Image file) with embeded objects (like
pictures, forms, ...) to binary format and store in a sql server 2005
table as a blob (binary large object). This is accomplished with an
ADO.Net filestream. I only mention this because this is about as secure
as you will get, and this is how I have been doing it. As long as I am
mentioning this - here is the code to perform this task (I did it in C#)
-- actually a word doc containing an embeded .jpg picture file
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms
private void Form2_Load(object sender, EventArgs e)
{
conn1 = new SqlConnection();
conn1.ConnectionString = "Data Source=COMP9\\SQLEXPRESS;Initial
Catalog=comp9;Integrated Security=True";
da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = conn1;
ds = new DataSet();
}
private void btnWriteDocToSqlSvr_Click(object sender, EventArgs e)
{
//--this code writes a doc file to sql server in binary format - kind of
like serializing the doc
string FileNameAndpath = Application.StartupPath + @"\doc1Test.doc";
Console.WriteLine(FileNameAndpath);
FileStream fs = new FileStream(FileNameAndpath, FileMode.Open,
FileAccess.Read);
byte[] b = new byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();
fs = null;
string strQuery;
strQuery = "insert into DocStore(FileName, binFile)
values('doc1Test.doc', @doc)";
da.SelectCommand.Parameters.Add("@doc", SqlDbType.VarBinary, b.Length,
"binFile");
da.SelectCommand.Parameters["@doc"].Value = b;
da.SelectCommand.CommandText = strQuery;
conn1.Open();
da.SelectCommand.ExecuteNonQuery();
conn1.Close();
}
private void btnRetrieveDocFromSqlSvr_Click(object sender, EventArgs e)
{
//--and this code retrieve the binary Doc file and writes it to the disk
in .doc format
da.SelectCommand.CommandText = "Select * from DocStore Where rowID = 2";
da.Fill(ds, "tbl1");
FileStream fs = new FileStream(@"C:\1C\myTestdoc.doc",
FileMode.CreateNew, FileAccess.Write);
byte[] blob = (byte[])ds.Tables["tbl1"].Rows[0]["binFile"];
fs.Write(blob, 0, blob.Length);
fs.Close();
fs = null;
}
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Salad
> This isn't exactly an Access solution but it is possible to serialize an
> Excel File (or a Word file, Image file) with embeded objects (like
> pictures, forms, ...) to binary format and store in a sql server 2005
> table as a blob (binary large object). This is accomplished with an
> ADO.Net filestream. I only mention this because this is about as secure
> as you will get, and this is how I have been doing it. As long as I am
> mentioning this - here is the code to perform this task (I did it in C#)
> -- actually a word doc containing an embeded .jpg picture file
Thanks Rich.
The way I see it is:
1) If one has ALL rights in an MDW file he has access to view a table
2) If one has access to a folder and read rights that person can view
files in that folder.
3) If one has all rights in an MDW file but does not have rights to a
folder that contains the table, that person can't view the file.
4) If one has all rights in an MDW file and has rights to a folder but
the file is encrypted, that person can't view the file if he/she can't
unencrypt it.
5) One can encrypt an MDB with A2007. Prior versions one can't encrypt
the file.
I am wondering how one handles presenting a form that is bound to a
backend MDB that he/she does not have network rights to. Ex:
FE: C: Has linked tables.
BE1: F: Has linked tables shared by all
BE2: G: Has linked tables shared by a couple of people.
When one opens the FE, there won't be a "valid" link to BE2 if the user
doesn't have network rights to that folder.
I was curious as to how one presents the form to all users and either
presents the data if rights exist or rights don't exist.
Rich P
versions of Access and has s few new features, but for the added
complexity - it really can't compete with an enterprise solution.
Acc2003 can do pretty much what Acc2007 can do as far as RDBMS stuff
goes and without the added complexities of Acc2007. If one wants to
venture into Enterprise country (Encryption is enterprise country) with
Acc2007 - good luck.
The sql server 2005 solution I posted here for your questions offers the
most security you are going to get and with way less complexity than the
mdw route. It just isn't an Access solution.
I try not to sound like a critic of Access because it is a formidably
powerful tool for non enterprise projects with way less overhead than an
enterprise solution for non enterprise projects. But since Access has a
little bit of overlap into Enterprise country - when people start
spinning their wheels trying to develop a solution for an enterprise
level problem with Access - it just isn't the best or most efficient use
of Access. You may come up with one solution that will work only under
the most ideal circumstances. But as you know - if that is the case -
it is usually the first user who will accidentally do something non
ideal right off the bat and the Access solution fails. This is why I
stepped up to enterprise land. But then, I wasn't one of the pioneers
of RDBMS development like a lot of the bigger posters here, so I didn't
have a lot of legacy issues to bring over to enterprise land and the
upgrade for me was fairly seemless. If you aren't one of the RDBMS
pioneers I would recommend stepping up to enterprise country.
Salad
Well Rich, our conversation has been picked up Access Monster. Of
course, nobody at that site has responded.
http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/43273/Encrypting-data-and-networking-question
What the hell is Access Monster but a leech?
James A. Fortune
>
> What the hell is Access Monster but a leech?
I just tried their search engine to bring up the following post of
mine from March 2004:
I couldn't find it using the current Google Search, so for now the
"leech" is providing a valuable service.
Rich (OT question):
Was there a particular reason you used a WinForm over WPF?
From:
http://msdn.microsoft.com/en-us/library/8fxztkff.aspx
"Windows Forms
In general, in a Windows Form, use a dataset. Windows Forms are
typically used on rich clients where the form is not created and
discarded (along with its data) with each user operation, as with Web
Forms. Windows Forms applications also traditionally offer data-access
scenarios that benefit from maintaining a cache of records, such as
displaying records one by one in the form. For more information see,
Creating Client Data Applications.
Specifically, use dataset under the following circumstances:
If you are working with the same records repeatedly, such as allowing
a user to navigate between records.
If you are using the Windows Forms data-binding architecture, which is
specifically designed to work with datasets.
For any of the other reasons listed under Web Forms above.
Use a TableAdapter query or data command under the following
circumstances:
If you are getting a scalar value from the database
If you are performing a non-query operation, such as a DDL command.
If you are getting read-only data to display in a form — for example,
creating a report. Stated differently, if there is no need to keep the
data available after accessing it, use a data command."
Another Microsoft page said:
"If you are not using a dataset, you can send update commands directly
to the database."
What I notice is that once the decision was made to use WinForms, that
seemed to imply using a dataset.
Thanks,
James A. Fortune
CDMAP...@FortuneJames.com
Salad
It's also at
http://www.rhinocerus.net/forum/databases-ms-access/588934-encrypting-data-networking-question.html
now.
I entered the wrong line at Monster.
http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/43273/Encrypting-data-and-networking-question
Rich P
motivation factor to pursue the cutting edge stuff is fairly limited
(almost to 0 = too lazy to do the thinking part) I will wait till there
are tons of examples from other people who have mastered the newer
stuff. Eventually, once I have a fairly solid handle on this newer
stuff I reinforce it by sharing my experience with individuals who are
dealing with overlapping technologies. To summarize -- Access is one of
these overlapping technologies which overlaps a little bit into
enterprise stuff (sql server / .net related related solutions). Since I
have a reasonable grasp of these 2 worlds - I share my experience with
the individuals who are encountering challenges and thus I reinforce my
own experience and simultaneously provide some assistance to the
individual.
But for WPF, I just haven't seen enough documentation on it to deal with
it yet.
James A. Fortune
Fair enough. It will be interesting to see what percentage of
programmers stick with WinForms over time. I plan on doing some
overlapping and sharing of my own.
James A. Fortune
CDMAP...@FortuneJames.com
Microsoft is itself providing several compilers that produce code
targeting the runtime: C++ with Managed Extensions, C#, Visual
Basic .NET (which now subsumes Visual Basic Scripting Edition, or
VBScript, and Visual Basic for Applications, or VBA), and JScript. --
Applied Microsoft .NET Framework Programming, Microsoft Press 2002,
Jeffery Richter
James A. Fortune
wrote:
> On Sep 13, 6:37 pm, Rich P <rpng...@aol.com> wrote:
>
>
>
> > There isn't that much documentation on WPF at this time. And since my
> > motivation factor to pursue the cutting edge stuff is fairly limited
> > (almost to 0 = too lazy to do the thinking part) I will wait till there
> > are tons of examples from other people who have mastered the newer
> > stuff. Eventually, once I have a fairly solid handle on this newer
> > stuff I reinforce it by sharing my experience with individuals who are
> > dealing with overlapping technologies. To summarize -- Access is one of
> > these overlapping technologies which overlaps a little bit into
> > enterprise stuff (sql server / .net related related solutions). Since I
> > have a reasonable grasp of these 2 worlds - I share my experience with
> > the individuals who are encountering challenges and thus I reinforce my
> > own experience and simultaneously provide some assistance to the
> > individual.
>
> > But for WPF, I just haven't seen enough documentation on it to deal with
> > it yet.
>
> > Rich
>
> > *** Sent via Developersdexhttp://www.developersdex.com***
>
> Fair enough. It will be interesting to see what percentage of
> programmers stick with WinForms over time. I plan on doing some
> overlapping and sharing of my own.
>
> James A. Fortune
>
> Microsoft is itself providing several compilers that produce code
> targeting the runtime: C++ with Managed Extensions, C#, Visual
> Basic .NET (which now subsumes Visual Basic Scripting Edition, or
> VBScript, and Visual Basic for Applications, or VBA), and JScript. --
> Applied Microsoft .NET Framework Programming, Microsoft Press 2002,
> Jeffery Richter
corrigendum:
Jeffery -> Jeffrey
James A. Fortune
CDMAP...@FortuneJames.com
Salad
Getting back to my original post...
Is there anyone that is using Access 2007 that can link to another
database that has been encrypted via VBA. If so, can you provide a
smaple command line?
Rich P
>Is there anyone that is using Access 2007 that can link >to another
database that has been encrypted via VBA. If >so, can you provide a
smaple command line?
Sorry, for digressing, but No. I have not seen any
discussion/documention/or related example using Acc2007. That's not to
say there isn't anything, or that no one will ever come up with
something. I suspect if someone does labor to develop such a solution
with Acc2007, it may be fairly complex. Note: there is a difference
between sophisticated and complex. Something becomes complex when the
resource you are using to build it is being extended beyond its original
design capabilities. Your project is doable fairly easily in the
enterprise environment, but may be challenging in Access as the source.
The only other thing I could think of would be a third party tool, which
most likely would have been developed on an enterprise level platform.
Salad
Thanks.
I would think one should be able to link to a database and it's tables
whether or not it has been encrypted.
James A. Fortune
> Sorry, for digressing, but No. I have not seen any
> discussion/documention/or related example using Acc2007. That's not to
> say there isn't anything, or that no one will ever come up with
> something. I suspect if someone does labor to develop such a solution
> with Acc2007, it may be fairly complex. Note: there is a difference
> between sophisticated and complex. Something becomes complex when the
> resource you are using to build it is being extended beyond its original
> design capabilities. Your project is doable fairly easily in the
> enterprise environment, but may be challenging in Access as the source.
> The only other thing I could think of would be a third party tool, which
> most likely would have been developed on an enterprise level platform.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***
You might ask, why would someone who doesn't like to take a paradigm
too far beyond its original concept want to enhance Access beyond it's
design capabilities instead of going directly to .NET? I'm glad you
asked :-).
First, I have been programming for such a long time in Access (billing
approximately 20,000 hours) that custom programming something in the
Access environment is nearly automatic for me. I know with a lot of
certainty where the trouble spots are going to be. Customers know
that they will be getting very good value for their money and that
even the "beta" versions of the custom code are likely to be fairly
robust and have few bugs, which are usually totally eradicated within
a few days of testing.
Second, I have already been enhancing Access through the use of API
functions for many years. Adding .NET managed "extras" seems to be a
fairly natural step for me, especially given the way Microsoft has
bent over backwards to make sure the managed code would not have to
rely on .ini files, registry settings or avoiding DLL version
mismatches.
Third, I might decide to go the .NET route in the future. Extending
Access' capabilities will allow me to keep from having to phase out
Access earlier than I wanted, perhaps by a few years. It will also
allow me to test the .NET waters and gain familiarity with it to see
if there is any chance of it even approaching Access' RAD capabilities
given adequate experience in .NET.
Fourth, .NET will be the focus for Microsoft programming for quite a
while. IMO, Access is not as central in Microsoft's thinking as it
once was. Consequently, some of Access' weaknesses were allowed to
remain in a way that was at first enigmatic to most Access
developers. I don't know whether Access was allowed to keep its
weaknesses due to a sharpened focus on .NET or if Microsoft expected
such weaknesses to be overcome through .NET. Either way, Access'
weaknesses are profound enough that Access in its present state will
not be adequate for my purposes in the reasonably near future. It
even appears that .NET can adequately deal with all those weaknesses.
Extending Access with .NET as an interim solution to Access'
weaknesses seems to me to be more natural and less complex than it
appears. As someone who likes to keep up with where Microsoft is
going as part of an eclectic programming philosophy, becoming familiar
with their .NET focus is a reasonable goal that can be achieved
without sacrificing the benefits Access brings.
As far as the choice of using C# for .NET, I believe that so much
Access programming has made me somewhat "VB heavy." I could have
transitioned to VB.NET, but I think that C# programming will help
bring my programming more into balance. Without discounting the
challenge of database programming, I notice that many C# programmers
seem to tackle more diverse and ambitious projects than Access
programmers do. Perhaps that perspective is from simple familiarity
with the tasks associated with Access programming, but it is
refreshing to attempt other kinds of programming tasks as part of the
learning process. I'm also curious about how what I learned
programming VBA will affect my programming style in C#. I noticed
that the Visual C++ programming I did many years ago affected the way
I approached programming in VBA. With effort, maybe just the good
habits will carry over to C# :-). Maybe I can also carry some good
habits from C# back.
James A. Fortune
CDMAP...@FortuneJames.com