Archives of the TeradataForum

Message Posted: Mon, 13 May 2013 @ 13:59:30 GMT

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

Hello James,

I'll try to be brief, (security is not a topic explained in 123... ;-) )

In our shop, we have 3 different types of roles:

* Database (or data) access roles:

Each time a new database is created, automatically we create 5 such roles.

     - _EXEC (exec)
     - _QUERY (= _EXEC + select)
     - _DML (=_QUERY+insert, delete, update)
     - _DDL (= _DML + create / drop rights)
     - _BKR (= backup & restore rights)

* User roles (or user groups):

Users doing the same type of work, are grouped into a role. We call it an business role. That business role will be granted the database access roles it needs for those people to do their job. (but before we can do that, our in house process requires the "OK" from all necessary data-owners.) Example : all accountants are grouped into 1 role that contains all the rights they need to do for their job.

* applicative roles:

These kind of roles are assigned 1 on 1 to a particular technical user, created for a particular purpose.

Examples:

1) We have Web, olap, analytic applications. For most of them, the security is handled within the application itself. However in such case, they will still need a technical user (=middle tier user) to make a connection towards the TD database possible. In that case, we make 1 dedicated role for that user, being granted all data access roles it needs.

2) Backup restore:

This role holds all the _BKR roles for each database + accessright on all users. (non-database) The backup restore role is granted to a series of dedicated backuprestore users. (to make parallel backups possible)

In our shop we have the following rules:

- Direct accesses are NEVER granted to end-users directly, also not via data access roles. (if you have lots of end-users, the DBC accessrights table can grow extensively, and decrease logon performance)

- Database (or data) access roles are only granted to user roles and applicative roles, never to an end-user.

- End users can only be granted User roles, NEVER applicative roles. If that's needed, well, a role will have to be duplicated. (you must be able to distinguish end-users from technical users at all times) An end-user is allowed to have more than 1 user role. (in that case, be sure to set his default role to ALL)

- Applicative roles are 1 to 1 with technical users.

Besides that, we use stored procedures to create/remove databases, database groups, users, data roles, user roles, applicative roles etc.

Of course all is logged, so in case something is not completely as desired, you can look into the logs who has done what.

A big pro of the role base security is indeed the maintainability. You only need to grant the additional/missing right(s) to a role and every role member gets it immediately. Also it makes it easy getting an overview of all the members of a role.

Regards,

Raf

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