how to give IP based grants in Informix
The poster asked whether Informix 12.10 supports MySQL-style IP-bound grants (e.g. GRANT SELECT TO user@192.168.x.x), motivated by a user sharing his password with colleagues. The consensus: no such syntax exists. Suggested workarounds were trusted contexts (11.70+), or checking the connecting IP/hostname in a sysdbopen() procedure against a table of allowed IP/login pairs and raising an exception (-746) to refuse unauthorized connections — with care to exempt informix/DBSA sessions so you don't lock yourself out. Others noted it's really an HR issue: change the password and hold the user accountable.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I want to grant IP based grants in informix?
e.g
grant select to username@192.168.X.X;
is there any way to bind grants with user name and IP.
Hi,
Why do you want to do this?
Which Informix version
The closest would be using trusted contexts which would need Informix 11.70 or
higher.
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id
s_sqs_2231.htm
Regards,
David.
> On 21 August 2017 at 14:45 MUNAWAR AHMED <mahmed04@i2cinc.com> wrote:
>
>
> Hi,
> I want to grant IP based grants in informix?
> e.g
> grant select to username@192.168.X.X;>
> is there any way to bind grants with user name and IP.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
No. However, you could use the sysdbopen function to set a role for the
user is based on the IO it connected from and grant private to that role.
But the user could change to another role he has permission to use manually.
Art
On Aug 21, 2017 08:46, "MUNAWAR AHMED" <mahmed04@i2cinc.com> wrote:
> Hi,
> I want to grant IP based grants in informix?
> e.g
> grant select to username@192.168.X.X;>
> is there any way to bind grants with user name and IP.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
informix version is 12.10. i want to bind grants with user IP. ( grant select to user@IP) because i assigned a role to a user and that user gave his password to other colleagues. now simultaneously, many users are login from same username, and As DBA it is difficult for me to track CPU utilization of users. so i am looking for a way to assign IP based grants in informix.
Hello, You can make special login for this user and in dbopen procedure restrict connection of this user from another IP. Best way - make it via special table with IP+login. Best regards, Arthur V.Sidorenko. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUNAWAR AHMED Sent: Tuesday, August 22, 2017 2:46 PM To: ids@iiug.org Subject: Re: how to give IP based grants in Informix [39727] informix version is 12.10. i want to bind grants with user IP. ( grant select to user@IP) because i assigned a role to a user and that user gave his password to other colleagues. now simultaneously, many users are login from same username, and As DBA it is difficult for me to track CPU utilization of users. so i am looking for a way to assign IP based grants in informix. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Again, not possible. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 22, 2017 at 6:45 AM, MUNAWAR AHMED <mahmed04@i2cinc.com> wrote: > informix version is 12.10. > i want to bind grants with user IP. ( grant select to user@IP) > because i assigned a role to a user and that user gave his password to > other > colleagues. now simultaneously, many users are login from same username, > and > As DBA it is difficult for me to track CPU utilization of users. > so i am looking for a way to assign IP based grants in informix. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
For some reason I don't the the OPs original posts. So I'm just answering Art's post and copying the OP (just in case the other way also has issues)... I believe this is not a GRANT issue. First your company should approach the password sharing in a much more serious way... Both the user who shared the password and the ones that use it should be warn and made accountable for such behavior, and if needed more serious measures should be taken... But assuming this is out of your responsibility scope.... You don't need GRANTs based on IP (or you would just need CONNECT based on IP). But considering it's not available (nor in other RDBMS I believe) what you need it to prevent connect from non authorized IPs. And there was another answer that points the correct direction. Validate the pair IP/Login in the sysdbopen() procedure. If the IP/Hostname is not allowed for the user, than just raise and error (RAISE EXCEPTION -746 .... ) and and error in the sysdbopen() procedure will refuse the connection. Make sure you test it properly and exempt some logins from the check (informix) and make sure you check the name of the variable that prevents sysdbopen() from being applied to your (DBSA) sessions (or you could risk refusing any connection, and then you wouldn't be able to change the procedure) Regards On Tue, Aug 22, 2017 at 2:21 PM, Art Kagel <art.kagel@gmail.com> wrote: > Again, not possible. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Tue, Aug 22, 2017 at 6:45 AM, MUNAWAR AHMED <mahmed04@i2cinc.com> > wrote: > > > informix version is 12.10. > > i want to bind grants with user IP. ( grant select to user@IP) > > because i assigned a role to a user and that user gave his password to > > other > > colleagues. now simultaneously, many users are login from same username, > > and > > As DBA it is difficult for me to track CPU utilization of users. > > so i am looking for a way to assign IP based grants in informix. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Change the password. Threaten the correct user if he gives the new password away. Dont try and use technology to solve HR issues. Clive > On 22 Aug 2017, at 12:45, MUNAWAR AHMED <mahmed04@i2cinc.com> wrote: > > informix version is 12.10. > i want to bind grants with user IP. ( grant select to user@IP) > because i assigned a role to a user and that user gave his password to other > colleagues. now simultaneously, many users are login from same username, and > As DBA it is difficult for me to track CPU utilization of users. > so i am looking for a way to assign IP based grants in informix. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >