When a request is submitted, Teradata parser parses the request and checks for appropriate access rights .We are managing access rights through Roles.There are some direct access rights granted also( DBC.AllRIghts).Which one is better method as far as performance is considered: Accessing through Roles or access through direct access at object level?
Accessing through Roles is always considered to be better. In an environment where the number of users and the number of objects are more(which is usually the case with Teradata), it is always better to have roles for access. The reason being if you start granting rights at the object level, the dbc.accessrights table will be flooded. As you know each query fired will have to access the rights tables, it will start taking more time and if multiple queries from the same user comein then there is a greater possibility of a deadlock. we need to try keeping the rights tables as small as possible. Any user creating an object will have explicit rights on the object which cannot be avoided.