When someone opens a workbook, I will process the
Workbook_Open() function. If the user does not
match Environ("Username"), I plan to immediately close
the workbook with ThisWorkbook.Close().

This will effectively restrict users to only have access
to their own workbooks.

Does anyone see any problems with this approach?
Are there better solutions to restricting access to
workbooks? I'm curious what you think.

Rob

Hi Robert,

Am Thu, 20 Nov 2014 01:14:01 -0700 schrieb Robert Crandal:

Does anyone see any problems with this approach?
Are there better solutions to restricting access to
workbooks? I'm curious what you think.

if a PC is started and the user is logged in the Environ("UserName") is
always correct for everyone you works with this PC.
If the users have also to log in for Excel you can match
Environ("UserName") with Application.Username.
Or you match the workbook author with the environ("UserName"):
If Thisworkbook.BuiltInDocumentProperties("Author") <
Environ("UserName")
Or:
If Thisworkbook.BuiltInDocumentProperties(3) < Environ("UserName")



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

if a PC is started and the user is logged in the Environ("UserName") is
always correct for everyone you works with this PC.


Our computers at work are on a network, and everyone logs
into their own machine. This means that everyone will have
their own Environ("UserName") identifier. That means
my idea will work, right?

If the users have also to log in for Excel you can match
Environ("UserName") with Application.Username.
Or you match the workbook author with the environ("UserName"):
If Thisworkbook.BuiltInDocumentProperties("Author") <
Environ("UserName")
Or:
If Thisworkbook.BuiltInDocumentProperties(3) < Environ("UserName")


I'm actually the author of all the workbooks that everyone uses.
Does that mean the above code will not work?

Hi Robert,

Am Thu, 20 Nov 2014 02:07:58 -0700 schrieb Robert Crandal:

Our computers at work are on a network, and everyone logs
into their own machine. This means that everyone will have
their own Environ("UserName") identifier. That means
my idea will work, right?

that is clear. But with what will you match the identifier?
What is the criteria for the logged in persons workbooks?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Thu, 20 Nov 2014 02:07:58 -0700 schrieb Robert Crandal:

Our computers at work are on a network, and everyone logs
into their own machine. This means that everyone will have
their own Environ("UserName") identifier. That means
my idea will work, right?

create a folder for each user and give the users only the rights to open
this folder and the subfolders


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Thu, 20 Nov 2014 10:13:30 +0100 schrieb Claus Busch:

that is clear. But with what will you match the identifier?
What is the criteria for the logged in persons workbooks?

the users that work with the workbook and save changes are the last
author of the workbook
So you could use
If Environ("UserName") < Thisworkbook.BuiltinDocumentProperties(7) then
7 denotes the Last author

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" schrieb:

that is clear. But with what will you match the identifier?
What is the criteria for the logged in persons workbooks?


Each user has their own assigned workbook in their own
folder. So, the code in each workbook will be unique for
each user. For example, in Bob's workbook, the code will
look like:

if (Environ("UserName") < "Bob") then
ThisWorkbook.Close()
end if

Do you have any comments about this approach?

BTW, changing the folder permissions is not an option.

Hi Robert,

Am Thu, 20 Nov 2014 03:07:59 -0700 schrieb Robert Crandal:

Each user has their own assigned workbook in their own
folder. So, the code in each workbook will be unique for
each user. For example, in Bob's workbook, the code will
look like:

in this case it is ok.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Not to discount Claus' very good suggestions.., I handle this scenario
as follows...

No password/login.
Commandbar menus only, 1 for all users.
Menus/procedures enabled/disabled 'in-context' to "Username".
Menu state handled by code AFTER they're created.

This follows same logic as 'context-sensitive' menus in regular apps,
where the context condition is Environ("username"). You can even use
Select Case constructs in 'common' procedures so they too are
context-sensitive.

I put the username in a global string var so I only have to type
'sUser' anywhere I need to ref that user's name. I use a sub named
"InitGlobals" at startup which sets all runtime values for the project.

End result is I can maintain/distribute a single project but control
who has access to what 'features'. In your scenario you state that as
the file has user-specific code. Well, unless the entire rest of the
project is also suser-specific, I'm all for 'most simples' with minimal
maintenance!

