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; ...
Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.