Re: Getting 'Insufficient Privileges' error. Why?

On 13-Jan-98 02:04:09 Joanne James wrote:
>Hello out there! (I can almost hear the echo... :). I'm having difficulty
>in using the 'system' user to assign insert, update, etc. privileges on the
>tables I have created. The 'system' user does have the privileges provided
>automatically, including the privilege of being able to perform grants for
>privileges and roles. However, I get the ORA-01031 Insufficient Privileges
>error when attempting to grant insert, update, etc privileges on any of the
>tables. Any clues???? I have tried looking at the help for this error
>message and it doesn't seem to apply to my situation. Thanks for any help!

The user 'system' does not have select privilege on every table created. He has a system privilege called 'select any table' which allows him to, well, select from any table.
So the system user cannot grant select on tables he does not own, because he does not have the select privilege himself. You should grant select using the owner who owns the tables. Perhaps you should consider defining a role that has select privileges on the tables. So you only have to grant select etc. to the role and not to every user. Well you have to grant the role to the users but if you want to revoke select from every user, you could simply do

revoke select from my_role;

We use the following way to handle such things:

First create a user which owns all the tables. E.g. call him db_owner. Then create a role called db_user.
Wenever you need a new table, you connect as db_owner and do

create table new_table (...);
grant all on new_table to db_user;
create public synonym new_table for new_table;

Whenever you create a new user that has to get access to the tables you just do

grant db_user to new_user;

The user then can do

select * from new_tabe where ...;

That means that he needs not know the name of the db_owner. The public synonym makes it possible.

Hope that helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |