{"id":135,"date":"2010-05-12T14:53:14","date_gmt":"2010-05-12T18:53:14","guid":{"rendered":"http:\/\/dbtricks.com\/?p=135"},"modified":"2010-05-12T14:56:39","modified_gmt":"2010-05-12T18:56:39","slug":"how-to-grant-privileges-on-all-tables-to-a-user","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=135","title":{"rendered":"How to grant privileges on all tables to a user"},"content":{"rendered":"

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it.
\n
\nIt is important to note that the best way to do that is creating a role, granting the privileges to this role and then grant the role to the user. This way, you can fine tune the privileges and the process will be a lot faster next time.
\n
\nAnother important thing to note is that you should avoid the temptation to use\u00a0the \u00a0“SELECT ANY TABLE” privilege.\u00a0This\u00a0will\u00a0indeed allow the user to select any table but (and this is a big but) since it is a system privilege, it will allow him to select any table from any schema. Depends on the setting, it might even allow to user to select from the dictionary tables themselves. There is no reason to grant this privilege to\u00a0someone who is not the\u00a0DBA.
\n
\nThe easiest way to grant select on all of\u00a0 one account’s object to another user is to use a PL\/SQL loop. This script will grant select on tables but it should be easy to adapt it to any other privilege.
\n
\n\u00a0begin<\/strong><\/p>\n

\u00a0for\u00a0i in (select * from user_tables)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/strong><\/p>\n

\u00a0loop
\n\u00a0\u00a0\u00a0\u00a0 execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
\n\u00a0\u00a0 end loop;
\n\u00a0end;<\/strong>
\n
\nIn case you need a pure SQL solution, the best way to go would be to spool the results of the following query\u00a0and run the script created in grant-all.txt.
\n
\nSQL> set lin 150<\/strong><\/p>\n

SQL> spool c:\\grant-all.txt<\/strong><\/p>\n

SQL>select ‘grant select \u00a0to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;<\/strong><\/p>\n

SQL> spool off<\/strong>
<\/p>\n","protected":false},"excerpt":{"rendered":"

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it. It is important to note that the best way to do that is creating a role, granting […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[61,107,60,62],"class_list":["post-135","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-grant","tag-oracle","tag-oracle-grant-select","tag-select"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=135"}],"version-history":[{"count":8,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions"}],"predecessor-version":[{"id":141,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/revisions\/141"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}