Archives of the TeradataForum

Message Posted: Mon, 19 Mar 2001 @ 21:48:19 GMT

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

You might consider a shared database for your users - effectively a sandbox were they can do what they want.

When I've implemented this scheme in the past, I create the database 'SANDBOX' where the default is for NO FALLBACK tables and where all users have CREATE TABLE access ONLY. All you need is:

GRANT CREATE TABLE ON SANDBOX TO user;

nothing more.

I let all the users have access to the 'SANDBOX'. Most users really don't have a need for temporary space, but at least they have a temporary space should they need it. Remember that whatever space is allocated to 'SANDBOX' and not used, is space that is available for spool space.

Since a user inherits all the rights on a table when they create it, only the creator will be able to DROP, ALTER, read or modify their tables. If they want, they can GRANT rights on that table to whoever they choose - and they have the responsibility for their own security. It's the user's responsibility to clean-up after they are done.

I make it clearly understood that this database is for temporary tables and that it will not be archived. If a user has a problem with the tables they create, then they have to fix it - there is no mechanism for recovery.

Also part of the policy of the 'SANDBOX' is that a nightly automated process will drop all non-FALLBACK tables 30 days after their creation. If a table has been made FALLBACK, then the table is ignored by the automated process and will have to be dropped manually. The point is that the users are responsible for cleaning-up the 'SANDBOX' and if they don't, a mindless process will.

The automated process is a simple BTEQ job that identifies candidate tables from the dictionary tables, based upon the CREATETIMESTAMP and PROTECTIONTYPE in DBC.TVM. Part of the automated process is to identify FALLBACK tables which have been around for longer than 60 days. Depending on your environment and how much work you want to put into your process, you can have that job send e-mail reminders to the creators of those tables.

For the most part, I've had good success with this approach. There is always some screaming the first couple of times that somebody ignores that the 'SANDBOX' is for temporary tables, but the lesson is quickly learned and it doesn't take long before it becomes part of the daily environment.

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