I have been working with Informix for just over 15 years. This has caused me to stop, pause and think about all the changes in the industry that have happened in that time. Back when I started, databases were still small. Databases were maintained to aid users performing one task or another. Today, they have become the very lifeblood of many companies. With databases becoming so important, security has also become a top priority.
Over the years, I have seen many databases that are not as secure as they should be. Some are still having users connect via the informix DBSA user account. Others have their tables configured with all permissions still open to public, and secure them by using only the database connect privilege. I have been asked many times why someone would do this and the answer is always the same, it’s quick and easy.
In early versions of Informix Dynamic Server,if you had utilized the table level permissions, a large number of table grants and revokes would need to be generated as users came and went. Many DBAs just didn’t want to deal with that. The short cut, utilizing roles, would help but required that the application activate the role during execution. When dealing with third party applications this usually was not possible or even optional.
As of Informix Dynamic Server version 11, you can now set a role as default for a user. This works much like Microsoft Windows groups where if you are a member of a group, you inherit all the permissions that group has. You can now just log in and automatically assume one role and you will be granted all permissions that that role has granted to it. So now the DBA can issue a single grant for the user rather than one for each and every table in the database:
grant default role {rolename} to {username};
It is just that easy to grant any specific individual access to all the tables they will need, depending upon the role that they are granted to be their default. When the user logs in, they will automatically have a “set role {rolename}” command issued in the background. That means that the application does not need to support the role syntax. You will still need to determine what table-level privileges are needed by each group and grant those privileges to the role(s), but that is only difficult the first time.
Going forward, it is imperative that DBAs take the time to issue appropriate table level privileges for their users. By utilizing roles, the extra security (and peace of mind) can be gained with only the few minutes that it will take to do this.