Archives of the TeradataForum

Message Posted: Mon, 23 Dec 2002 @ 13:52:18 GMT

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

The best way, Jim, would be to separate the tables (db_of_tables) from the views and macros (db_of_views). This is fairly standard procedure in Teradata-land. You would then grant the following:

grant select
 on db_of_tables
 to db_of_views
 with grant option;
grant select, execute
 on db_of_views
 to [ALL group_of_users, or a role, or PUBLIC, or individual ID's];

As Dave mentioned, if you must keep everything in the same database, you must grant the rights on every object individually. Yuk.

Since you aren't a DBA, you probably aren't aware of why this is undesirable. The reason is that nearly every access right you grant will insert a new row into DBC.AccessRights. If you grant, say, select and execute to a database, as in the statements above, only one row will be inserted into the table; it will have a TableName of 'All' and a ColumnName of 'All' (or actually '00'xb, which means 'All'). There will still be one row per user, but at least there will only be one row per database. If you have 500 users, this will result in 500 rows. If, however, you grant access on individual objects, a row will be inserted for each object and each user. If there are 100 objects in the database and 500 users, you will need 50,000 rows in AccessRights just for this one database. Now, keep in mind that AccessRights has to be searched every time anyone runs a query. Probably most of these searches are one- or two-AMP operations, but cutting down on the number of rows always helps.

--wgr

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