Security - DBADMIN vs individual DBA accounts - tracing accountability

Database
Visitor

Security - DBADMIN vs individual DBA accounts - tracing accountability

Afternoon,

We are wrestling with out security model a little so looking to build on other people's experience.

We have a system where we have a number of defined DBAs - we created them as such for auditing purposes so they are along the lines of john_dba, bob_dba etc. However the Teradata documentation recommends doing all DBA type activites using a DBADM (Database Administration) and SECADM (Security Administration) ids. Using the separate ids as we do means that whenever one of us creates objects - databases, users, roles etc then they have to then grant the access to control those objects to the other DBAs. This seems clumsy.

I would have expected that we should be able to log on as out individual ids e.g. john_dbc and then perform the equivalent of a "sudo" to the DBADM or SECADM id. This would keep the security model very neat - but I can find nothing of the kind.

Very open to ideas.

Chris.

3 REPLIES
Teradata Employee

Re: Security - DBADMIN vs individual DBA accounts - tracing accountability

Using DBADM (or SECADM), you could create stored procedures with SQL SECURITY CREATOR, which perform administrative functions via dynamic SQL. Then grant EXECUTE PROCEDURE to the individual DBAs (directly or via role). Such a stored procedure should also include whatever audit logging is required. 

Junior Supporter

Re: Security - DBADMIN vs individual DBA accounts - tracing accountability

Hi Chris,

Initially we had a sysadmin id and all the DBAs used that to all admin/install activities. But, we had tracebility issues, so we created individual DBA ids as you did. But, we dont have to give access explicitly when one dba creates any object. We have roles called dba_install role and dba_admin role.  install role that has all DDL/DML access on that platform on DB level and admin role has all execute SP, execute macro access on platform at DB level. So, if any dba creates an object, the other DBAs implicitly get access on them.

--Samir Singh

Junior Supporter

Re: Security - DBADMIN vs individual DBA accounts - tracing accountability

as Fred pointed out, we have different stored procedures to perform different admin functions