{"id":14,"date":"2011-10-24T20:23:00","date_gmt":"2011-10-24T20:23:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=14"},"modified":"2026-02-07T15:29:24","modified_gmt":"2026-02-07T21:29:24","slug":"script-to-grant-privileges-and-create-synonyms","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/14\/","title":{"rendered":"Script to Grant Privileges and Create Synonyms"},"content":{"rendered":"
Extending the previous post\u00a0Granting privileges on all or multiple objects to user\/role<\/a><\/span><\/span>\u00a0, here is another script.<\/p>\n

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 the schema name where read privileges on the objects to be granted. It produces output to the screen and writes to file named tmpgrants.sql. Execute the tmpgrants.sql to grant SELECT privilege and to create a synonym under the read only account, so that the query account need not worry about qualifying the table with schema name.<\/schema><\/p>\n

First create the read only user account using similar syntax as below…<\/p>\n

create user tdmaa_query identified by tdm123q default tablespace users;<\/span>
grant create session to tdmaa_query;<\/span><\/p>\n

Be sure to save all the below lines to a file and run the file in SQL*Plus… Executing this script will produce a script file named tmpgrants.sql. Run that script to grant privileges and create synonyms.<\/p>\n

set pages 0 lines 200 trims on verify off feedback off<\/span>

<\/span>
accept grants_to \u00a0prompt ‘Enter user to grant privileges: ‘<\/span>
accept schema \u00a0 \u00a0 prompt ‘Enter schema on which to grant: ‘<\/span>

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

<\/span>
select ‘grant select on ‘||owner||’.’||table_name ||’ to &grants_to;’, chr(10),<\/span>
\u00a0 \u00a0 \u00a0 \u00a0‘create synonym &grants_to..’||table_name ||’ for ‘||owner||’.’||table_name||’;’, chr(10)<\/span>
from \u00a0dba_tables<\/span>
where owner = upper(‘&schema’)<\/span>
union all<\/span>
select ‘grant select on ‘||owner||’.’||view_name ||’ to &grants_to;’, chr(10),<\/span>
\u00a0 \u00a0 \u00a0 \u00a0‘create synonym &grants_to..’||view_name ||’ for ‘||owner||’.’||view_name||’;’, chr(10)<\/span>
from \u00a0dba_views<\/span>
where owner = upper(‘&schema’)<\/span>
;<\/span>

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

<\/span>
set pages 99 lines 80 verify on feedback on<\/span>

<\/span>
prompt “Run tmpgrants.sql if you are satistified with the script…”<\/span><\/p>\n

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

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 the schema name where … 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-14","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\/14","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=14"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/14\/revisions"}],"predecessor-version":[{"id":3002,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/14\/revisions\/3002"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=14"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=14"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}