0

I am attempting to write an sql command into an sql file so that the output itself can be used as a script. Basically using a script to create output that is itself formatted into runnable script. Maybe it's redundant but I haven't been able to think of a better way. I could use an extra set of eyes (or ideas) if possible. Thanks

Here's the script:

CONNECT &&master_user/&&master_pwd.@&&tns_alias
SET LINESIZE 132 PAGESIZE 0 ECHO OFF FEEDBACK OFF
SET VERIFY OFF HEAD OFF TERM OFF TRIMSPOOL ON
SPO syns_list.sql
--GRANT CREATE ANY SYNONYM TO &&syn_user;//how is this line formed?
select  'create or replace synonym ' || table_name ||
' for ' || '&&master_user..' ||
table_name || ';'
from user_tables
order by table_name asc;
--REVOKE CREATE ANY SYNONYM FROM &&syn_user;//how is this line formed?
SPO OFF;
SET ECHO ON FEEDBACK ON VERIFY ON HEAD ON TERM ON;
exit

This is the output:

//need the "grant" line here
create or replace synonym AGENTS for webdemo_admin.AGENTS;
create or replace synonym CONSTRUCTION_COMPANY for webdemo_admin.CONSTRUCTION_COMPANY;
create or replace synonym CONTRACT for webdemo_admin.CONTRACT;
create or replace synonym CUSTOMERS for webdemo_admin.CUSTOMERS;
create or replace synonym CUSTOMER_INTEREST for webdemo_admin.CUSTOMER_INTEREST;
create or replace synonym FEATURE for webdemo_admin.FEATURE;
create or replace synonym HOME for webdemo_admin.HOME;
create or replace synonym HOME_NONSTD_FEATURE for webdemo_admin.HOME_NONSTD_FEATURE;
create or replace synonym INTEREST for webdemo_admin.INTEREST;
create or replace synonym NON_STD_FEATURE for webdemo_admin.NON_STD_FEATURE;
create or replace synonym SALES for webdemo_admin.SALES;
create or replace synonym STD_FEATURE for webdemo_admin.STD_FEATURE;
create or replace synonym STD_MODEL for webdemo_admin.STD_MODEL;
create or replace synonym STD_MODEL_FEATURE for webdemo_admin.STD_MODEL_FEATURE;
create or replace synonym SUB_CONTRACTOR for webdemo_admin.SUB_CONTRACTOR;
create or replace synonym WARRANTY_WORK for webdemo_admin.WARRANTY_WORK;
//need the "revoke" line here

I am sure there are better ways to do this, I'm still picking things up as I go. As fo an explanation (for understanding): I want that I maintain a user with full control over the tables (and the schema) but I need a user (or upto 10 users) with the above-listed private synonyms in order for multiple connections to have the same type of access that a real-estate agent might (when viewing this from the perspective of a web app). That is, the synonyms allow for select,insert, update and delete transactions. The reason for the grant/revoke before and after the synonym creation is only to automate the process and remove the security problem of granting the create any synonym to a normal user. Further, I do not want the synonyms to be public as that is also a headache to keep track of (and again a security problem). In any case, private is better and offers more control.

Again, I appreciate any suggestions anyone may have.

Improve this question

2 Answers 2

Reset to default
2

Is the SQL*Plus PROMPT keyword what you're after? This just displays the rest of the line:

SQL> DEFINE syn_user = exampleuser1234
SQL> PROMPT GRANT CREATE ANY SYNONYM TO &&syn_user;;
GRANT CREATE ANY SYNONYM TO exampleuser1234;
SQL>

Note that we must double the semicolon since SQL*Plus interprets the first one as the end of the substitution parameter syn_user.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

that did it. "PROMPT" was the keyword I was missing. Works great now. Thanks!
1

If you're going to run the generated script as the synonym owner, which appears to be the case, it shouldn't need CREATE ANY SYNONYM privilege, just CREATE SYNONYM privilege. I would just grant that privilege when creating the user.

To do a grant in this script, you'd need to first connect as a privileged user, such as SYSTEM. In that case, you might as well run the whole things as SYSTEM, which already has CREATE ANY SYNONYM. To do that you would just need to modify your create statements to include the owner of the synonym, e.g. create or replace synonym <syn_user>.STD_MODEL_FEATURE for webdemo_admin.STD_MODEL_FEATURE; (where <syn_user> is the value of the &syn_user substitution variable).

(To clarify, those are two different ways of approaching it. Pick one or the other, not both.)

Improve this answer

3 Comments

Went with: `select 'create or replace synonym &&syn_user..' || table_name || ' for ' || '&&master_user..' || table_name || ';' from user_tables order by. What threw meoff was that there was no prompt for the syn_user variable. Thanks!
To clarify, the prompt for the variable winds up in the created script file.
@Jinx3y -- Sorry, I didn't mean that you should generate the script with the substitution variable in it. I was just showing where it would go in the generated command. I'll try to improve it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.