CODEWITHSUNDEEP
sqloracle-databaseplsql
Angus
Angus

Reputation: 167

How to run repeated statement with different parameter in SQL

Using SQL (or PL/SQL) I'd like to do something like:

GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE, REFERENCES, RULE ON {mytable} to {userid}

but do this for n number of tables. In SAS I could create a macro and pass in the table name (and/or the userid) as a parameter. Can the same be done in SQL using a procedure?

Upvotes: 0

Views: 63

Answers (1)

Littlefoot
Littlefoot

Reputation: 142753

If you have list of tables stored in some other table (or, if it is about all tables in a schema), then you could create a procedure which would accept username as a parameter and grant those privileges on all those tables to that user.

For example (Oracle, which uses PL/SQL; as you didn't mention database you really use):

SQL> create or replace procedure p_grant (par_username in varchar2) is
  2  begin
  3    for cur_r in (select table_name
  4                  from user_tables
  5                  where table_name in ('EMP', 'DEPT', 'BONUS')
  6                 )
  7    loop
  8      dbms_output.put_Line('Grant on table: ' || cur_r.table_name);
  9      execute immediate 'grant select, insert, update, delete on ' || cur_r.table_name || ' to ' || par_username;
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL> set serveroutput on
SQL> begin
  2    p_grant('mike');
  3  end;
  4  /
Grant on table: BONUS
Grant on table: DEPT
Grant on table: EMP

PL/SQL procedure successfully completed.

SQL>

Upvotes: 3

Related Questions