Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Access Rights
From:   Geoffrey Rommel

  I have a database with several tables, views and macros. What is the most efficient way to grant the following to ALL users:  

  No one should be able to access a table.  

  Everyone should be able to access any one of dozens of views.  

  Everyone should have the ability to execute any one of 7 macros.  

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.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023