{"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":"
Granting privileges on all or multiple objects to user\/role<\/u><\/b><\/p>\n

Question:<\/u><\/b>
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

Answer:<\/u><\/b>
No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.<\/p>\n

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

Create Role:<\/u><\/p>\n

CREATE ROLE XXVMX_UPDATE;<\/span><\/p>\n

Grant Role to Users:<\/u><\/p>\n

GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;<\/span><\/p>\n

SQL Script:<\/u><\/p>\n

set pages 0<\/span>
set lines 300 trims on feedback on echo off<\/span>

<\/span>
spool grants.sql<\/span>

<\/span>
SELECT ‘grant select, update on ‘ || owner ||’.’||table_name|| ‘ to XXVMX_UPDATE;’<\/span>
FROM dba_tables<\/span>
WHERE owner = ‘XXVMX’;<\/span>

<\/span>
spool off<\/span>

<\/span>
set pages 99 lines 80<\/span>
set feedback on echo on<\/span><\/p>\n

Execute the script file created to grant the privileges.<\/p>\n

@grants.sql<\/span><\/p>\n

You may replace the “dba_tables<\/span>” in the query with “dba_objects<\/span>” and change the WHERE <\/span>clause appropriately to filter different sets of objects…<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

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 … 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_post_was_ever_published":false,"_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":[133],"tags":[],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_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}]}}