![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Mon, 23 Dec 2002 @ 13:52:18 GMT
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.
--wgr
| |