Re: system privilege granting

You have to grant privileges on each table, one at a time. It sounds like what you want is a way around doing this manually. Assuming that you want to grant the same set of privs on each table, you can do something like the following:

1. first create a role so that you don't have to grant to each specific user.

create role test_user;

2. Use the SQL*Plus SPOOL command to generate a file of grant statements:

	SPOOL C:\TEST_GRANTS.SQL
	SELECT 'Grant select,insert,update,delete on ' 
		|| table_name || 'to test_user;'
	FROM all_tables
	WHERE owner = 'TEST';
	SPOOL OFF

3. Now the TEST_GRANTS.SQL file will contain a large number of grant commands. Execute that file in order to execute all the grants:

        @c:\test_grants.sql

4. Grant the role test_user to any user needing access to the TEST schema's tables.

	grant test_user to scott;
	grant test_user to jonathan;
	etc...

You need to use SQL*Plus for the above, because it has the spool command. You probably will need to log in as system to create the test_user role, and also again to grant that role to your users. You need to log in as TEST when you are actually executing the test_grants.sql script.

Hope this helps.

Jonathan Gennick

On Sat, 17 Oct 1998 23:29:09 -0600, Brian Yan <by2_at_gpu.srv.ualberta.ca> wrote:

>I have a test schema called TEST on the database, which contains
>thousands of objects. In the same database, there are some test users as
>well. I want to grant the necessary privilege so that the test users can
>select, update, delete tables. I understant that I can achieve this by
>granting SELECT(UPDATE, DELETE) ANY TABLE to users. However, this would
>allow users to manupulate the table in any schemas. My question is: Is
>there any method that can grant these privelege to specific schema? For
>example: GRANT SELECT ANY TABLE ON schema_name TO user1?
>
>Thank you for your help!
>
>Brian
Received on Sun Oct 18 1998 - 09:08:23 CDT