Sign In (Logon)

One of the most problem prone areas with creating and starting a new application is preparing the database and initially logging on to the database.

If you are using the Omnis data file, things are relatively simple because unlike read DB management systems, the Omnis data file doesn't support or enforce 'Users' and 'Permissions'.

Signing in (logging on) to a new database becomes a chicken-and-egg situation where we want StudioWorks to add a 'usr' table to the datatabase and insert a system administration user so that you can signin-in, but for StudioWorks to do this it needs to somehow logon to the database as the 'tables owner' database user.

Once you get the database setup with the tables owner user, the 'usr' table added, and a system administrator inserted into the 'usr' table you can easily 'Sign-In'... but getting there can be a headache.

Database Users

With SQL92 compliant databases:

• The database is owned by a superuser.
• Database tables are owned by a tables owner user.
• Database users must be granted select, insert, update, and delete privileges for each database table they wish to access.

The superuser, tablesowner, and database user could all be the same user, however, experienced SQL92 developers have suggested to me that this is not a good idea.

It is important that we understand and use consistent terms when discussing users.

1. Superuser - The root user who owns the database. The root user can add/delete/modify other users. The root user can change other users passwords.
2. Tables Owners User - The user who owns the tables in the database. Different tables in the database could be owned by different users but avoid doing so unless you have a logical reason for doing so.
3. Database Users - These are individual users who are permitted to logon to the database and are granted privileges to select, insert, update, and delete records in various tables.
4. Application Users - These are users which you insert into the 'usr' table in the database. The 'usr' table has additional fields of information needed by StudioWorks version 4 to enforce security access to menus, windows, and reports for users and groups.

To simplify starting a new database StudioWorks version 4 has a number of defaults which you should use when setting up a new database. You can change the default passwords for higher security prior to releasing your application to the customer.

Note: To find the StudioWorks version 4 default users and passwords search the StudioWorks Wiki for 'Default Database Users'

The StudioWorks version 4 framework is designed to support having a database user for every application user.

The advantages of having a database user which matches each of application user are:

1. Using the DB tool provided by the DBMS vendor the database adminstrator can tell each individual user who is logged onto the database. If one user was running a query that was tying up the database, the administrator could kill that user's session.
2. The database will enforce table security on a user by user basis. If users are accessing the database through various applications (e.g. web browsers, Crystal reports, etc.) the database will only allow them to access or update tables which they have been specifically been granted those privileges.

The disadvantages of having a database user which matches each of application user are:

1. Every time you add a table to the database you must grant privileges to your users to access the table or they will get a SQL error when they try to access the table.
2. Every time you add a user you must grant privileges to that user for the tables the user needs to access.
3. You must keep the database users and passwords in sync with the application users and passwords.

Tables Owner Logon

If you choose the StudioWorks version 4 default of not adding a database user for every application user, the Sign-In (Logon) window has to be able to open a session with the database logging on as the tables owner. The DBMS won't know about the application users so they can't directly logon to the database.

To accomplish this without giving the users the actual tables owner name and password, StudioWorks version 4 allows the database administrator to create an encrypted tablesownerlogon.txt file which is stored in the startupsettings folder. On sign-in StudioWorks version 4 decrypts the file and logs on to the database as the tables owner. StudioWorks version 4 then fetches the application users from the Usr table and checks the user and password entered by the user against the list of application users. If a matching application user is found and the password matches the sign in process is completed and the main menu is installed and the main window is opened.

The tablesowner.txt file that ships with StudioWorks version 4 uses the default tables owner name and password. (To find the defaults ask the StudioWorks members list). If you use the defaults, you won't need to create a new encrypted file.

To create a new encrypted file:

1. Main menu > Programmer menu > Database Administration
2. Make sure the correct session is selected in the sessions picker droplist.
3. Select the Users tab.
4. Click the Create Table Owner Logon File button at the bottom of the window.

This creates the encrypted file with the file path APP/startupsettings.tablesownerlogon.txt. The encrypted file contains the tables owner name and password. Encrption is done using the blowfish object.

Alternately you can store the tables owner logon information in a saved session as follows:

1. Open the Sessions Manager window.
2. Create a new session and enter all of the session information including the user name and password for the superuser and the tables owner. Be sure to use a unique name for the session name.
3. Save the session and then click the Test button in the toolbar to make sure it works.
4. In the startupsettings.txt file, in the logon group, include the following:
   
   

   sessionname = TheUniqueSavedSessionName ;

Prepare New Database

StudioWorks version 4 tries to make preparing a new database a simple task... but be prepared for a few speed bumps to get in the way.

To prepare a new database.

Using the database vendor's tool:

1. Create a new database.
2. Set the database superuser name and password to the StudioWorks version 4 defaults.
   
   Note: The defaults names and passwords can be found by searching the StudioWorks Wiki.
3. Add a tables owner user and password matching the StudioWorks version 4 defaults.
   
   Make sure the tables owner default schema is set to the tables owner name.

Using your StudioWorks application:

1. Open (or reopen) your StudioWorks application to get to the Sign In (Logon) window.
2. Enter the tables owner name and password.
3. StudioWorks will logon as the tables owner, check to see if any tables exists, and if none, will prompt you with a message asking if this is a new database and if you want StudioWorks to prepare the database. Click Okay.
4. StudioWorks will create tables and views in the database for all the schemas and views included in your application's modules.
5. StudioWorks will copy records from the APP/startupsettings/NewData.df1 file into the matching tables in your new database.
6. All going well StudioWorks will prompt you with a message to logon as the system administrator.
7. Enter the system administrator name and password and click the Sign In button.
8. Test adding/editing/deleting records in the new database.
9. Add application users, groups, security privileges and test signing in as different application users.

Logon Settings

You can preset any of the session properties for the default session which StudioWorks loads when your application is opened.

The startupsettings.txt file located in the APP/startupitems/ folder is where you can control the startup session properties.

StudioWorks checks for a startupsettings.txt file, and if found prepares a session row variable using the properties which if finds in the logonsettings{} properties group in the file.

The actual opening of a session with the database does not occur until after the user clicks the Sign-In button on the Sign-In window. This gives the user (or developer) an opportunity to change the session settings which are preset by the startupsettings.cfg file. The user can change the session or session settings by clicking the Change Session button on the Sign-In window.

If the startupsettings file is not found or doesn't contain a logonsettings properties group StudioWorks simply uses the last selected session.

If there are no saved sessions the user will need to click the Change Session button to open the Sessions Manager window where they can create a session. See Sessions Manager for details on creating and modifying sessions.

The following is sample logon settings text from a startupsettings.txt file

/*
----- Logon Settings -----

On starting up the application will check this file for any logon settings.
Any session properties specified in this configuration file will be used
for the initial logon session settings. If no settings are specified in this
file, the last session will be used.
-----
*/

logonsettings {

 damname = FRONTBASEDAM ;
 hostname = localhost ;
tablesownerlogon = TRUE ;

}

StudioWorks Documentation - Copyright 2005 Vencor Software