We have a need for a database user and role security reporting script to report by user or by AD Use

Database

We have a need for a database user and role security reporting script to report by user or by AD Use

Teradata Database Security Report Requirements

 

We have a need for a database user and role security reporting script to report by user or by AD User Group to be used within our regular IT Auditing review. We are looking for information found in the MS SQL database for the similar columns in teradata

 

Database users and roles report column attributes:
1. ServerName 
2. DatabaseName 
3. UserName 
4. AcctType - (Note - Indicates whether the user account is a Active Directory Account or an Active Directory Group or a database SQL Account) 
5. UserAccount DateCreated format (MM/DD/CCYY) 
6. UserAccount DateUpdated format (MM/DD/CCYY)

Note - For the below columns db_owner through db_executor we are looking for an indicator to indicate whether the user has this permission or not.
For an example In SQL dB this is indicated by using an 'X' and it left empty is the user does not have this permission.
 
7. db_owner 
8. db_accessadmin 
9. db_securityadmin 
10. db_ddladmin 
11. db_backupoperator 
12. db_datareader 
13. db_datawriter 
14. db_denydatareader 
15. db_denydatawriter 
16. db_executor 
17. Report Status or CheckDate data format (MM/DD/CCYY) 


Database User logins report column attributes:
1. ServerName 
2. Loginname 
3. AcctType 
4. UserAccount createdate date format (MM/DD/CCYY) 
5. UserAccount updatedate date format (MM/DD/CCYY)
 
Note - For the below columns sysadmin through bulkadmin we are looking for an indicator to indicate whether the user has this permission or not.
For an example In SQL dB this is indicated by using an 'X' and it left empty is the user does not have this permission.

6. sysadmin 
7. securityadmin 
8. serveradmin 
9. setupadmin 
10. processadmin 
11. diskadmin 
12. dbcreator 
13. bulkadmin 
14. Report Status or CheckDate date format (MM/DD/CCYY)

  • database.teradata
  • SQL ASsistant
Tags (2)
2 REPLIES
Junior Supporter

Re: We have a need for a database user and role security reporting script to report by user or by AD

Hi,

A number of your labels for reporting do not really have an equivalent in Teradata. For example there is not relaly anything that would identify a userid as having the  "db_accessadmin", because this isn't an access right in Teradata.

I think you are going to have to really think about what each of your labels means to you.

For example:

db_owner - is this just any userid that owns another database or user? If so then you're looking for any userid (or database) that is shown in the 'ownername' column in dbc.databasesv.

db_ddladmin - this might be any userid that has any of the CREATE or DROP access rights on any object in the system. Although I suspect that will end up being virtually every userid.

Regards,

Dave

 

Re: We have a need for a database user and role security reporting script to report by user or by AD

Thanks for the reply Dave, i will review.

Ron