Get Password of currentuser in code

[Glenn]

to reiterate an earlier question from Jeff in this group:

From code, is it possible to get the current users password?

My need/purpose:

from Access,
I open an Excel template with CreateObject("Excel.Application").
and fill that excel file with data from the access database by opening
an ODBC connection back to the database and use a query (querytables)

Until now the application ran without security. From Now on, the access
database is secured and so now, I need to add the user and his password
to the connection string.

'example from excel-help
Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
"ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("B1"), Sql:=sqlstring)
.Refresh
End With


best regards
glenn

 

[Rick Brandt]

to reiterate an earlier question from Jeff in this group:

From code, is it possible to get the current users password?

What purpose would a password serve if your program can determine what it
is?

If your code is running within the secured Access app can't you "push" the
data to the Excel file instead of "pulling" it from the Excel file? Then
security is a non-issue.

 

[Scott McDaniel]

No, you cannot retrieve the password via code.

The connection string you're using has a username/password in it already ...
do you need a different user/pass for this string? you can always require
the user to re-enter their password:

Dim strPass as String

strPass = InputBox("Enter your password: ")

connstring = "ODBC;DSN=96SalesData;UID=" & CurrentUser & ";PWD=" & strPass &
";Database=96Sales"

 

[Glenn]

What purpose would a password serve if your program can determine what it
is?

I expressed myself badly in the subject: "reuse" would have been be a
better choice of words.

You're correct that passwords are rather pointless if they can simply
retrieved by the program, but what value does the password have if it's
hardcoded ?
It is known all the same.

As the users can change their password at any given moment, this forces
me to create a "system" user for which I can impose the password (and
hardcode it).
This user must have at least the same level of permissions as the
current user to achieve the task at hand.
With several different user groups/permissions levels, I am, as a
consquence, forced to more or less duplicate the secured access
protection to mimic that of the current user.
Something I only see possible in the frontend, not in the backend. and
this, in turn, practically means this system user must be part of the
admin group. Which I don't really like.

If I could reuse the current user credentials, I would catching the
standard access security violation event.

Furthermore, I suppose, those currentuser credentials are present in the
access environment; they get used whenever a linked table from a backend
table is accessed for the first time.

I am surprised the reusability of the password is lacking in Access.
I feel the connections methods should have the user/password as optional
parameters. If they are not given, the current ones should be used. This
way, it's Access itself dealing (as it should) with the credentials and
not the code.

If your code is running within the secured Access app can't you "push" the
data to the Excel file instead of "pulling" it from the Excel file? Then
security is a non-issue.

The pull method is carried over from the non-secured version where it
worked perfectly. I had thought adding user & password to the connection
would be simple, just a matter of systemdb=users.mdw

I prefer the "pull" method to the "push" method because:
- simple to implement
- routines are developped(easy debugging) and used in their own
specific environment

This usually also means one can leave some of the data validation over
to the internals of that function, instead of having to rewrite them all
from another application.

IMO, it is best to let each application handle their own
specific functions, for which most of them are optimized, even for
applications as close as excel/access/word.

In this specific case, the excel code is basically one line:
querytables.
If the data had to be pushed to excel,meaning using Access functions,
I'd have to open a recordset, loop through all fields and record one by
one to enter individual cell values.
I'm pretty sure this will be slower that querytables.

 

[Glenn]

No, you cannot retrieve the password via code.

This, I don' want to hear

The connection string you're using has a username/password in it already ...
do you need a different user/pass for this string?

It was just an example to demonstrate the required syntax.
As I wrote in the reply to Rick Brandt, I have no need to access the
string itself. I simply like to reuse the current ones. But the syntax
being the syntax that is, the string is needed.
If I omit it, access ask for user & password to be entered.

the solution could be that the user/password are optional parameters in
that connection function. if ommited, the ones entered at startup should
be reused.
This way, the code does not need not to "know" the password.

you can always require the user to re-enter their password:

Dim strPass as String

strPass = InputBox("Enter your password: ")

connstring = "ODBC;DSN=96SalesData;UID=" & CurrentUser & ";PWD=" & strPass &
";Database=96Sales"

that is of course a solution. But I'd like to entertain an air of
professionalism by not asking my user twice for his passwordbut, given the choice of rewriting the transfer Access>Excel to "push"
the data (see reply to Rick Brandt again) and this, wellll....
I'm thinking about reverting to amateur status

thanks for your reply
glenn

 

[TC]

Glenn

The problem is this. Even though you are automating Excel from Access, Excel
really has no concept of "the current Access user". >You< have that concept,
Excel does not. Excel just sees that it is being automated from some
external source. That source might be Access, or Word, or any of a gazillion
other "automation capable" programs.

So it is not actually reasonable to expect Excel to say, "I'll get the
current Access user's password, so I can do things with it". If Excel did
that, then for consistency, it would surely have to do similar things with
those gazillion other programs! For example, for consistency, when automated
from Word, should it pick up the current document's security password? When
automated from Powerpoint, should it pick up the current presentation's
powerpoint password?

Nope. Ain't gonna happen! So although your expectation seems reasonable from
your perspective, it is actually not reasonable from Excel's perspective.

As to your problem, you can certianly get the current user's >encrypted<
password through code - but that is not the plaintext password that the
connection requires. And, you can certainly use the NewPassword method to
set a new plaintext password from code - then pass that known password - but
then, there is no way to return to the original password.

