Sams Teach Yourself SQL in 21 Days, Fourth Edition

Granting System Privileges to Multiple Users

As a database administrator or an individual responsible for maintaining users, you will often receive requests for user IDs. In addition to having to grant privileges to users that allow them proper database access, you also have to modify users' privileges to accommodate their changing needs. You can get the database to generate the GRANT statements to grant system privileges or roles to many users.

SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT CONNECT, RESOURCE TO ' || USERNAME || ';'
  2  FROM SYS.DBA_USERS
  3  WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','RYAN','PO7','DEMO')
  4  / GRANT CONNECT, RESOURCE TO KEVIN; GRANT CONNECT, RESOURCE TO JOHN; ...