{"id":1633,"date":"2018-07-14T15:18:20","date_gmt":"2018-07-14T15:18:20","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1633"},"modified":"2018-07-14T15:18:20","modified_gmt":"2018-07-14T15:18:20","slug":"how-to-find-all-access-rights-in-teradata","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1633","title":{"rendered":"How To Find All Access Rights In Teradata"},"content":{"rendered":"<p>The problem with the table DBC.AccessRights is that the data is not something easily readable.\u00a0 Many of the columns use a 4-byte IDs.<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/AccessRights.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1634 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/AccessRights.jpg\" alt=\"DBC.AccessRights\" width=\"1197\" height=\"221\" \/><\/a><\/p>\n<p>By joining a couple of tables to the DBC.AccessRights table, the information brought back is more meaningful.\u00a0 Also by adding a couple of predicates in a where clause will help isolate this information to the desired rights on a user, database, or what ever is being searched for.<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1635 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights.jpg\" alt=\"Meaningfull Access Rights\" width=\"1500\" height=\"625\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights.jpg 1500w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights-300x125.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights-768x320.jpg 768w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights-1024x427.jpg 1024w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2018\/07\/MeaningfullAccessRights-210x88.jpg 210w\" sizes=\"(max-width: 1500px) 100vw, 1500px\" \/><\/a><\/p>\n<pre>sel\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 UI.DatabaseNameI as \"UserName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DI.DatabaseNameI as \"DatabaseName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 TI.TVMNameI as \"ObjectName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 TI.TableKind as \"ObjectType\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AR.FieldId,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AR.AccessRight,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AR.WithGrant,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 GI.DatabaseNameI as \"GrantorName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AR.AllnessFlag,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 CI.DatabaseNameI as \"CreatorName\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AR.CreateTimeStamp,\r\n\u00a0\u00a0\u00a0\u00a0 AR.LastAccessTimeStamp,\r\n\u00a0\u00a0\u00a0\u00a0 AR.AccessCount\r\nfrom DBC.AccessRights AR\r\ninner join dbc.dbase UI \r\n\u00a0\u00a0 \u00a0on AR.UserId = UI.DATABASEID\r\ninner join dbc.dbase DI \r\n\u00a0\u00a0 \u00a0on AR.DatabaseId = DI.DATABASEID\r\ninner join dbc.dbase GI \r\n\u00a0\u00a0 \u00a0on AR.GrantorID = GI.DATABASEID\r\ninner join dbc.dbase CI \r\n\u00a0\u00a0 \u00a0on AR.CreateUID = CI.DATABASEID\r\ninner join dbc.tvm TI \r\n\u00a0\u00a0 \u00a0on AR.TVMId = TI.TVMId\r\norder by 2;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The problem with the table DBC.AccessRights is that the data is not something easily readable.\u00a0 Many of the columns use a 4-byte IDs. By joining a couple of tables to &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=1633\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1634,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[54],"tags":[134],"class_list":["post-1633","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teradata","tag-dbc-accessrights"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1633"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1633"}],"version-history":[{"count":3,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1633\/revisions"}],"predecessor-version":[{"id":1638,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1633\/revisions\/1638"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1634"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1633"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1633"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1633"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}