User's DDL

Database
Enthusiast

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.

5 REPLIES
Enthusiast

Re: User's DDL

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

logon status from dbc.logonrules.

Rglass

Teradata Employee

Re: User's DDL

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.

Enthusiast

Re: User's DDL

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

Junior Supporter

Re: User's DDL

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
Enthusiast

Re: User's DDL

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.

BR.