Wiki Home

Upsizing VFE Security Tables


Namespace: WIN_COM_API
Just a couple of extra things you need to do after following Bob's instructions:
1. Modify cuserscursor and set the lWritePrimaryKeyOnNew to .f.
2. In cGroupsRightCursor set NoDataOnLoad to .t.
3. cGroupRightsCursor has an alias set which seems to cause it to not be able to get the DBCX. Re-set alias property to default

There are very few occasions where I modify the "c" layer classes. Hopefully F1 will take care of some of these issues in the next release. -- Randy Jean

Randy - What I ended up doing is copying cSecure.vcx into my applications lib directory and making the mods to it. You only have to watch out for doing a refresh with VFE which will add the \vfeframe\csecure back to your project. -- Bob Archer

Looks like 2 & 3 where changed in VFE7. 1 still needs to be handled. -- Randy Jean

Bonus Chapter - Upsizing VFE Security Tables


In Chapter 19 we recommended that you not upsize the VFE security. However, it is hard to convince anyone that a client/server application is secure if the security information is readily accessible in DBF files on the file server which the users must be given full rights to.

The Visual Fox Express security system is very robust and configurable. As a matter of fact, we generally do not define any security beyond setting up the security names and giving our users access to the security screen. In this chapter I will give you the tools and information you need to move the tables to the server. The main caveat here is that the following has been done on SQL Server 7.0. If you are using another version or manufacturer you’re on your own, however, we suspect the process will be very similar.

Tables

The first step is to move the security tables to your SQL database. There are several ways that you can do this. The first would be to use the upsizing wizard. This will get you started but not 100% of the way there. The VFE tables have several rules on them which the wizard doesn’t handle very well. The second method you could use would be to script them by hand. This method will work well, if you like writing scripts. You could also use enterprise manager to create the tables.

However, you are in luck, since I already created these tables for you. I generated a SQL script for you to create them. To run this script, copy it to the SQL Query Analyzer and run it. Be sure you have selected your application’s database prior to running the script. The tables will be created with duplicates of all the DBC rules that VFE imposes on these tables.

_ Upsizing VFE Security Tables _ mk Tables

When you run the above script you should get the message ‘Execution Completed without error’ or similar in your results pane. If you browse your database in enterprise manager you will be able to verify that the tables have been created.

What I get:
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table 'dbo.apprites' because this table does not exist in database 'op7'.
Server: Msg 4902, Level 16, State 1, Line 2
Cannot alter table 'dbo.appusers' because this table does not exist in database 'op7'.

Why is it doing that when a few lines later:
drop table [dbo].[apprites]

I am guessing the ALTERs need to be moved under some if exists

Remote Views

The second step is to create remote views to these tables in your FESYS database. The FESYS database is the database container file that VFE creates to stored the security tables and views. They are created in a separate database so that you can share your security tables with several applications if you wish, or also several data sets for a specific application.

The first step to doing this is to create a connection for the remote views. As you know, a connection is the information VFP will use to connect to your SQL Server database. Once this connection is established it is utilized to send the remote view queries to the server.

As we showed in the ‘tips’ chapter you want to share the connection. However, your current connection was created in your application’s .DBC file, and not in the FESYS file. We have verified that if you create a connection with the same name that connection will be shared between views in the separate databases.

Once you have your connection created you will need to create the remote views which will be used by VFE to retrieve and update data. The views in FESYS are designed to use the LV_/RV_ prefix to views. If you are not familiar with this, what happens is that, based on a property in the application object, ‘lUseLocalData’ the cursor class knows whether to open the LV_xxx view or the RV_xxx view. In both cases the view will be opened with an alias of V_xxx. What this means is that the first step creating the remote views is to set ‘lUseLocalData’ to true in your application object.

The line above says "set ‘lUseLocalData’ to true" and the next line says "set you ‘lUseLocalData’ propert to false" - so which is it? falses seems to make sense, so that is what I am going with.

OK, at this point you have created your connection and set you ‘lUseLocalData’ propert to false. Once again, I will make this easy for you. The following code will create the remote views for you. There are three things you need to do to run this:

Change the #DEFINE of the first line of code to the name of the connection that you have created in place of "NameOfYourConnection".

At the command window open the FESYS database using the command ‘OPEN DATABASE FESYS’.

Run the code from the command window. You can either paste this code to a .PRG file or paste it to the command window to run it.

_ Upsizing VFE Security Tables _ VFESYSprg

After you run the above code and have also created the tables on the database, you should be able to ‘USE’ these views. This is also how you can move the data from the local tables to the remote tables. If you simply use the views then you can append from the tables to the views, and issue a tableupdate() command.

Keep in mind that foreign key constraints are set up, so you will need to put the parent data before you put the child data. This means you must put groups on the server before you put users. You must also put appnames on the server before you put the apprights.

Gotchas

While this process worked a lot better than I expected there are a few issues that you should be aware of, but none of them are show-stoppers in my opinion.

Login Journal

Since SQL Server doesn’t allow for empty dates, I set the logout and cleared dates to allow nulls. However, when you use the ‘Purge’ button on the login journal of the login screen ALL records are erased. This is happening because the code basically says, delete all records where cleared or logout is not empty. Well, as we all know, NULL is not equal to empty, so all records are matched. I have reported this as a bug, but it is easy enough to fix yourself.

Do what I say, not what I do...

Well, with all the awesome power of VFE, and all the time it saves me, I guess I can forgive this one. But, basically, the IDE is NOT n-tier. As a matter of fact, when you add a security name in the IDE, it doesn’t even open the VIEW to add the name, it opens the table directly.

What this means is that you have to leave the security tables around on your development maching. As you add security names in DBCX Explorer and the wizards VFE will add those names to the appname.dbf file. It’s a small problem, once you are done developing just move the data to the remote table of the same name. As a matter of fact, you can write a quick PRG to do this for you. I won’t insult your iteligence by providing it here. (Actually I would if I had written it yet.)

You actually don't need ALL the security tables kept local - just keep appnames.dbf in the fesys.dbc but blow away all the other dbfs. -- Randy Jean

As of VFE2005 you do not need to keep a local appnames.dbf if you've upsized your security tables. However, you will need to keep lv_Securitynames but make it a remote view instead of local for the security name dropdowns to work in DBCX and the Security Setup dialog to work. You still need to keep RV_SECURITYNAMES around for running the app, however. -- Randy Jean

i-Layer Security Classes

Well, the truth is, very few of the security classes are in the iLibs. So, if you want to extend the use tables, add your own attributes to users, etc, you will have to modify some of the c-Layer classes.

Proposed new text

Login form opens the user table?

One issue with the way the VFE login works causes problems with making a smooth remote login. The application object creates the security object, then calls the login method of the security object. The login method of the security object asks the factory for the login object, which by default is the login form.

The login form actually opens the user table and verifies the login rather than just being a UI to capture the login name and password. While you can change the factory to use something completely different as a login object and some changes in 6.3 allow the login to take place without any UI the system still expects the login object to verify the users.

The main problem here is that we wanted a single login form that would support the SQL login (standard or trusted) and the application login. The login form would prompt for User Name and Password with a checkbox for trusted connection. The system would then log into SQL Server with the entered username/password (or make a trusted connection). When that was successfull the same username/password would be use for the application login by passing it to the security object.

Hoever, with the current implementation it is necessary for the SQL connection to already have been made so the login object can validate the user, or VFP will put up a SQL login screen before the VFE login screen when the user table is opened. If you are using trusted connections here this will work fine and you won't see the SQL login form appear. Otherwise you will get an error message and then the login screen.

Old text replaced above
Login form creates the security object? What? Yes, I don’t know why, but this is how VFE works. Instead of the application object instantiating the security object, and having the security object run the login form if no user information was provided the login form is called, which creates the security object and attaches it to the application object.

The main problem here is that we wanted to be able to put up an SQL like login form, and once the connection was made pass the user/password info to the security object. Then we could tell our users to set up the same username/password for the application that the users have on the SQL server. This would work sort of the way windows does, if you use the same Windows username and password as your network, the login info is passed, and if it is valid you never see the second login form.

Not! (Sorry, I know you sent me this to proof, but I just caught this.)
The application object creates the security object, then calls the login method of the security object. The login method of the security object asks the factory for the login object, which by default is the login form. You can change the factory to use something completely different. FWIW, I've made some changes that'll be in SP3 that allow the login to take place without any UI. I'm using this in a web app. -- Mike Feltman

Ok guys - will one of you refactor this into just the facts!!! otherwise I will take a crack at it, and that seams kind retarded. - ?CFK

The second problem is that you have an ‘Unknown’ data session hanging around. Because the security tables are opened up under the login form’s private data session, but the security object is created in the default data session. So the security object has a reference to the cursor class which was created in the login form’s session. Once the form goes away, the session changes to ‘Unknown’. There isn’t really a problem with this, it is just a bit of a kludge. This is due to a known VFP bug. It's been confirmed by MS. -- Mike Feltman

We’re on it

The folks at F1 have promised that they know there are issues with the secuirty system and will address it ASAP. We hope to se some security enhancements in service pack 3. At least creating I-layer classes and using those and the factory object so you wont have to modify c-layer classes to customize the security.

Summary

If you are going to use a SQL Server database and one of its benefits is security, don’t lose that benefit. I have done most of the leg work for you here. All you need to do is make some backups, run a few scripts, and give it a go.
Category Visual FoxExpress
( Topic last updated: 2007.01.31 12:19:58 AM )