Migrating to ROLEs

[RogerCruse]

Hi,
Currently, all access rights use the standard USER / DATABASE model and I want to migrate to a ROLE / DATABASE model now possible with V2R5.

This should reduce the overall size of dbc.accessrights and reduce LOCKS occuring plus greatly simplify the administration process.

Has anybody done this?
What issues are there and how did you resolve them?

So far, to test my understanding of Roles within Teradata I've created a couple of userids and assigned them a Role and granted this Role access to a database.

e.g.
CREATE ROLE ROLE_TEST1;
GRANT SELECT ON DATABASE_TEST1 TO ROLE_TEST1;
GRANT ROLE_TEST1 TO USER1;

But when I test it - getting USER1 to access a table in DATABASE_TEST1 - it fails. If I give USER1 access directly, it works.

What have I missed?
Is there something NCR need to enable?

Roger...

 

[dnoeth]

As you can have more than one role, you have to switch on a role within a session:

.logon user1,....
SET ROLE role_test1;

Now that role is active until User1 logs off, submits a SET ROLE NONE or switches to another role SET ROLE another_role.

or assign it as a default role to a user:
MODIFY USER user1 AS DEFAULT ROLE = role_test1;


The main problem in implementing roles is to find out which roles to create (and which to nest).
And if you have lots of inherited rights the job revoking all those rights (don't forget the ALL keyword) and regranting them to the roles may run for quite a long time.

Dieter

 

[RogerCruse]

Hi Dieter,
I've now got ROLEs working.

And, yes, working out the ROLEs to create did take longer than I expected. I haven't yet removed all the USER rights yet, But I have a question about nesting ROLEs.

Is there an overhead in doing this?

I mean, won't Teradata have to resolve the nests first and won't this take time?

If I kept the nesting to two levels would the overhead be manageable?

Finally. How do I code a nest of ROLEs?

Roger...

 

[dnoeth]

Nesting:
GRANT role1 TO role2;

Of course there's a small overhead to check nested roles, but you can nest only one level in the current release ;-)

Dieter