This article will introduce you to the new user experience provided by Teradata Studio's Administration Perspective; an improved interface for creating and modifying database objects. It will also describe the new privileges forms for administering users, roles, and object level access. Note: This article is relevant for Teradata Studio 15.11 or later.
The following topics are discussed:
The first thing you will notice is the new layout for the Administration Perspective. The new layout is divided into 3 main panes: the Navigator View, the Filter View, and the Object List Viewer.
Select a connection profile from the Navigator, then choose the object category you wish to administer. Double clicking on a category folder in the Navigator will cause the object list to be displayed in the Object List Viewer.
The Navigator View is where you select the connection profile of the database you wish to administer. The list of connection profiles is the same list displayed in the Data Source Explorer (DSE). The Navigator also provides similar toolbar options as the DSE, such as actions to create new connection profiles, or import and export existing connection profiles. In addition, the Navigator toolbar includes an action to refresh the object lists based on the applied filters and an action to toggle on the hierarchical display for Teradata databases and users.
Once you have selected a connection profile, the list of database object categories is displayed, depending on the database (Aster, Hadoop, or Teradata).
For example, below shows the list of categories for a Teradata 15.10 database.
NOTE: If there is a filter applied to a category, the category name is appended with the "[Filtered]" annotation.
...and the list of categories for an Aster 6.10 database:
The first level objects for the Database and Schema categories are also displayed in the Navigator. Right clicking on an object in the Navigator will provide the list of "show" actions for displaying the lists in the Object List Viewer. The double-click action is shown with a check mark (for example "Show Tables"). You can also double-click on the category folder to display the top-level objects in the Object List Viewer.
The Filter View allows you to create filters to refine the object list displayed in the Object List Viewer. The filters are created based on the object category. For each category, there is a pre-defined list of properties or attibutes you can filter on. Filters represent a WHERE clause that is generated when retrieving the object list data. Multiple filters can be combined for a category using the AND/OR option. Filters are persisted as properties of the connection profile. Filters can be copied to the clipboard and pasted to another connection profile.
Checking the Filter 'Apply' button will apply that filter when the Object List Viewer is refreshed. Press the Refresh,, action in the Object List Viewer toolbar to refresh the Object List Viewer.
The Object List Viewer is where you can view the objects you wish to administer. The objects are displayed in a table format with a toolbar containing the available actions for the category, such as "Create Database", "Modify Database/User", "Open Database/User", or "Show Tables". You can also select an action from the right-click menu when an object is selected in the Object List Viewer table.
The 'Create', 'Modify', or 'Open' actions will cause a form to be opened below the Object List Viewer. The 'Show' actions that show dependent objects, such as Tables, Views, or Macros for a Teradata Database or User, will refresh the Object List Viewer with the dependent object list. The 'Show' actions that show space, files, or Jars information or actions such as 'Row Count' or 'Move Space' will show that information in a form below the Object List Viewer.
The Starts With text field provides a quick filter on the 'Name' column of the object list.
A Breadcrumb trail is provided to reflect the navigation path of where you are located in the database hierarchy and help you navigate back within the hierarchy. For example, if you navigate from a parent database (copy_db) to a child database (myCopy) and then to the list of tables within the child database, you may see a breadcrumb like:
The "Databases" nodes represent links to take you back to the list of Database objects. You can also navigate back by pressing the back arrow,, to the left of the breadcrumb trail. This will take you back one level in the breadcrumb trail.
NOTE: If there is a filter applied to a category, the category name is appended with the "[Filtered]" annotation.
You can manage the list of toolbar and menu actions displayed for each object category using the Object List Viewer Preference page. For Menu actions, you can also change the double-click action for each category.
Below shows the Object List Viewer menu actions preference page for Teradata object categories. Right-click on a category action to set it as the double-click action.
After removing the 'Show Child Objects Group' and changing the double-click action, the Object List Viewer right-click menu options for the Database category would then look like:
Using the Object List Viewer Preferences, choose the Menu tab and open the category and action group to locate the desired double-click action. Right click on the action and choose 'Set as Double-Click Action'.
Studio's Administration Perspective uses a form-based interface to display, create, and modify object information. The forms are presented in their own pane below the Object List Viewer. Forms can be moved by selecting and dragging it to a new location within the perspective.
The detailed information about objects is displayed by selecting an object and choosing the 'Open' object action. The open form provides vertical tabs containing the attributes of the object. Included is a 'Show Definition' tab to display the object's DDL definition. Select one or more objects in the Object List Viewer, right click and choose the 'Open' object menu option.
Below shows two database object forms opened in a tabbed display.
You can easily view privileges for an object by selecting the 'Privileges Held' tab (list of privileges that this database or user holds) or 'Privileges On' tab (list of privileges that are on this object).
Below shows an example of the list of privileges held by a database.
Below shows the list of privileges that are on this table object.
You can also move the individual forms side-by-side to easily compare the detail information of two or more objects, including the SQL definitions.
Below are two table forms opened and moved to a side-by-side display.
'Create' and 'Modify' actions will also open a form for the user to enter or modify the object attributes. Choose the vertical tab and enter the required information. Press 'Commit' to send the SQL DDL statement to the database. Once committed, the form is refreshed in a read-only state.
Below shows the Create Database form.
NOTE: Also note the filter icon,, to the right of the Parent database dropdown list above. This is a reminder that the database list is affected by any filters applied to the database category.
To create a new Table, choose (double-click) the Databases category in the Navigator. Locate the database in the Object List Viewer that you wish to create the table in and double-click. This will show you the list of tables and provide a toolbar action "Create Table''.
This will open a new 'Create Table' form below the Object List Viewer. First, in the General tab, enter a name for the table. Then select the Columns tab, press the Add button to add columns for your table. Enter a column name and select the column type. The drop down list will present the list of valid column types for the Teradata version you are creating your table in.Click Add to create another column definition. Next, choose the column Groupings, Indexes, Constraints, or additional information for the table. Lastly, select the SQL tab to view the generated SQL. Press the Commit toolbar button o execute the CREATE TABLE statement. This will create the table in the database and cause the list of tables to be refreshed in the Object List Viewer.
Showing object attributes, such as Show Object Space, Show Created Space, Show Child Space, Show Files, or Show Jars, will also display the data in a form below the Object List Viewer.
Below is a display of the 'Show Object Space' for a database object.
In the Navigation View toolbar, choose the 'New Connection Profile',, option to invoke the New Connection Profile dialog. This is the same dialog as provided in the Data Source Explorer. Choose the Connection Profile Type and press 'Next'. Enter the Database Server Name, User Name, and Password information for the database server and press 'Finish' to create a new connection profile. Studio uses JDBC to connect to your Teradata Database, Aster Database, or Hadoop system. ODBC is not supported with Studio. The Teradata JDBC driver, Aster JDBC driver, and HiveServer2 or Impala JDBC driver come embedded in the Studio product.
Studio provides an option to modify privileges for objects listed in the Object List Viewer. Select the object and choose the 'Modify Privileges' option to open the Privileges form. You can view the generated GRANT and REVOKE statements in the SQL tab of the privileges form.
For Databases and Users, you can grant privileges on the database or user to selected grantees. Select the database or user in the Object List Viewer and choose the 'Modify Privileges' action. Choose the Privileges Granted tab and select the Grantee Type (User, Role, or Public), select one or more Grantees, check the boxes of the Display Privileges categories, and press the Display Selected button. This will display the list of privileges in a table format with grantees along the vertical axis of the table and the list of possible privileges along the horizontal axis of the table.
You can now modify privileges by clicking or double clicking (for 'Grants with grant' option) on the privilege. Grant and Revoke privileges using the following legend:
For Databases, Users, and Roles you can select objects to grant and revoke privileges from. Select the database, user, or role in the Object List Viewer and choose the 'Modify Privileges' action. Choose the Privlieges Held tab, select the Object Type (such as Authorization, Database, Table, Macro, View, etc.), select the Database, select one or more Object Names, select the Display Privlieges categories, select the Grant Options, and press the Display Selected button. Again, this will display the list of privileges in a table format with grantees along the vertical axis of the table and the list of possible privileges along the horizontal axis of the table.
NOTE: When there are mulitple Grantees, as in the Privileges Held by example above, the form allows you to apply the privilege to all of the objects by selecting the 'Apply to all' button in the first row. The "mixed" icon,, indicates that there is a mix of privilege states for that privilege across the objects in the list.
For other objects, such as Tables, Views, Macros, Stored Procedures, etc., you can grant and revoke privileges to selected grantees via the Object Level Privileges tab. Select the object in the Object List Viewer and choose the 'Modify Privileges' action. Choose the Object Level Privileges tab and select the Grantee Type (User, Role, or Public), select one or more Grantees, check the boxes of the Display Privileges categories, check the Grant Options, and press the Display Selected button. Again, this will display the list of privileges in a table format with grantees along the vertical axis of the table and the list of possible privileges along the horizontal axis of the table.
For Teradata User objects, you can view the list of roles that a User is a member of by clicking the Role Membership tab.
For Tables and Views, you can also grant or revoke column level privileges. Select the table or view in the Object List Viewer and choose the 'Modify Privileges' action. Choose the Column Rights tab and select the Grantee Type (User, Role, or Public), select one or more Grantees, check the boxes of the Grant Options, and press the Display Selected button. Again, this will display the list of privileges in a table format with the columns listed along the vertical axis of the table and the list of column rights along the horizontal axis of the table.
For Users and Roles, you can also grant or revoke system level privileges. Select the user or role in the Object List Viewer and choose the 'Modify System Privileges' action. Choose the Privileges tab and select the Grantee Type (User, Role, or Public), select one or more Grantees, check the boxes of the Display Privileges categories, check the Grant Options, and press the Display Selected button. Again, this will display the list of privileges in a table format with grantees along the vertical axis of the table and the list of possible privileges along the horizontal axis of the table.
Studio 15.11 provides support for administering Teradata Secure Zones. Administrators can create, drop, modify, and add users and guest users for a Secure Zone. Double-click on the Secure Zone category in the Navigator to display the list of Secure Zones. Open Secure Zone, Create Secure Zone, Add/Drop Secure Zone Users, Modify Secure Zone, and Drop Secure Zone menu and toolbar actions are provided in the Object List Viewer.
You can open a Secure Zone object and display the list of users in a hierarchical tree display. Select the Secure Zone in the Object List Viewer and choose the Open Secure Zone action. Select the Users in Zone vertical tab. Users in the Zone are displayed in a tree format, showing parent/child relationship of users. Select the Users tree on the right side of the form. Guest users and roles are displayed below the list of Zone Users.
To add Users or Guest Users/Roles to a Secure Zone, select the Secure Zone in the Object List Viewer and choose the 'Add/Drop Secure Zone Users' action. Select, the Users in Zone vertical tab. To create a new Zone User, locate the parent of the new Zone user in the Users tree. Right click and choose the 'Create Zone User' action.
This will invoke the 'Create User' form. Once the form is completed and the operation commited, return to the Secure Zone form and choose the 'Refresh' menu option on the Users tree node to display the new Zone User.
Guest users can be added to the Secure Zone by right-clicking on the Users node in the Users tree. Choose the 'Add/Remove Zone Guests' menu option to open a mult-list display. Choose between Users or Roles, select a user from the Available Users list and move it to the Zone Guests list. Click 'OK' to create the GRANT ZONE statement for the guest user. Click the 'Commit' toolbar button to send the statements to the database. Once committed, the Secure Zone form is refreshed in a read-only state.
The following dialogs have been moved from the Administration Perspective to the Query Development Perspective:
The Access Logging and Query Logging dialogs are accessed through the Data Source Explorer, right-click on a Database or User object.
The Logon Rights and Connect Through Rights dialogs are accessed through the Data Source Explorer, right-click on a User object.
Statistics dialogs are accessed via a table or column selected in the Data Source Explorer. As well, Generate DDL and Rename are also accessed via objects selected in the Data Source Explorer.
These dilaogs will eventually be migrated to the new form-based user interface and brought back to the Administration Perspective.
In Studio, go to Help> Help Contents. Navigate the help topics on the left or type in a search text in the Search box.
Sorry but I do not like...
Where is the hierarchy of databases ?
The permissions window is horrible !
I will continue using the classic Teradata Administrator (It's more simple and beatiful)
How many DBAs / developers really use the studio ?...
Pablo, The Navigator allows you to toggle between hierarchy display for databases/users or drill in from databases/users to the tables, macros, views, etc. using menu or toolbar buttons in the Object List Viewer. I hope you can give it more time using the new interface.
This is quite a big shift in the User Interface. Maybe to the better, not shure yet...
But in the Role Dialog and 'Privileges Held', I'm not able to see the Databasename. Just Object, Column, Privilege and Grantor.
Something i have missed, or a bug?
Otherwise, the stuff i use daily seems to be in place!
When doing a generate DDL, we have comments before the actual DDL. If we change the procedure and do a replace using Studio, those comments are ignored, and we lose them.
I tried finding a setting for this, but couldn't.
Is this a jdbc driver setting perhaps?
Thanks in advance,
Randy, What version of Teradata are you using. Studio executes a SHOW command to get the DDL. In older versions of Teradata, the SHOW in some cases did not reflect the latest DDL.
We are still on 14.10, but using Studio 15.10. The Show statement will give us the Comments, as below .... but if I turn around and execute it within Studio, the comments are lost. Within SQLA they are not.
Looks like I was given bad information - my apologies. It doesn't lose the comments, it won't even produce the procedure with the comments before the REPLACE.
That is still problematic for us though.
Randy, I am seeing the statement fails with [3606 : 42000] Syntax error: Invalid SQL Statement when the comment comes before the REPLACE PROCEDURE.
I found a similar post on our Studio Forum and a detailed explanation from Tom Nolan:
The Teradata Database requires a special wire protocol to be used for the CREATE PROCEDURE and REPLACE PROCEDURE statements, that is slightly different than the wire protocol used for all other SQL statements.
Because of the need to use a different wire protocol, the Teradata JDBC Driver must examine the SQL request text and determine whether or not each SQL statement is CREATE/REPLACE PROCEDURE.
If you place a comment before the CREATE/REPLACE PROCEDURE keywords, then you will confuse the Teradata JDBC Driver, it won't recognize the SQL statement as CREATE/REPLACE PROCEDURE, and then it will send the SQL request to the Teradata Database using the normal wire protocol, and you will get the 3706 syntax error.
For comparison, BTEQ deals with this issue by forcing the user to put a CREATE/REPLACE PROCEDURE in a separate file, and use the special .COMPILE command to submit the CREATE/REPLACE PROCEDURE command to the Teradata Database. That is also a clunky workaround.
There is an outstanding RFC 94094 against the Teradata Database to remove the need for the special wire protocol for CREATE PROCEDURE and REPLACE PROCEDURE statements.