I get the impression (from your posts over the years) that you work
in/for a corporate employer/client[s]. I'm self-employed but I do work
lots of work for corporate clients with tough 'security' rules for who
has access to what. I store the 'authorized users' list on the server
so all users' projects ref the same validation file. I load that file
into a global string var and Split() that into an array if it contains
multiple lists. In the latter case the using project has its own
'index' held in a global constant so it can ref that to verified user
credentials. May seem complex but it's really quite simple to work with
once you have the 'busines logic' part of it structured. Maintenance is
a breeze because you just distribute 1 project file and update the
'access permissions' file on the server when required. Next time the
project gets opened by any user your project can simply give them "You
do not have permission..." notification!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:
I store the 'authorized users' list on the server so all users' projects
ref the same validation file. I load that file into a global string var
and Split() that into an array if it contains multiple lists. In the
latter case the using project has its own 'index' held in a global
constant so it can ref that to verified user credentials. May seem complex
but it's really quite simple to work with once you have the 'busines
logic' part of it structured. Maintenance is a breeze because you just
distribute 1 project file and update the 'access permissions' file on the
server when required. Next time the project gets opened by any user your
project can simply give them "You do not have permission..." notification!


Hey, I was actually thinking about implementing an "authorized users" list
in a separate workbook. The list will probably be stored in a very hidden
sheet. There will be one row for each Environ("Username"). Each row will
will also include a list of authorized users to that particular workbook.

This is almost similar to what you mention above, right?

"GS" wrote:
I store the 'authorized users' list on the server so all users'
projects ref the same validation file. I load that file into a
global string var and Split() that into an array if it contains
multiple lists. In the latter case the using project has its own
'index' held in a global constant so it can ref that to verified
user credentials. May seem complex but it's really quite simple to
work with once you have the 'busines logic' part of it structured.
Maintenance is a breeze because you just distribute 1 project file
and update the 'access permissions' file on the server when
required. Next time the project gets opened by any user your
project can simply give them "You do not have permission..."
notification!


Hey, I was actually thinking about implementing an "authorized users"
list
in a separate workbook. The list will probably be stored in a very
hidden
sheet. There will be one row for each Environ("Username"). Each row
will
will also include a list of authorized users to that particular
workbook.

This is almost similar to what you mention above, right?

Not exactly! I'm sure that idea will work just fine but the list would
be a problem to maintain if the number of files using the methodology
is more than a few. I only have to maintain a single file that every
'restricted access' workbook reads at startup. This is just a plain
text file (uap.dat) which is encrypted so it can't be edited without
being decrypted.

Another approach I've used is to have certain Excel files require a
password to open so only authorized users can open them with a utility
addin that already knows the password AND who has access permission to
even open the file.

