{"id":674,"date":"2016-06-08T07:39:12","date_gmt":"2016-06-08T07:39:12","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=674"},"modified":"2016-06-08T08:15:58","modified_gmt":"2016-06-08T08:15:58","slug":"oracle-test-a-database-link-of-a-user-as-sys-user","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=674","title":{"rendered":"Oracle &#8211; test a database link of a user as sys user"},"content":{"rendered":"<pre>\r\n\r\n1. A database link exists in the current database owned by CURRENTSCHEMA user (you don't know the password of the CURRENTSCHEMA user)\r\n\r\nThe database link was created by the application owner as follows:\r\n\r\n  CREATE DATABASE LINK \"'DBLINKNAME.DOMAIN.COM\"\r\n   CONNECT TO \"TARGETSCHEMA\" IDENTIFIED by password\r\n   USING 'TNSNAMES_ALIAS';\r\n\r\n2. Create a table in the target database as a snapshot of the current v$session dynamic view\r\n\r\n                       create table x as select * from v$session;\r\n\r\n3. grant select on the table to TARGETSCHEMA\r\n\r\n                       grant select on x to targetschema;\r\n\r\n4. Use anonymous pl\/sql block below to execute a command on the new table to prove that the database link \r\n   exists, works, and is pointing at the right target database.\r\n\r\nset serverout on size unlimited (if using sqlplus)\r\n\r\ndeclare\r\nstrresult varchar2(1024);\r\nstmt_cursor NUMBER;\r\nrc          NUMBER;\r\nfound       NUMBER := 0;\r\np_schema    VARCHAR2(30) := 'CURRENTSCHEMA';\r\np_obj       VARCHAR2(30) := 'DBLINKNAME.DOMAIN.COM';\r\nuid    dba_users.user_id%TYPE;\r\nBEGIN\r\n  select user_id into uid from dba_users where username=p_schema;\r\n  stmt_cursor := dbms_sql.open_cursor;\r\n  sys.dbms_sys_sql.parse_as_user(stmt_cursor, \r\n      'BEGIN SELECT COUNT(*) INTO :found \r\n             FROM user_db_links\r\n             WHERE db_link = :p_obj;\r\n       EXCEPTION WHEN OTHERS THEN :found := 0;\r\n       END;', dbms_sql.NATIVE,\r\n       uid);\r\n  --dbms_sql.bind_variable(stmt_cursor, ':p_schema', p_schema);\r\n  dbms_sql.bind_variable(stmt_cursor, ':p_obj', p_obj);\r\n  dbms_sql.bind_variable(stmt_cursor, ':found', found);\r\n  rc := dbms_sql.execute(stmt_cursor);\r\n  dbms_sql.variable_value(stmt_cursor, ':found', found);\r\n  dbms_sql.close_cursor(stmt_cursor);\r\n  \r\n  dbms_output.put_line('FOUND: '||found);\r\n  \r\n  IF found = 1 THEN\r\n     stmt_cursor := dbms_sql.open_cursor;\r\n     sys.dbms_sys_sql.parse_as_user(stmt_cursor, 'select machine from sys.x@'||p_obj, dbms_sql.NATIVE,uid);\r\n     sys.dbms_sys_sql.define_column(stmt_cursor,1,strresult,1000);\r\n     rc := dbms_sys_sql.execute(stmt_cursor);\r\n     dbms_output.put_line('rc is: '||to_char(rc));\r\n     loop\r\n         if ( dbms_sys_sql.fetch_rows(stmt_cursor) > 0 )\r\n         then\r\n           dbms_sys_sql.column_value(stmt_cursor, 1,strresult );\r\n           dbms_output.put_line('table_name is: '||strresult);\r\n         else\r\n          exit;     \r\n         end if;     \r\n     end loop;\r\n     dbms_sql.close_cursor(stmt_cursor);\r\n     DBMS_OUTPUT.PUT_LINE(stmt_cursor);\r\n  END IF;\r\nEND;\r\n\/\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1. A database link exists in the current database owned by CURRENTSCHEMA user (you don&#8217;t know the password of the CURRENTSCHEMA user) The database link was created by the application owner as follows: CREATE DATABASE LINK &#8220;&#8216;DBLINKNAME.DOMAIN.COM&#8221; CONNECT TO &#8220;TARGETSCHEMA&#8221; &hellip; <a href=\"https:\/\/dbafox.com\/?p=674\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[28],"tags":[],"class_list":["post-674","post","type-post","status-publish","format-standard","hentry","category-oracle-technical"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Oracle - test a database link of a user as sys user - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=674\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle - test a database link of a user as sys user - dbafox\" \/>\n<meta property=\"og:description\" content=\"1. A database link exists in the current database owned by CURRENTSCHEMA user (you don&#039;t know the password of the CURRENTSCHEMA user) The database link was created by the application owner as follows: CREATE DATABASE LINK &quot;&#039;DBLINKNAME.DOMAIN.COM&quot; CONNECT TO &quot;TARGETSCHEMA&quot; &hellip; Continue reading &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=674\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-08T07:39:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-06-08T08:15:58+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbafox.com\/?p=674\",\"url\":\"https:\/\/dbafox.com\/?p=674\",\"name\":\"Oracle - test a database link of a user as sys user - dbafox\",\"isPartOf\":{\"@id\":\"https:\/\/dbafox.com\/#website\"},\"datePublished\":\"2016-06-08T07:39:12+00:00\",\"dateModified\":\"2016-06-08T08:15:58+00:00\",\"author\":{\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\"},\"breadcrumb\":{\"@id\":\"https:\/\/dbafox.com\/?p=674#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbafox.com\/?p=674\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbafox.com\/?p=674#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbafox.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle &#8211; test a database link of a user as sys user\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbafox.com\/#website\",\"url\":\"https:\/\/dbafox.com\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbafox.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"sameAs\":[\"http:\/\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Oracle - test a database link of a user as sys user - dbafox","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbafox.com\/?p=674","og_locale":"en_GB","og_type":"article","og_title":"Oracle - test a database link of a user as sys user - dbafox","og_description":"1. A database link exists in the current database owned by CURRENTSCHEMA user (you don't know the password of the CURRENTSCHEMA user) The database link was created by the application owner as follows: CREATE DATABASE LINK \"'DBLINKNAME.DOMAIN.COM\" CONNECT TO \"TARGETSCHEMA\" &hellip; Continue reading &rarr;","og_url":"https:\/\/dbafox.com\/?p=674","og_site_name":"dbafox","article_published_time":"2016-06-08T07:39:12+00:00","article_modified_time":"2016-06-08T08:15:58+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=674","url":"https:\/\/dbafox.com\/?p=674","name":"Oracle - test a database link of a user as sys user - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2016-06-08T07:39:12+00:00","dateModified":"2016-06-08T08:15:58+00:00","author":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=674#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=674"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=674#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"Oracle &#8211; test a database link of a user as sys user"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":"Person","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","sameAs":["http:\/\/www.dbafox.com"]}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3ecMb-aS","_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/674","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=674"}],"version-history":[{"count":3,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/674\/revisions"}],"predecessor-version":[{"id":678,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/674\/revisions\/678"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=674"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}