All Forums Database
Cesco 13 posts Joined 02/13
05 Aug 2016
User's DDL

Hello everybody,
Do you know if there is a way to know the DDL of an existing user?
In addition, I need to know if a user has the logon privilege or not, but I can't find how can do it.
Thank you, in advance.

Glass 225 posts Joined 04/10
05 Aug 2016

sel * from dbc.users where username = 'youruser';
logon status from dbc.logonrules.

Fred 1096 posts Joined 08/04
05 Aug 2016

There is nothing like a "SHOW" statement that will generate a CREATE USER for you. You would need to reverse engineer the values from the dictionary columns yourself. Also the UsersV view will only show users you have the rights to MODIFY. If you just need the names, you can query DatabasesV and look for DBKind=U.
Note that LogonRulesV has specific and generic rules. Check the Data Dictionary manual and/or Security Admin Guide.

Johannes Vink 28 posts Joined 08/14
06 Aug 2016

Maybe, but I did not check, the new Teradata Studio can generate the SQL for you.

abhishek.jadhav 28 posts Joined 02/08
06 Aug 2016

Like others mentioned there is no direct way that will show the DDL of any user.In addition what is mentioned in above replies you can try the below steps and see if thats what you are looking for.
In Teradata Administrator,make sure you have checked the option “preview SQL before submitting”
Next,select the user for which you want the DDL.
Now click “Clone user” (Tools > Clone User).
Enter some username and password and click on “Create”. You will see the DDL of the user. Everything except the username and password will the same as the user whose DDL you wanted. After you get the DDL cancel the clone user task.

Abhishek Jadhav

Cesco 13 posts Joined 02/13
09 Aug 2016

Thank you all for your answers.
Unfortunatly, in my company we can't use the "Teradata studio", but I can obtain all the informations I need from DBC.logonrules, DBC.users and using the clone user tool.

You must sign in to leave a comment.