I need to create multiple users for one schema. All users should have same privileges. It seems that except the schema's owner user I have to grant the object privilegs on all objects to other users one by one. For example, I have to grant the privileges on each table to other users. If I have more than a thousand tables, I have to grant the privileges on tables to other users more than a thousand times.
I was wondering if there is any easy way to do that.
There is ROLE which is nothing but a user which holds all the privileges catrgorized based on the functionality of the system.
Say Clerk doesn't need to update anything, but to see. Supervisor needs to update.
Create 2 roles Clerk and Supervisor.
Grant select on xyz to clerk
grant update on xyz to supervisor.
These roles can be granted to any of the users who work as Clerks/Supervisors
In your context, Grant 1000 tables privileges to XYZ Role and Grant that XYZ role to all the Users of the system. Read the docs on Roles.