Archives of the TeradataForum

Message Posted: Sun, 16 May 1999 @ 11:28:44 GMT

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

I suspect that most customers have a common sandbox which is shared amongst their users - this has certainly been the case amongst the customers to which I have knowledge. Even with a common sandbox, it seems like there is always a small group of users who have their own perm space.

Although I generally like the idea of a sandbox, I find that users aren't good at cleaning-up their tables and there isn't any way in which to limit how large any one user makes their tables. If all of your users are diligent, then the shared database works well. If your users are not that diligent, then I think that the administrator spends too much time in getting people to clean-up their temporary tables. There is also the issue of two (or more) users having contention over the use of tables with a common name (ie- LOCATION, INVENTORY, etc).

Even though I prefer to give individual users perm space, I generally set-up a sandbox - most customers feel that the sandbox is easier to administer. When I set-up the sandbox, I create a database with as much space as I can justify. I then give each user CREATE TABLE on that database - nothing else, only CREATE TABLE. When a user creates a table in the sandbox, they automatically receive all rights on that one table. Other users can not access that table, unless the creator grants rights to other users.

This approach provides good security on a user-by-user basis and helps to minimize table contention. The biggest problem, security wise, is that all users can do a HELP DATABASE on the sandbox and see what tables have been created and once they know that a table exist, they can do a SHOW TABLE on those for which they are snooping. For most customers, this isn't a big problem. For others, it is.

I require that all users prefix their tables with their UserID (ie- JHALL_TEMP1).

I also require that tables which are created in the sandbox to be non-fallback.

It's relatively easy to set-up some simple batch jobs which automatically does a periodic clean-up of the sandbox. One job can drop any table which does not have a legal UserID prefix. The other job can drop any table which is older than a specific period (ie- 30 days). When I've done this in the past, I've provided overrides so that tables which begin with 'KEEP_' or have been created as fallback are kept indefinitely.

When I have users who have trouble following the rules or regularly have problems with their SQL (ie- skewed or excessively large tables), I give them a very limited amount of perm space and they can use their own space. When I say limited, I mean that they need to talk with the DBA regularly about what they are doing (I know: it's a pain in the hindquarter - but it works).

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