As I read, there are certain access privileges given to a user or role in Informix. If I give any of the database level privilege, it will have access to all users/ schemas under that database.
My doubt was that can I restrict this privilege to specific users/ schemas in that database? Do we have any user/schema level privilege in Informix?
If you grant a user CONNECT privilege to a database, then they can access tables within that database for which they have been granted access (which is typically, but not necessarily, all tables in the database). You can also revoke PUBLIC privileges on a table, and then grant access only to named users or roles, and then only those users who have been granted access can access it. Here, "access" means "if they try to SELECT, they need to have been granted SELECT access; if they try to INSERT, they need to have been granted INSERT access; etc.".
By default, when you create a table in an ordinary database, then all users are given select, insert, update, delete privileges on the table. This is done via the equivalent of GRANT ALL ON the_table TO PUBLIC. Note that every user has the permissions granted to PUBLIC, and possibly some extra privileges. Therefore, to ensure only selected users or roles have access, it is necessary to use REVOKE ALL ON the_table FROM PUBLIC. There is an environment variable NODEFDAC=1 and a $ONCONFIG file setting with the same name that suppresses the default 'discretionary access controls' (DAC) granted to PUBLIC. Note that in a MODE ANSI database, no permissions are granted to anyone else by default.
The constraints on users granted RESOURCE privilege in a database are similar users granted CONNECT privilege — the difference is that RESOURCE users can create their own tables, whereas CONNECT users cannot.
If a user is granted DBA privilege, they can access any tables in the database. Be cautious about granting DBA privilege, therefore.