The only other way that I have thought of, is to have a >second< workgroup
file, with the same users, but where each user's password was set to their
user name (GAK). When a user logged-on to the database, you would ensure
this used the standard workgroup file - so they would have to enter the
proper passwords. But when Excel did the query, it would use the >other<
workgroup file, in which each user's password was set the same as their
name. Access would get the current user's name with the CurrentUser()
funcion, to pass to Excel. Excel would use that value as the username >and
password<.

This would work - for sure - but it would put you at the risk of someone
finding that other workgroup file, & using it to log-on to Access as someone
else.

But that's the only method that I can see, using normal Access security!

HTH,
TC

 

[TC]

(snip)

This would work - for sure - ...

Assuming that you can name the second workgroup file in the ODBC connection
string. And that I ain't sure of!

TC

 

[Rick Brandt]

[snip]

Furthermore, I suppose, those currentuser credentials are present in the
access environment; they get used whenever a linked table from a backend
table is accessed for the first time.

I am not intimately familiar with how security works internally, but I would
suspect that the password is only needed when "entering the system" to prove
that you are who you say you are. After that the system should only need to
refer to the UserName and not concern itself with the password at all. Keeping
the password in memory would be an inappropriate exposure.

 

[TC]

(snip)

Furthermore, I suppose, those currentuser credentials are present in the
access environment; they get used whenever a linked table from a backend
table is accessed for the first time.

Are you talking about the database password of the linked-to database? If
so, that password >does< exist (in plaintext form) in the linking-from
database file - you can easily find it using a text editor (it is preceded
by a certain, fixed string) - and it can easily be retrieved from code. It
is the "user level" passwords that can not be retrieved via code.

I am surprised the reusability of the password is lacking in Access.
I feel the connections methods should have the user/password as optional
parameters. If they are not given, the current ones should be used. This
way, it's Access itself dealing (as it should) with the credentials and
not the code.

See my other reply for why this will not work in your case.

HTH,
TC

 

[Glenn]

Sorry for my late reaction. Real world fatigue and returning Kings got
the better of me.

Glenn

The problem is this. Even though you are automating Excel from Access, Excel
really has no concept of "the current Access user". >You< have that concept,
Excel does not. Excel just sees that it is being automated from some
external source. That source might be Access, or Word, or any of a gazillion
other "automation capable" programs. 8<
Nope. Ain't gonna happen! So although your expectation seems reasonable from
your perspective, it is actually not reasonable from Excel's perspective.

this is, of course, true and something I quite agree to.

As to your problem, you can certianly get the current user's >encrypted<
password through code - but that is not the plaintext password that the
connection requires. And, you can certainly use the NewPassword method to
set a new plaintext password from code - then pass that known password - but
then, there is no way to return to the original password.

The only other way that I have thought of, is to have a >second< workgroup
file, with the same users, but where each user's password was set to their
user name (GAK). When a user logged-on to the database, you would ensure
this used the standard workgroup file - so they would have to enter the
proper passwords. But when Excel did the query, it would use the >other<
workgroup file, in which each user's password was set the same as their
name. Access would get the current user's name with the CurrentUser()
funcion, to pass to Excel. Excel would use that value as the username >and
password<.

That's a workable idea. I'll keep that in mind.

This would work - for sure - but it would put you at the risk of someone
finding that other workgroup file, & using it to log-on to Access as someone
else.

that is indeed the next problem. and because the user need read access
to it, they can use file monitors and such to find the file.
so once more, security is weak.

But that's the only method that I can see, using normal Access security!

But all this further proves the need for connection functions with
better password capabilities.
IMO Microsoft knows this is a problem, but glosses over it. It's nice to
show examples on security using blank or static passwords, while (in
other areas) urging users to change passwords frequently

But it isn't there for the moment, so I have to live with it.

as a immediate solution, I'll simply ask the users to reenter their
password as it is needed only in one particular, infrequently used,
function.
the "push" apprach suggested in the other messages, I'll use if more
frequently used functions need it.

HTH,
TC

More than H
thanks
glenn

 

[Glenn]

(snip)



Assuming that you can name the second workgroup file in the ODBC connection
string. And that I ain't sure of!

TC

The registerdatabase method lets you define the MDW file ( see included
code).

glenn


Private Function CreateConnectionODBC() As Boolean

Const C_DRIVER = "Microsoft Access Driver (*.mdb)"

Dim errLoop As Error

'Local error routine
On Error GoTo Err_Register

' Update Windows Registry.
DBEngine.RegisterDatabase "Users_DB", C_DRIVER, True, "SystemDB=C:
\DB\Users_SEC.mdw"

CreateConnectionODBC = True
Exit Function

Err_Register:

' Notify user of any errors that result from
' the invalid data.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr &
errLoop.Description
Next errLoop
End If
CreateConnectionODBC = False
End Function

 

[Glenn]

[snip]

Furthermore, I suppose, those currentuser credentials are present in the
access environment; they get used whenever a linked table from a backend
table is accessed for the first time.

I am not intimately familiar with how security works internally, but I would
suspect that the password is only needed when "entering the system" to prove
that you are who you say you are. After that the system should only need to
refer to the UserName and not concern itself with the password at all. Keeping
the password in memory would be an inappropriate exposure.

yes you're problably right, I forgot the linked databases are accessed
with the same security mdw file. So once the user is allowed in,
verifying user permissions with the linked database should be
sufficient.

glenn

 

[Glenn]

(snip)
Are you talking about the database password of the linked-to database? If
so, that password >does< exist (in plaintext form) in the linking-from
database file - you can easily find it using a text editor (it is preceded
by a certain, fixed string) - and it can easily be retrieved from code. It
is the "user level" passwords that can not be retrieved via code.

I don't use database passwords, only user level ones. that should be
sufficient.
8<
glenn