Oracle - Grant SELECT On All Tables In A Schema


	Oracle - Grant SELECT On All Tables In A Schema

ghiberti

10-4-2006 8:39 AM

12293 views

tags:

oracle, dba

1 Comment |

Add a Comment

4-2-2009 5:35 PM

kgjames1

Couple bugs in the code provided:
1. Remove the end of line character ';' in the FOR loop, just before the LOOP.
2. The cursor referenced in the for loop doesn't match the declaration above. Replace user_tabs_cur with ut_cur.

Seems to work after those adjustments... Where is this code coming from anyway? Doesn't look like it gets tested very well, so be cautious with what you get here!

Login to Comment. Not a member yet? Sign up

Today's Top Clips

visit the Top Clips page

View the Top Clips from October 4, 2006

Embed This Clip In Your Site...

<div style="margin: 12px 0px; font-family: arial; color: #333333; background: #ffffff; border: solid 4px #e5e5e5; width: 100%; clear: left;"><div class="CM_CTB_Content_Wrap" style="margin: 0px; padding: 0px;background-color: #ffffff;"><div style="border-bottom: solid 1px #dcdcdc; white-space: nowrap; margin-bottom: 8px; background-color: #eeeeee ;background-image: url(http://www.clipmarks.com/images/source-bg.gif); background-repeat: repeat-x; height: 24px; line-height: 24px; vertical-align: middle; padding-bottom: 4px; color: #666666; font-size: 10px;" ><a href="http://clipmarks.com/clip-to-blog/" title="see clips that are hot right now"><img src="http://content.clipmarks.com/blog_embed/6d94323b-fc61-4395-bef5-38d83a6f21d6/136843B7-6242-4D74-8840-71EF33D17F34/" alt="" width="19" height="19" border="0" style="vertical-align: middle; margin: 0px 4px; display: inline; border: none; float:none;" /></a>clipped from <a title="http://www.psoug.org/reference/roles.html" href="http://www.psoug.org/reference/roles.html" style="font-size: 11px;">www.psoug.org</a></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://www.psoug.org/reference/roles.html"><FONT size="2" face="Arial">Grant SELECT On All Tables In A Schema</FONT></blockquote><div style="height: 2px; font-size: 2px; background: #dcdcdc; border-bottom: solid 1px #f5f5f5; margin: 2px 4px;"></div><blockquote style="text-align: left; padding: 0px 8px; margin: 4px 0px 8px 0px; background: transparent; border: none;" cite="http://www.psoug.org/reference/roles.html"><table background="undefined" bgcolor="#d8d8c4"><tr><TD width="70%" bgcolor="%2380ffff"><FONT face="Courier"><DIV>CREATE OR REPLACE PROCEDURE GRANT_SELECT AS</DIV>
    
CURSOR ut_cur IS
SELECT table_name<BR /><DIV>
      FROM user_tables;</DIV>
    
RetVal  NUMBER;
sCursor INT;<BR /><DIV>
    sqlstr  VARCHAR2(250);</DIV>
    
BEGIN
FOR ut_rec IN user_tabs_cur;
LOOP
sqlstr := 'GRANT SELECT ON '|| ut_rec.table_name
|| ' TO <FONT color="%230000ff">jwc7675</FONT>';
sCursor := dbms_sql.open_cursor;<BR /><DIV>
          dbms_sql.parse(sCursor,sqlstr, dbms_sql.native);</DIV>
      
RetVal := dbms_sql.execute(sCursor); 
dbms_sql.close_cursor(sCursor);
END LOOP;
    
END grant_select;</FONT></TD></tr></table></blockquote></div><div style="margin: 0px 6px 6px 4px;"><table style="font-size: 11px;border-spacing: 0px;padding: 0px;" cellpadding="0" cellspacing="0" width="100%"><tr><td style="background:transparent;border-width:0px;padding:0px;">&nbsp;</td><td align="right" style="background:transparent;border-width:0px;padding:0px;width:107px" width="107"><a href="http://clipmarks.com/share/136843B7-6242-4D74-8840-71EF33D17F34/blog/" title="blog or email this clip"><img src="http://content6.clipmarks.com/images/c2b-foot.png" border="0" alt="blog it" width="107" height="17" style="border-width:0px;padding:0px;margin:0px;" /></a></td></tr></table></div></div>

New from the makers of Clipmarks: Amplify.com - Don't just share the news...Amplify it!

Clipmarks: Home; New Clips; Top Clips; Dashboard

Popular Topics: News; Life; Science; Technology; Entertainment

Get Started: Sign Up; Install Clipping Tool; How Clipping Works; Clip-to-Blog™; ClipSearch

Tools and Resources: FAQ; ClipWeek; Top Clippers; Top Tags; Site Map

About Clipmarks: About Us; Contact; Copyright; Privacy; EULA

Search Options

Today's Top Clips