Sams Teach Yourself SQL in 21 Days, Fourth Edition

Granting Privileges on Your Tables to Another User

Granting privileges on a table to another user is quite simple, as is selecting a row count on a table. But if you have multiple tables to which you wish to grant access to a role or user, you can make SQL generate a script for you—unless you just love to type.

First, review a simple GRANT to one table:

SQL> GRANT SELECT ON HISTORY TO BRANDON;

Grant succeeded.

Are you ready for some action? The next statement creates a GRANT statement for each of the 10 tables in your schema.

						SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SET HEADING OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT SELECT ON ' || TABLE_NAME || ' TO BRANDON;'
  2  FROM CAT
  3  / GRANT SELECT ON ACCT_PAY TO BRANDON; GRANT SELECT ON ACCT_REC ...