grant permissions on tables to users of your choice

				
--**************************************
-- Name: grant permissions on tables to users of your choice
-- Description:this code uses sql from sql to run lots of commands. 
the sql spools the required commands from the user_constraints ans user_tables data dictionary, it then prompts the user to input the user name of the person you want to grant permissions to, then runs the spool file, 
an easy way of running a lengthy task!
-- By: John Hunsley
--
-- Inputs:the user ID of the user you want to grant permissions of all your tables to.
--
-- Returns:access granted to user on all your tables
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=634&lngWId=5--for details.--**************************************

--*****************************************************************************
-- security.sql, sql from sql. these commands allow accesses to all tables*
-- by a selected user *
-- tables can be accessed by both the database constructor,*
-- and the selected user ONLY following the successful execution of this *
-- file. *
--*****************************************************************************
--*****************************************************************************
-- turn off echo, remove heading and feedback from the spooled file*
--*****************************************************************************
SET ECHO OFF
SET HEADING OFF
SET FEEDBACK OFF
SPOOL securitySpool.sql
--*****************************************************************************
-- select the appropreate commands from the data dictionary needed to disable *
-- all table constraints. *
--*****************************************************************************
SELECT 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM user_constraints
ORDER BY constraint_type DESC;
--*****************************************************************************
-- select the commands from the data dictionary needed to grant access on all *
-- tables to a selected user. *
--*****************************************************************************
SELECT 'GRANT SELECT ON '||table_name||' TO '||'&user'||';'
FROM user_tables
ORDER BY table_name DESC;
--****************************************************************************
-- select the commands needed to re-enable the constraints on all tables in *
-- the appropreate order *
--****************************************************************************
SELECT 'ALTER TABLE '||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM user_constraints
ORDER BY constraint_type ASC;
SPOOL OFF
SET ECHO ON
SET HEADING ON
SET FEEDBACK ON
--**************now run the spooled sql file**********************************
start securitySpool