Creating users in Informix

On Jun 26, 2:30 pm, Naeem wrote:
> I need to create a new user in IBM Informix (Unix platform) and give
> him access to only temp1 table but not to temp2 table. The problem is
> that there are some existing users who have access to both tables
> through Public role. If I explicitly grant access to new user on temp1
> and revoke the access privileges on temp2 from Public role, existing
> users will loose access to the table resulting in big problem, as the
> database is real-time. Is there any way to create a user who doesn't
> belong to Public group? How can I know which users have access to
> which tables, through Public role, so that I first could revoke
> privileges from Public and then explicitly grant these to the users.
> Any other solution, please suggest.....

Fix:
Step 1: GRANT access privs on temp1 and temp2 to each user who needs
it.
Step 2: REVOKE privs from PUBLIC

If you have IDS 9.30 or later you can create ROLES for the users that
need access to temp2 and for those that do not.
Grant access to the tables to the appropriate ROLES. REVOKE all privs
from PUBLIC. Add the appropriate users to each role as needed. Have
users who are privileged to see temp2 set that role at startup.

In 9.40 you can define a default ROLE.

When you move up to 11.10 you can define a default ROLE and can have a
'connect' procedure for each user that automatically sets his roll at
connect time.

Art S. Kagel

>I need to create a new user in IBM Informix (Unix platform) and give
>him access to only temp1 table but not to temp2 table.

>The problem is
>that there are some existing users who have access to both tables
>through Public role.

So security was not previously a concern but is now? Are you
absolutely sure that all the users accessing this table are allowed to
have access to this table? do you have an explicit list of the users
that SHOULD be allowed to this table?

>If I explicitly grant access to new user on temp1
>and revoke the access privileges on temp2 from Public role, existing
>users will loose access to the table resulting in big problem, as the
>database is real-time.

Or do you have a bigger security problem? Do you ahve unauthorised
users having access to information they shouldn't? What CONNECT
privilegdges do you have on the database? just the users that are
allowed acces or again is it public.


>Is there any way to create a user who doesn't
>belong to Public group?

No

How can I know which users have access to
which tables, through Public role, so that I first could revoke
privileges from Public and then explicitly grant these to the users.

ALL users have access to the table through PUBLIC role, so if they
have CONNECt priviledge they have access to the tables.

Can you see who has connect priviledge or is it just PUBLIC? If it is
just PUBLIC you may have bigger access control problem than you
realise!

>Any other solution, please suggest.....

onstat -u will show you which users are connecting to the instance

onstat -g ses or onstat -g sql will show you which users ae connecting
to which databases which should give you an idea who is using the
database and therefore who has access..