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 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

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

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