John, you need to use the sys_tables as your source. SET VAR vTableName TEXT DROP CURSOR c1 DECLARE c1 CURSOR FOR SELECT sys_table_name FROM sys_tables OPEN c1 FETCH c1 INTO vTableName WHILE SQLCODE <> 100 THEN GRANT ALL PRIVILEGES ON &vTableName TO PUBLIC -- This next line will allow any user to create tables and views -- One of the maintenance items in our databases is a routine to -- drop and recreate all the views. GRANT CREATE is needed for this -- file to run properly. Leave it out or replace it with specific -- users in mind. GRANT CREATE TO PUBLIC FETCH c1 INTO vTableName ENDWHILE DROP CURSOR c1
-- Now selectively drop the permissions in code here -- If you need to allow users to create tables and views --- John Engwer <[EMAIL PROTECTED]> wrote: > Is there a way to grant access to all tables and views without having to > grant access a table at a time. There are many tables in this database so > I would like to grant privileges to all tables and then revoke/restrict > access to two or three tables/views that contain sensitive data. Currently > I have a table that contains all of the table/views names and I use a > declare cur and while loop to assign rights. That works OK but it is a pain > to keep the table/view list updated. During development I am always adding > and deleting tables/views from the DB. I am building most of my security > into the embedded code in forms but I still want to maintain an owner > ID/Password. > > > > Anyone have a better way of doing it. > > > > John > > ===== Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
