How to grant all user object privilege

In oracle, each user/schema has some objects. They can be tables, views, procedures, functions or packages. By default a user can not access other objects owned by other user, unless after the owner gives that user some objects privileges.

Granting objects privileges to other user in oracle can by done one by one. If a user has 100 objects for example, he must run a script 100 times to give the whole object. But there are tricks he can do to overcome it.

To give permissions all objects owned by a schema, perform the following steps:

1. login as object owner
2. run script below
   

   begin
   for i in (select object_name from user_objects where object_type in ('TABLE','VIEW'))
   LOOP
   execute immediate 'grant select on '|| i.object_name||' to bb';
   end loop;
   end;
   /