{"id":15,"date":"2011-10-18T17:53:00","date_gmt":"2011-10-18T17:53:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=15"},"modified":"2026-02-07T15:29:25","modified_gmt":"2026-02-07T21:29:25","slug":"granting-privileges-on-all-or-multiple-objects-to-userrole","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/15\/","title":{"rendered":"Granting privileges on all or multiple objects to user\/role"},"content":{"rendered":"<div dir=\"ltr\" trbidi=\"on\"><b><u>Granting privileges on all or multiple objects to user\/role<\/u><\/b><\/p>\n<p><b><u>Question:<\/u><\/b><br \/>I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?<\/p>\n<p><b><u>Answer:<\/u><\/b><br \/>No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.<\/p>\n<p>If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.<\/p>\n<p><u>Create Role:<\/u><\/p>\n<p><span>CREATE ROLE XXVMX_UPDATE;<\/span><\/p>\n<p><u>Grant Role to Users:<\/u><\/p>\n<p><span>GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;<\/span><\/p>\n<p><u>SQL Script:<\/u><\/p>\n<p><span>set pages 0<\/span><br \/><span>set lines 300 trims on feedback on echo off<\/span><br \/><span><br \/><\/span><br \/><span>spool grants.sql<\/span><br \/><span><br \/><\/span><br \/><span>SELECT &#8216;grant select, update on &#8216; || owner ||&#8217;.&#8217;||table_name|| &#8216; to XXVMX_UPDATE;&#8217;<\/span><br \/><span>FROM dba_tables<\/span><br \/><span>WHERE owner = &#8216;XXVMX&#8217;;<\/span><br \/><span><br \/><\/span><br \/><span>spool off<\/span><br \/><span><br \/><\/span><br \/><span>set pages 99 lines 80<\/span><br \/><span>set feedback on echo on<\/span><\/p>\n<p>Execute the script file created to grant the privileges.<\/p>\n<p><span>@grants.sql<\/span><\/p>\n<p>You may replace the &#8220;<span>dba_tables<\/span>&#8221; in the query with &#8220;<span>dba_objects<\/span>&#8221; and change the <span>WHERE <\/span>clause appropriately to filter different sets of objects&#8230;<\/p>\n<p><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Granting privileges on all or multiple objects to user\/role Question:I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user? Answer:No such privilege in Oracle. You will have to write a &#8230; <a title=\"Granting privileges on all or multiple objects to user\/role\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/15\/\" aria-label=\"Read more about Granting privileges on all or multiple objects to user\/role\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[133],"tags":[],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack-related-posts":[{"id":14,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/14\/","url_meta":{"origin":15,"position":0},"title":"Script to Grant Privileges and Create Synonyms","author":"Biju","date":"October 24, 2011","format":false,"excerpt":"Extending the previous post\u00a0Granting privileges on all or multiple objects to user\/role\u00a0, here is another script.Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1473,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/1473\/","url_meta":{"origin":15,"position":1},"title":"Oracle 12c &#8211; READ Privilege","author":"Biju","date":"December 23, 2014","format":false,"excerpt":"The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or \u201cselecting\u201d from) the table, you are\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2210,"url":"https:\/\/bijoos.com\/oraclenotes\/2018\/2210\/","url_meta":{"origin":15,"position":2},"title":"Oracle Tidbits &#8211; July 2018 #oratidbit","author":"biju.thomas","date":"August 1, 2018","format":false,"excerpt":"\u201cOracle *daily* TidBits\u201d (#oratidbit) published on\u00a0\u00a0Facebook,\u00a0Twitter,\u00a0and\u00a0Google+\u00a0during weekdays\u00a0in July 2018. Hope you find these helpful to learn something new or to remind you of its existence and use. #oratidbit #db18c To change a regular user (password authenticated) to schema only account, the user should not have any administrative privileges (SYSDBA, SYSRAC,\u2026","rel":"","context":"In &quot;TidBits &amp; Quick Tips&quot;","block_context":{"text":"TidBits &amp; Quick Tips","link":"https:\/\/bijoos.com\/oraclenotes\/category\/tidbits\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2018\/04\/oratidbit.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":92,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/92\/","url_meta":{"origin":15,"position":3},"title":"OracleDB12c New Feature: Capture Privilege Usage","author":"Biju","date":"June 26, 2013","format":false,"excerpt":"One of the top features of the Oracle Database 12c that attracted me is capturing the privileges used (and unused). This feature is introduced to strengthen the security of the database to enforce the principle of least privilege. Many application users today have excessive privileges assigned, and believe me, have\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":236,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/236\/","url_meta":{"origin":15,"position":4},"title":"ASM User Management Q&#038;A","author":"Biju","date":"March 6, 2014","format":false,"excerpt":"Q: Can you create users in ASM instance? A: Yes, absolutely. Q: Why? A: For better security. Q: I was told there is no database associated with ASM, it is just an instance, so where is this user really created? A: You must have a password file to create users.\u2026","rel":"","context":"In &quot;Infrastructure &amp; Exadata&quot;","block_context":{"text":"Infrastructure &amp; Exadata","link":"https:\/\/bijoos.com\/oraclenotes\/category\/infrastructure-exadata\/"},"img":{"alt_text":"users","src":"https:\/\/i0.wp.com\/bijoos.com\/oraclenotes\/wp-content\/uploads\/2014\/03\/users-150x150.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":145,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/145\/","url_meta":{"origin":15,"position":5},"title":"OracleDB12c New Feature: Added Administrative Privileges","author":"Biju","date":"July 7, 2013","format":false,"excerpt":"Oracle11g Grid Infrastructure introduced SYSASM administrative privilege, in 12c we have 3 more administrative privileges... SYSBACKUP, SYSDG, SYSKM. SYSDBA, SYSOPER and SYSASM privileges and behavior are similar to 11gR2 in 12c database as well. SYSBACKUP administrative privilege has ability to perform RMAN backup and recovery operations, database startup and shutdown.\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":3003,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/15\/revisions\/3003"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}