October 1995 Newsletter

Volume 5, No. 4

Highlights of This Issue

The Informix Worldwide User Conference & Exhibition, by Malcolm Weallans

Roles - A New Security Feature in 7.10.UD1, by Lester Knutsen

Using the Internet to Access Informix-Based Resources, by Carlton Doe

New WAIUG Home Page on the World Wide Web

WAIUG Forum 96

An all day conference for Informix Users

Exhibitors One room will be set-up as an exhibit hall with places for 12 exhibitors. Four exhibitors have already signed up. If you would be interested in exhibiting your products please, contact Lester Knutsen at 703-256-0267.

Speakers We are looking for a few very good technical sessions on developing, administering, and using Informix databases and client-server tools. If you are interested, please contact Lester Knutsen at 703-256-0267 with a proposal.

Participants: John Petruzzi, our Membership Director, will be handling registration. If you would like to attend or need more information on membership, please contact John Petruzzi at 703-490-4598.

Last Minute Update - Jonathan Leffler, author of "Using Informix-SQL", and one of the most helpful Informix developers at responding to users questions on comp.database.informix, will be speaking and participating in the Q&A session of the forum. Watch our Web page at http://access.digex.net/~waiug for updated information on this event.

Next Meeting - December

The WAIUG Home Page and other Informix Resources on the Internet

Presentation on Imaging Systems using Informix

Date and Time: December 12, 1995 9:00 a.m. - 12:00 noon

Location: Informix Software Corporation

8065 Leesburg Pike, Suite 600, Vienna, VA 22182

Phone: 703-847-2900

Newsletter Sponsorship

Corporate Membership

Marriott Corporation, Defense Courier Services, and PRC Realty Systems, Inc.

The INFORMIX Worldwide User Conference & Exhibition

Introduction

The Conference and Exhibition were held this year in the San Jose Convention Center. Some 5000 users attended and INFORMIX are to be congratulated on their ability to keep so many people fed, watered, and partied. This report has been compiled by the International Informix user Group to say thank-you to INFORMIX for their efforts.

The Tutorials

As usual the tutorials were well attended and provided a diverse selection of topics. In all there were 9 tutorials to choose from, with most being 1/2 day events meaning that attendees could attend two. The subjects covered were split between OnLine and NewEra, and the depth was sufficient to satisfy the most avid technical specialist.

The Conference

It was a challenge to accommodate the large number of people who attended. Each day began with a general session, including a keynote address by Marc Andreesen of Netscape, followed by track sessions. Each session could accommodate between 300 and 400 people, and there were 6 sets of sessions each day for 3 days. These sessions were divided into 6 tracks covering technology trends, business management, and advanced technology. The one disappointing thing about the track sessions was the difficulty in getting in to any session where Gary Kelley - the chief architect on OnLine - was speaking. That said, the sessions were well received by the majority of the attendees, although some of the more advance techies felt that the level of technical detail was lacking in some of the sessions.

The Exhibition

Computer exhibitions are the same all over the world, and with over 100 exhibitors to choose from there were many opportunities to get information on a variety of topics, ranging from which machine to choose for your very large database to applications and class libraries for NewEra. But with so many track sessions to choose from it was difficult to spend much time in this area. Perhaps INFORMIX and the exhibitors should consider keeping the exhibition open outside of conference hours longer.

The Parties

No conference would be complete without a fair degree of partying. And this conference was no exception. The partying started before the conference began when the Internet Users met in a brewery - where else - and continued for the whole week. The highlight of the partying was the full conference party when all of the attendees and the INFORMIX staff got together in a local facility for an evening of fun and entertainment. And, when the beer eventually ran out many members adjourned to other facilities in the San Jose area. Many of these centered on the Sports Bar in San Jose, the largest sports bar in the world. It's certainly worth a visit if you are in the area, or may be a few visits.

Your User Group Involvement

This year saw a new development at this conference. Five user group members got together to present a conference session on User Groups at Home and Around the World. Complete with maps showing where user groups were known to exist, and an explanation of how to access user group provided resources, this session attracted a good number of attendees.

Later the same day saw the formation of the new International Informix User Group and its inaugural meeting. At this meeting a board of directors was appointed, and there will be a follow up posting on comp.database.informix to explain more about this group.

And then came the highlight of the week as far as the user groups were concerned. The User Group reception. In past years this had been a fairly low-key event which was poorly attended. This year some 100+ people attended and enjoyed some lavish hospitality provided by INFORMIX, for which the user groups are very grateful.

And, as usual for the past few years, there was a user group stand in the exhibition. However, this year was different. The user group stand was not just a void in the exhibition area where people could pick up newsletters. The 5 user group speakers had worked to set up a schedule so that the stand was always manned, and had arranged that the stand would provide a valuable service to user group members and prospective members. This service was a Sun Sparcstation connected to the Internet so that users could look at User Group services on the WorldWide Web, and a PC so that users could download this information onto floppies for offline reading. INFORMIX funded the stand on our behalf and also arranged for the loan of equipment from Sun and provided the PC for our use.

The International Informix User Group would like to thank INFORMIX for their help, especially Nancy Twomey and Christine Shannon. Remember these names - they are the main user group contacts in INFORMIX - and look out for them attending user group meetings and conferences in the future. I am sure that you will all benefit from their involvement, and their commitment to the user group cause.

Malcolm Weallans

Online Database Consultancy

Email: onlinedbc@cix.compulink.co.uk

Phone (+44)628-72154

Fax (+44)628-3746

Roles - A New Security Feature in INFORMIX OnLine 7.10.UD1

INFORMIX OnLine 7.10.UD1 was released with a few surprises in the form of new features. One of the features I was most interested in is Roles. Roles provide a way to grant and revoke privileges to a function, rather than to individual users. A user is granted the privilege to use one or more Roles. When a user needs access to the privileges of a Role, the user or application sets the current access levels to the Role. And, when the user is finished performing the functions for which the Role was granted, the Role can be unset and the privileges are no longer in effect. This article will take a look at a few examples of using Roles to improve your security, and discuss some of the limits. The examples for this article were developed using the Stores database in OnLine 7.10.UD1 running on a Sun Sparc.

We will start with a quick example of using Roles. The stores database has a table called orders. For this example, we will restrict insert, update, and delete access to a group of users in the Orders Department. First, we must revoke all privileges from everyone on this table. Then, instead of granting the select, insert, update and delete privileges to each individual, we will create three Roles. One Role for select-only access, which we will call "read_ord". The next will be for select, update and insert access, which we will call "upd_ord", and the final one will include delete privileges, which we will call "del_ord". Then we will grant individuals the privilege to use these Roles. Finally, we will set-up the applications to use these Roles.

Creating Roles

To create a Role, we begin with the "CREATE ROLE role_name" statement, where role_name is an eight character name for the Role. The role_name cannot be the name of a user on the system because it is stored in the system table sysusers. To create a Role you must have dba privileges in the database. The following statements create our three Roles:

		create role read_ord;
		create role upd_ord;
		create role del_ord;

After creating the Roles we can perform the following query on the system table sysusers to see the Roles:

		select * from sysusers where usertype = "G";

This returns the following data:

		username 	usertype priority password
		read_ord 	G               5
		upd_ord  	G               5
		del_ord  	G               5

In the sysusers table, a usertype of "G", a new usertype in 7.10.UD1, indicates a Role definition.

Privileges for a Role

Granting privileges to a Role is the same as granting privileges to a user, and uses the same syntax. For our example to work, we must first revoke all privileges on the orders table. The following SQL statement will show all privileges that have been granted on the orders table:

	select * from systabauth where tabid in
		(select tabid from systables where tabname = "orders" ); 

To revoke privileges from public, we use the following SQL statement:

		revoke all on orders from public;

This command will need to be repeated for every user with privileges to the orders table.

Next, we will use SQL to grant the privileges to each Role:

		grant select on orders to read_ord;
		grant select, insert, update on orders to upd_ord;
		grant select, delete on orders to del_ord;

After granting the privileges, we can run our query against the system tables to see the results:

	select * from systabauth where tabid in
		( select tabid from systables where tabname = "orders" );


	Results:
		grantor		grantee		tabid 		tabauth
		lester		del_ord		101		s---d---
		lester		read_ord	101		s-------
		lester		upd_ord		101		su-i----

This shows that the user lester granted the privileges, the grantee column shows the Role name, and the tabauth column shows the privileges.

Adding Users to a Role

Now we need to add our users to the appropriate Roles. Let's say we have five users in the orders department: Abby, Joe, Ron, Jack, and Linda. We want everyone to read orders, linda and abby to add and update orders, and abby to be able to delete orders. To accomplish this, we need to use the following SQL statements.

		grant read_ord to abby, joe, ron, jack, linda;
		grant upd_ord  to abby, linda;
		grant del_ord  to abby ;

In 7.1 there is a new system table called sysroleauth that stores the information about users' access to Roles. If we perform a select on that table, we get back the following information:

		rolename 	grantee  	is_grantable
		read_ord 	abby     	n
		read_ord 	joe      	n
		read_ord 	ron      	n
		read_ord 	jack     	n
		read_ord 	linda    	n
		upd_ord  	abby     	n
		upd_ord  	linda    	n
		del_ord  	abby     	n

This shows the Role name, the users that have access to that Role and an N (No, cannot grant this Role to someone else) or Y (Yes, can grant this Role to someone else).

Using a Role - SET ROLE Statement

Once a user has been granted the privilege to use a Role, they do not yet have automatic access to the privileges of the Role. The user, or the application executed by the user, must first execute the SET ROLE statement. (Any user with SQL knowledge and connect privilege to the database can use the SET ROLE command to activate a role.)

If Joe tries to select data from the orders table before the SET ROLE statement has been executed, he will see the following error message:

		select * from orders;
		#                  ^
		#  272: No SELECT permission.

However, when Joe, or the application he is using, sets the current Role to one that has proper privileges, he will be able to read data. The following SQL command will set the Role and select all the data from the orders table:

		set role read_ord ;
		select * from orders;

When a user is finished with a Role, the Role can be set to NONE or NULL. This has the effect of taking away the privileges of the Role. In your application, when a user is done with a Role, use the SET ROLE NONE or NULL statement to end the use of the Role's privileges. The following is what happens when we do this for Joe.

		set role none;
		select * from orders;
		#                  ^
		#  272: No SELECT permission.

Roles in Your Applications

Roles are designed to be used in your applications. The application would set a Role, perform the tasks, and then unset the Role. This way a user only has the privileges while the application is running. Once the application is complete, the user has no privileges.

In order to use a Role in an application, you will need to prepare and execute a statement setting the Role for the application. The following statements are examples of what will be needed in a 4GL program to set the Role to "read_ord":

		prepare role_stmt from "set role read_ord"
		execute role_stmt
		if ( sqlca.sqlcode != 0 ) then
			error "Cannot use this role"
		fi

After executing the statement, check to make sure it was successful. Otherwise, the user will attempt to perform functions without the proper privileges, which will generate many other SQL errors.

There are several new error messages in 7.10.UD1 to handle Roles. For example, if a user does not have permission to use a Role, the sqlca.sqlcode is "19805: No privilege to set to the Role."

Conclusion

Roles are a great security feature, and when you have many users, this will enable you to more effectively control your database privileges. The only drawback, which is often true with a products new features, is that you will need to change your applications to take advantage of Roles. Unfortunately, Roles are not [yet?] available for INFORMIX SE 7.10.UD1. But I see nothing unique to OnLine in the implementation of Roles to prevent them from being implemented in SE.

I intend to start using Roles as a means for security in all new applications we develop. One question I keep getting asked is what is the impact of Roles on our security product DB Privileges? DB Privileges allows you to create groups of users and grant and revoke privileges to a group with an easy-to-use menu interface. The next version will include support for creating and controlling privileges through Roles with a menu interface. The current version of DB Privileges works well with Roles, since Roles are displayed on the Users screen. Table and column privileges can be granted to, and revoked from, Roles by entering a Y or N on the data entry screen.

Lester Knutsen

Advanced DataTools Corporation

Phone: 703-256-0267

Using the Internet to access Informix based resources

In the last 2 years much has been written about the Internet, some of it actually focused on the positive aspects of the network. With about 6 years of Internet experience myself, I have found that there is a tremendous amount of chaff out there -- but some incredible kernels of wheat as well. A growing number of Informix users are finding that some of the benefits of a traditional user group can be obtained electronically via the Internet. There are several world-wide forums that allow you to get technical advice, share a programming trick, or hear the latest Informix-related news, all without leaving your terminal or workstation. This article is intended to help you use the basic tools of the Internet to access those

computers which contain Informix related information.

The main suite of tools you'll be using are the Usenet group comp.databases.informix, a World Wide Web browser, anonymous ftp, electronic mail, telnet and a search service called "archie". To find out more about these and other tools, go to your local bookstore and get one or more well written books on the Internet. O'Reilly and Associates, publishers of the Nutshell series, have several excellent publications on the Internet and how to use the tools associated with it.

If you access the Internet via an on-line service (eg., CompuServe, A.O.L., Prodigy), I believe most have graphical applications to access files via ftp or the World Wide Web (here after abbreviated as WWW). They should have help files to explain how to use the application as well as the etiquette involved. To a lesser extent this is also true for Internet Service Providers (ISPs) where your access is Windows or Macintosh based. They should have a collection of graphical tools you can load to your own computer that allow you to point-and-click your way around the Internet. If you can set up a PPP/SLIP/CSLIP connection through your ISP, you can use a mixture of graphic or command line utilities -- either loaded on your own computer or on another remote computer depending on your needs and abilities.

List of Informix oriented sites

On a separate page is a listing of some of the computer systems which have Informix oriented tools, utilities or information. At first glance, the computer address may seem confusing, but in fact the address tells you how to access the computer and where to find the information once you are there. For example, in the address of "http://www.informix.com/" the "http://" means you need to use a WWW browser. In the computer name of "www.informix.com", the "www" preface means the computer will be responding in a hypertext based format so you'll be able to point-and-click your way around. In the address of "ftp.xyz.com/pub/informix/my_neat_stuff", you'll need to use anonymous ftp to log in to a computer called ftp.xyz.com. Once logged in, the information will be found in the "/pub/informix/my_neat_stuff" directory. While most computer systems administrators follow this format to identify their computers, you see names that have neither "www" or "ftp" at the beginning. In this case, try anonymous ftp first then your WWW browser.

Usenet, comp.databases.informix

For those unfamiliar with the term, "Usenet" is the name given to the collection of thousands of computer systems world-wide running software that allows them to act as one large distributed bulletin board system. Subscribers read and post news articles on their local systems, while background software controls the distribution of articles to other news sites. A "newsgroup" is analogous to an individual discussion forum on a typical small BBS. There is a newsgroup specifically oriented to Informix related issues. It is known as comp.databases.informix. Special software is required to read and post messages to this or any other newsgroup and is readily available from either your on-line service or ISP. Comp.databases.informix is dedicated to technically oriented discussions about the use and support of Informix software and related products. Topics include all of the Informix offerings, from C-ISAM to NewEra, plus third-party products. Membership is open to anyone, including end-users, vendors, and employees of Informix Software, Inc. The newsgroup is unmoderated, so subscribers are solely responsible for the content. All contributions are welcome, as long as they emphasize substantive information.

World Wide Web

Of the tools mentioned earlier, the most talked about right now is the WWW. From a simple to use graphical browser such as Netscape or Mosaic, you can point-and-click through a series of hypertext links to find information. The hypertext links are no different to use than the help section of any Windows/Macintosh/X-Window application. Words or phrases are highlighted in a different color. In these help functions if you click on the word or phrase, you go to that section. With the WWW, this is expanded in that these links can be to files on another computer somewhere else on the Internet. In WWW files you can the ability to display graphic images as well as text and make both hypertext links. "Surfing the web" is perhaps the best way of exploring the various and sundry topics that the Internet encompasses. Be forewarned however, tremendous amounts of time can be lost aimlessly wandering around if you are not focused in your search.

Anonymous ftp

Anonymous ftp is one of the greatest tools available on the Internet in my opinion. FTP (short for file transfer protocol) enables you to login to a computer and copy files from it to your own computer. Primarily a command line utility, you use the login name of "anonymous" and your own personal e- mail address as a password. Once logged in, you have a restricted ability to move around the directories and look at the file names there. You cannot see the contents of the files unless you copy them back to your own computer. This is accomplished using the "get" command. Help for any of the ftp commands, or ftp itself, is available by typing the word "help" while in a ftp session.

If you've never used anonymous FTP, here is a quick primer from Walt Hultgren, in the form of a sample session on mathcs.emory.edu with comments in {}. Lines where you type something are marked with "<<<" and what you type is bolded:

% ftp mathcs.emory.edu                         {or: ftp 128.140.2.1}  <<<
Connected to mathcs.emory.edu.
220 emory FTP server (SunOS 4.1) ready.
Name (mathcs.emory.edu:walt): anonymous                               <<<
331 Guest login ok, send ident as password.
Password: walt@rmy.emory.edu           {use your e-mail address here} <<<
230 Guest login ok, access restrictions apply.
ftp> cd pub/informix                                                  <<<
250 CWD command successful.
ftp> get README                                                       <<<
200 PORT command successful.
226 ASCII Transfer complete.
	{once copied to your system, you could read the contents of the file 
	then continue on}
ftp> get ls -lR                                                        <<<
200 PORT command successful.
226 ASCII Transfer complete.
          .
          .       {repeat this step for each file you want}
          .
ftp> quit                                                             <<<
221 Goodbye.
%

Electronic Mail

Electronic mail, as its name implies, is the ability to exchange written messages with others. There are so many tools, both command line and graphical to send and receive e-mail I could not attempt to explain how to use them all. You can also use e-mail to retrieve files from ftp servers, and participate in several Informix oriented mailing lists. There are two main Informix mail lists: "informix-list" and "iug-talk", both of which are currently based on the system rmy.emory.edu, located at Emory University in Atlanta, Georgia, U.S.A. Membership in the lists is open to anyone, including end-users, vendors and employees of Informix Software, Inc. There is no subscription or registration fee. The lists are unmoderated, so members are solely responsible for its content. All contributions are welcome, so long as they emphasize substantive information.

Informix-List carries technically oriented discussions related to the use and support of Informix software and related third-party offerings. Informix-List is gatewayed with the Usenet newsgroup comp.databases.informix. Messages posted to Informix-List are also posted to c.d.i automatically. IUG-Talk is the Informix User Group mailing list. It is a forum for discussing issues related only to Informix user groups. As such, its focus is relatively narrow.

To obtain more information about IUG-Talk, send e-mail to "majordomo@rmy.emory.edu" with message text consisting of the line "info iug-talk". The Subject line of the message will be ignored. For instructions on how to subscribe to the list, add the line "help" to the message.

To subscribe to Informix-List, send e-mail to "informix-list-request@rmy.emory.edu" with the following information:

1. The e-mail address or alias to be added to the list

2. Your name (not just your login ID)

3. Organization or Company name

4. Postal address

5. Voice phone number

Telnet

Telnet is used for logging into other computers on the Internet. Telnet is most often used to gain access to public service computers holding such things as library card catalogs and other kinds of databases as well as search tools like "archie". Most computers allowing general public telnet sessions require you to use specific login ids and passwords in order to get in. This information should be included with the computer name or displayed when you connect your telnet session to the computer.

Archie

Archie is a software program running on some servers that helps you find files that exist on other publicly accessible computers. There are a number of Archie sites around the world -- archie.rutgers.edu, archie.au, archie.funet.fi, archie.wide.ap.jp to name just a few. Once logged in to archie (via telnet), you can ask the program to find file names which contain keywords of your choosing or to suggest files whose descriptions contain keywords of your choice. Archie will return the computer name and location of the file(s) which match your search criteria. If you use a graphical based archie tool such as X-Archie, you can then click on the reference returned and you will be logged on to the referenced computer via anonymous ftp and be able to download the file to your computer.

If you are new to the Internet or never used it, I hope this has helped take some of the mystery out of using the tools available to you. The files you can access, the tools and the people you'll come in contact with using them can be a tremendous source of support and problem solving ideas as you work in the Informix environment.

Carlton Doe can be reached at dbaresrc@xmission.com. His WWW address with links to an number of Informix related items is http://www.xmission.com/~dbaresrc/index.html.

Informix resources available via the Internet

http://www.informix.com/
	Informix corporate home page

news:comp.databases.informix
	Usenet newsgroup discussing technical issues related to Informix
products and services

informix-list@rmy.emory.edu
	e-mail mirror of c.d.i for those who do not have usenet access.  Send e-
mail to "informix-list-request@rmy.emory.edu" with the following: e-mail
address to be added to the list, your name (not just your login ID),
organization /company name, postal address, voice phone #

iug-talk@rmy.emory.edu
	e-mail mailing list discussing user group related isuues only, send e-
mail to 	majordomo@rmy.emory.edu" with message text consisting of the
line "info iug-talk".

ftp://mathcs.emory.edu/pub/informix
	central repository of Informix related tools, archives of
comp.databases.informix, iug-talk, and some individual local user group
files

ftp://kcbbs.gen.nz
	home of the Informix FAQ

ftp://ftp.demon.co.uk
	mirror of the mathcs.rmy.edu server

ftp://cc1.kuleuven.ac.be/student/informix-faq
	copy of the HTML version of the FAQ

http://www.ece.vill.edu/~dave/index.html
	free software 

http://www.digital.com/
	all sorts of benchmark data on Digital Equipment

http://www.fourgen.com/
	commercial applications built with Informix tools/servers

http://www.garpac.com/informix.html
	assorted links and the FAQ

http://www.gate.net/~tschaefe
	the INX_UTIL utility

http://www.inmet.com/NBD/samedl.html
	ADA and SQL

http://www.lpac.ac.uk:80/~andym/
	general page on parallel databases

http://www.metu.edu.tr/METU/CC/OL/INFORMIX/
	old version of the FAQ

http://www.ncsa.uiuc.edu/SDG/People/jason/pub/gsql/starthere.html
	Mosaic/SQL gateway

http://www.strengur.is/~frantz
	Power-4gl

http://www.visionware.co.uk/informix-faq.html
	Old version of FAQ

http://www.xmission.com/~dbaresrc
	Utah Users Group and general Informix links, current HTML FAQ

http://www.mindspring.com/~bduncan
    	Southeast Users Group

http://www.madtech.com/iglug
	Chicago Users Group

http://www.cstp.umkc.edu/users/ewidjaja/kciug/kciug.html
	Kansas City, Kansas Users Group

ftp://ftp.demon.co.uk/pub/perl/db
	Perl scripts with Informix

ftp://prep.ai.mit.edu/pub/gnu/perl-5.000.tar.gz
	The latest version of the Perl language

ftp://quasar.ucar.edu/pub/Informix_Archive
	mirror of the mathcs.emory.edu site

http://arch-http.hq.eso.org/bfrasmus/wdb/
	the WDB web interface to Informix

http://library.byu.edu/staff/bkh/cgi_4gldoc.html
	Informix WWW interface

http://www.adc.com/ingres/ing-top.html
	Ingres related information

http://www.blackie.com/
	Blackie WWW interface to RDBMSs

http://www.access.digex.net/~waiug
	Washington Area Informix Users Group Page