Archives of the TeradataForum

Message Posted: Fri, 28 Jan 2005 @ 17:55:39 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Before getting into my practical experience, I'll second the following:

- Non-empty databases in ARC.

- Roles & profiles.

- Run times for creation (@10/minute => 600/hour => ~1,250 hours) where the data dictionary is *locked*.

My additions:

- Security required us to delete stale accounts to eliminate possible compromise points on the system. There's no point in creating 750K userids today if 180 days later you'll be deleting 749K of them. Check into this immediately.

- Application & web servers with small families of userids are taking over from desktop applications. End user authentication is no longer on the Teradata, but has been pushed out to the server (Unix or Windows).

Our experience:

We had about 25,000 users configured on our DBC1012, and we were projecting an additional 30,000 or so (we found the original 32K limit in testing). Besides the 32K limit, the biggest issue was a huge accessrights table that slowed down a lot of system references. Since many of our userids were inactive (a lot were created and never used), we created a userid archiving process.

We cloned dbc.dbase in an archive database and added a few additional columns for local information, then copied the inactive users into the archive and dropped them from the system. Userids that owned objects were immune from archiving, but almost all of our users had none (site policy says objects are in container databases).

We also put in a cron job that checked failed logons once an hour to see if the missing id was in the archive. If so, the cron job pulled the information out of the archive and rebuilt the userid on the fly. The end users got used to the logon and wait an hour routine, and it usually gave them just enought time to hunt up the account administrator to get the password reset. We also had an administrator tool for immediate recreates.

A key discovery for us was that only 5% of requested accounts were ever used at all (I suspect a smaller percentage for Chris), so we changed our account creation process and put *all* new accounts in the archive to start. There were still manual tools available to the account administrators for forced creations, but they were little used.

This system was in use for ~7 years but it's gone now. We currently have about 4,000 active users and no archive. Why? Well, the world changed in a couple ways.

The first change was that our security requirements were tightened, and Chris you *really* should check into this. After 180 days of inactivity we are required to delete an account from the system completely (reduces break-in points). Archival was not considered a delete, so the process went away as did the stale accounts. This cut our active user count to about 8,000.

The second change was that our applications have been shifting away from VB applications on the end user desktop to application/web servers. This has shifted user authentication to the servers or to the Windows world. The servers themselves are connecting to the Teradata via a small number of server accounts (mywebapp1, mywebapp2, etc). I recommend that you encourage the application developers to use multiple userids, one for each class of query (makes system management easier). This has cut our user count to about 4,000 and it's still dropping.

Hope this was helpful. Comments and questions are welcome as always.

/dave "the archiver" hough

	<Prev	Next>		<<First	<Prev	Next>	Last>>