defaria.com/blogs/Status/archives/000640.html

   1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
   2    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   3 <html xmlns="http://www.w3.org/1999/xhtml" id="sixapart-standard">
   4 <head>
   5    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
   6    <meta name="generator" content="Movable Type 5.2.3" />
   7
   8    <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles-site.css" type="text/css" />
   9    <link rel="alternate" type="application/atom+xml" title="Atom" href="http://defaria.com/blogs/Status/atom.xml" />
  10    <link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://defaria.com/blogs/Status/index.xml" />
  11
  12    <title>Status for Andrew DeFaria: LDAP changes/Synonyms and Grants/Reports</title>
  13
  14    <link rel="start" href="http://defaria.com/blogs/Status/" title="Home" />
  15    <link rel="prev" href="http://defaria.com/blogs/Status/archives/000639.html" title="GPDB/LDAP/Org/SBE" />
  16    <link rel="next" href="http://defaria.com/blogs/Status/archives/000641.html" title="GPDB Enhancements" />
  17
  18    <!--
  19 <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
  20          xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"
  21          xmlns:dc="http://purl.org/dc/elements/1.1/">
  22 <rdf:Description
  23     rdf:about="http://defaria.com/blogs/Status/archives/000640.html"
  24     trackback:ping="http://defaria.com/mt/mt-tb.cgi/28"
  25     dc:title="LDAP changes/Synonyms and Grants/Reports"
  26     dc:identifier="http://defaria.com/blogs/Status/archives/000640.html"
  27     dc:subject="GPDB"
  28     dc:description=" Change primitives::login and ldap_authenticate to return a user record of user information if we can find it in LDAP, even if we could not authenticate the password, and pass back proper status Straightened out new views WRT Synonyms and..."
  29     dc:creator=""
  30     dc:date="2007-05-04T14:35:52-06:00" />
  31 </rdf:RDF>
  32 -->
  33
  34
  35
  36
  37    <script type="text/javascript" src="http://defaria.com/blogs/Status/mt-site.js"></script>
  38 </head>
  39 <body class="layout-one-column" onload="individualArchivesOnLoad(commenter_name)">
  40    <div id="container">
  41       <div id="container-inner" class="pkg">
  42
  43          <div id="banner">
  44             <div id="banner-inner" class="pkg">
  45                <h1 id="banner-header"><a href="http://defaria.com/blogs/Status/" accesskey="1">Status for Andrew DeFaria</a></h1>
  46                <h2 id="banner-description">Searchable status reports and work log</h2>
  47             </div>
  48          </div>
  49
  50          <div id="pagebody">
  51             <div id="pagebody-inner" class="pkg">
  52                <div id="alpha">
  53                   <div id="alpha-inner" class="pkg">
  54
  55                      <p class="content-nav">
  56                         <a href="http://defaria.com/blogs/Status/archives/000639.html">&laquo; GPDB/LDAP/Org/SBE</a> |
  57                         <a href="http://defaria.com/blogs/Status/">Main</a>
  58                         | <a href="http://defaria.com/blogs/Status/archives/000641.html">GPDB Enhancements &raquo;</a>
  59                      </p>
  60
  61                      <a id="a000640"></a>
  62                      <div class="entry" id="entry-640">
  63                         <h3 class="entry-header">LDAP changes/Synonyms and Grants/Reports</h3>
  64                         <div class="entry-content">
  65                            <div class="entry-body">
  66                               <ul>
  67   <li>Change primitives::login and ldap_authenticate to return a user record of user information if we can find it in LDAP, even if we could not authenticate the password, and pass back proper status</li>
  68
  69   <li>Straightened out new views WRT Synonyms and Grants</li>
  70
  71   <li>Implemented Users, Clearcase and DesignSync reports</li>
  72 </ul>
  73                            </div>
  74                            <div id="more" class="entry-more">
  75                               <h2>Synonyms and Grants</h2>
  76
  77 <p>When one creates a table or view in Oracle it appears to be "private" to the user who has created it. Others can get to the table but they need to specify a more qualified name. Additionally the need to have been granted access to it (at least select access).</p>
  78
  79 <p>So if we create a view, say foo, we also need to grant select access to those tables to the other users who might be using it. Additionally, in GPDB we have 3 DB level usernames: cm_gpdb, cm_gpdb_readonly and cm_gpdb_update. So if we create the view foo while logged in as cm_gpdb then login as say cm_gpdb_readonly, this new session will not be able to see foo.</p>
  80
  81 <p>So then the sequence is:</p>
  82
  83 <ol>
  84   <li>Create the table or view</li>
  85
  86 <div class=code><pre>
  87 create or replace view foo ...
  88 </pre></div>
  89
  90   <li>Grant select privileges to the table or view:</li>
  91
  92 <div class=code><pre>
  93 grant select to foo on cm_gpdb.foo for cm_gpdb_readonly
  94 </pre></div>
  95
  96   <li>Log into Oracle database as other user and create synonym:</li>
  97
  98 <div class=code><pre>
  99 create or replace synonym foo for cm_gpdb.foo
 100 </pre></div>
 101 </ol>
 102
 103 <h4>Notes</h4>
 104
 105 <ol>
 106   <li>The database's schema name for GPDB is cm_gpdb above</li>
 107
 108   <li>Grants need to be done whenever the table or view is recreated. Synonyms, being pointers, need to be created only when a new pointer is needed</li>
 109 </ol>
 110                            </div>
 111                         </div>
 112                         <p class="entry-footer">
 113                            <span class="post-footers">Posted by  on May  4, 2007  2:35 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000640.html">Permalink</a>
 114                         </p>
 115                      </div>
 116
 117
 118                      <div class="trackbacks">
 119                         <h3 id="trackback" class="trackbacks-header">TrackBack</h3>
 120                         <div id="trackbacks-info">
 121                            <p>TrackBack URL for this entry:<br />http://defaria.com/mt/mt-tb.cgi/28</p>
 122                         </div>
 123                         <div class="trackbacks-content">
 124
 125                         </div>
 126                      </div>
 127
 128
 129
 130                   </div>
 131                </div>
 132             </div>
 133          </div>
 134       </div>
 135    </div>
 136 </body>
 137 </html>