In fact, my apps implement the latter methodology where only that app
can open its files because that's all the file dialog displays in the
filetype dropdown. (My apps use their own file extension[s] and so
won't even be recognized as Excel files in a regular file browser.)

For some clients I've implemented a 'GroupPolicy' type of approach
where every workstation has an M$ Office COMAddin installed which
'hooks' the FileOpen dialog and adds the 'proprietary' file extension
to the filetypes dropdown so only authorized users can even see those
restricted files in the browser. The COMAddin knows who's authorized
and so only provides the extra filetype[s] in the dropdown on that
basis. In this scenario the COMAddin can be programmed quietly unload
itself if the logged in user isn't authorized.

There's undoubtedly numerous other ways to handle 'user access
permissions' in a corporate environment. If I had a better overview of
what you want to accomplish I could better advise/suggest as I find
most 'security minded' clients prefer a standard 'GroupPolicy'
approach.

Finally, for another example, to restrict the number of simultaneous
users of my apps under a 'site license' I track how many running
instances against the site license 'MaxUsers' property. If the network
has 15 installs and a 10 seat 'site' license then the 11th user needs
to wait until an available seat becomes vacant before the app will run.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

Not exactly! I'm sure that idea will work just fine but the list would be
a problem to maintain if the number of files using the methodology is more
than a few. I only have to maintain a single file that every 'restricted
access' workbook reads at startup. This is just a plain text file
(uap.dat) which is encrypted so it can't be edited without being
decrypted.


I would just use ONE file to store a list of authorized users. My initial
idea was to store the list in an Excel workbook in a very hidden sheet,
but using a text file might be a better idea. I would want the user
validation
process to be very quick, so I'm thinking that scanning a user list in a
text file will be faster than opening a separate Excel workbook with
a hidden sheet.


For some clients I've implemented a 'GroupPolicy' type of approach where
every workstation has an M$ Office COMAddin installed which 'hooks' the
FileOpen dialog and adds the 'proprietary' file extension to the
filetypes dropdown so only authorized users can even see those restricted
files in the browser. The COMAddin knows who's authorized and so only
provides the extra filetype[s] in the dropdown on that basis. In this
scenario the COMAddin can be programmed quietly unload itself if the
logged in user isn't authorized.


That would be great, but I am not allowed to install any components
or software on our network. We cant even use Internet on our
computers either. I am kind of stuck using Excel as is.


There's undoubtedly numerous other ways to handle 'user access
permissions' in a corporate environment. If I had a better overview of
what you want to accomplish I could better advise/suggest as I find most
'security minded' clients prefer a standard 'GroupPolicy' approach.


Group Policy on the file level was the first thing that came to my mind,
but we will not be allowed to alter our current group policies. I only
need to give users access to their own Excel workbooks.

Loacking all the past workbooks will be a challenge - that means I probably
need to insert code into the Workbook_Open() function of each workbook,
but it will still work. I might have to install a "disable" flag in my
central
authorized user list, in the event that we need to turn off the authorized
user feature, which I don't think will happen, but I like to plan ahead
anyways.

"Robert Crandal" wrote:

Loacking all the past workbooks will be a challenge - that means I
probably
^^^^^^^^

Sorry about the typo. I meant "locking" here.

Sounds like your hands are tightly tied! I think that if you can store
your project files on the server then why not an addin file (.XLA)?
They can use standard Excel option 'Always open files in...' and
specify that server\share as a 'Trusted' location. Otherwise...

Reading a text file via standard VB file I/O features would definitely
be faster than using a workbook! What's going to be important is how
the file is constructed. In your scenario, there may be differing user
lists for differing files using the '1 file for all' approach. Not a
problem if you construct the file 'like' a database table so it parses
quickly for each project file user group. For example...

1st row should always contain 'headers' followed by 'records':
Filename|Usernames
File1|User1,User2
File2|User1,User2

...where each 'record' pertains to a particular restricted file and its
authorized users. Note that each line in the file contains a pipe
character separating the filename from a comma-delimited user list, and
ends with a newline (vbCrLf). You can use whatever record delimiters
you like, but IMO you'll be happy with these 'as is'!

The 1st thing your projects need to do at startup is read the file into
an array and access their 'specified' record index so they can validate
their respective authorized users. Note that the 1st line in the file
is array(0) (the headers) and so the 1st record starts at array(1), and
UBound(array) is the record count. (The last line in a database table
should never be blank!)

I'm not a fan of using a workbook's 'Microsoft Excel Objects' for
anything due to their vulnerability to corruption, and so here's how I
handle similar projects for simplest implementation/maintenance:

In a standard module named "mOpenClose":

Option Explicit
Const msModule$ = "mOpenClose"

'Define all this project's global variables here
'so they auto-instantiate when the file opens.
Public Const glUser_List_Ndx& = 1 '//edit to suit
Public Const gsValid_Users_File$ = "uap.dat"
Public Const gsUser_List_FilePath$ = "\\Server\Share\" '//edit to suit


Sub Auto_Open()
' This *replaces* the Workbook_Open event
If Not bValidUser Then ThisWorkbook.Close False
End Sub

Sub Auto_Close()
' This *replaces* the Workbook_BeforeClose event
ThisWorkbook.Save
End Sub

Function bValidUser() As Boolean
Dim vUserData, vTmp
Const sFile$ = gsUser_List_FilePath & gsValid_Users_File

vUserData = Split(ReadTextFile(sFile), vbCrLf)
vTmp = Split(vUserData(glUser_List_Ndx), "|")
bValidUser = (InStr(vTmp(1), Environ("username")) 0)
End Function

Function ReadTextFile$(Filename$)
' Reads large amounts of data from a text file in one single step.
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFile = Space$(LOF(iNum))
ReadTextFile = Input(LOF(iNum), iNum)

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFile()

Sub WriteTextFile(TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFile()

Use WriteTextFile to append new lists or update your file as needed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

The 1st thing your projects need to do at startup is read the file into an
array and access their 'specified' record index so they can validate their
respective authorized users. Note that the 1st line in the file is
array(0) (the headers) and so the 1st record starts at array(1), and
UBound(array) is the record count. (The last line in a database table
should never be blank!)


So, do you mean the last line of the authorized user list file should
never be blank? I don't like blank lines near the EOF, but my code
might not ever need to read as far as the last line, as long as each
username exists within the file.


Use WriteTextFile to append new lists or update your file as needed.


I will probably edit the master user text file with an editor like
Notepad, so I wouldn't need to use the WriteTextFile function
for any reason, right??

"GS" wrote:

The 1st thing your projects need to do at startup is read the file
into an array and access their 'specified' record index so they can
validate their respective authorized users. Note that the 1st line
in the file is array(0) (the headers) and so the 1st record starts
at array(1), and UBound(array) is the record count. (The last line
in a database table should never be blank!)


So, do you mean the last line of the authorized user list file should
never be blank?

Yes!

I don't like blank lines near the EOF, but my code
might not ever need to read as far as the last line, as long as each
username exists within the file.

You'll need to read the last line because it will contain the list for
whatever file it validates users for.


Use WriteTextFile to append new lists or update your file as
needed.


I will probably edit the master user text file with an editor like
Notepad, so I wouldn't need to use the WriteTextFile function
for any reason, right??

Since you work 'onsite' that's probably the way to manage it,
initially.

I use the read/write subs for updating the lists via code. This allows
me to add/remove/replace/edit programatically via a VB6.exe utility I
provide so the admins don't screw anything up in the file.

This uses the a logic whereby admins can view a list via clicking its
file in a combobox, then select an action from a menu strip. This
allows admins to add/remove/replace a new username (ie: if replacing
when an employee leaves, and gives them total control over the file's
contents. Putting the updated data back into the file is as simple
as...

WriteTextFile Join(vUserData, vbCrLf), sFile)

...after loading the file same as exampled using ReadTextFile. If
they're adding a new list to the file, they enter it in the textbox
that appears when they click the 'New List' button. It's been awhile
since I've worked with any user utilities for doing this but if I
recall correctly, the utility has the following features:

A VB6.exe that uses file encryption as follows...
DecryptFileToArray: loads uap.dat into vUserData()
EncryptArrayToFile: puts vUserData() into uap.dat
..instead of the read/write procs.

(Sub Main() checks to see that the user is authorized to run the
app. In this scenario, the 1st line in the uap.dat file contains
Admin:members in place of the normal datatable headers so
vUserData(0) is this app's list. This preserves that line2 is the
1st record!)

The Menubar buttons display the 'page' for their respective tasks.
'Pages' are an index in a Frame array which toggles visibility
Each page contains all controls required for its intended task.

Gives Admin users full control of uap.dat while protecting its
contents from other users.

Note: If file encryption isn't doable for you then at very least set
the file's 'Hidden' attribute so it doesn't appear in file browsers.

The utility is proprietary to my user and so if you're interested in
getting more into this I'm willing to do so offline via email if you
provide yours in a followup reply. Proprietary means each release of
the utility uses its own (unique) 256 bit encryption key to ensure
other users can't work with the file's contents, and the location of
the uap.dat file is hard-coded to a user-specified path.

I recall, though, having to rebuild once for a user who changed
location to another server. It occured to me after doing the revision
that I should convert this to a generic app caple of managing multiple
locations as well as generic its own encryption key at first startup.
That would involve adding features to select file paths, as well as
store path/key info with the app. Not difficult at all to do! This
would satisfy the security nuts where folder access is restricted to
corporate departments or the like. That way, department admins can only
access the file in their 'authorized' location.

Geez! See how easily I can 'make work' for myself. Gives me stuff to do
being house-bound during the winter months!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

Note: If file encryption isn't doable for you then at very least set the
file's 'Hidden' attribute so it doesn't appear in file browsers.

The utility is proprietary to my user and so if you're interested in
getting more into this I'm willing to do so offline via email if you
provide yours in a followup reply. Proprietary means each release of the
utility uses its own (unique) 256 bit encryption key to ensure other users
can't work with the file's contents, and the location of the uap.dat file
is hard-coded to a user-specified path.


Hey Gary, thanks for offering the details of your proprietary utility,
but I don't think it will be necessary.

I will be the only user who has control of our authorized user file.
Nobody else will know of its existence or location. It is just a
simple text file that I will edit with Notepad, and it will be stored
down a long path of sub-folders, possibly with a hidden file
attribute as well. Now I just need to work on the text file reader,
and I think you already put me in the right direction. Thank you!

Robert

"GS" wrote:

Note: If file encryption isn't doable for you then at very least
set the file's 'Hidden' attribute so it doesn't appear in file
browsers.

The utility is proprietary to my user and so if you're interested
in getting more into this I'm willing to do so offline via email if
you provide yours in a followup reply. Proprietary means each
release of the utility uses its own (unique) 256 bit encryption key
to ensure other users can't work with the file's contents, and the
location of the uap.dat file is hard-coded to a user-specified
path.


Hey Gary, thanks for offering the details of your proprietary
utility,
but I don't think it will be necessary.

You're welcome! Turns out, though, that that utility as a VB6exe is
under construction anyway due to changing the UI format of my Excel
version. I plan to finish it up so it's a 'done' item on my todo list.

I will be the only user who has control of our authorized user file.
Nobody else will know of its existence or location. It is just a
simple text file that I will edit with Notepad, and it will be stored
down a long path of sub-folders, possibly with a hidden file
attribute as well. Now I just need to work on the text file reader,
and I think you already put me in the right direction. Thank you!

Robert

Sounds good to me, Robert! Let me know if you want/need help with
coding for file encryption...

EncryptStringToFile
DecrpytFileToString

...are as simple to use as the read/write procedures, but they require
their Class wrapper be added to your project!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion