deny access to one of two databases in same instance?
sumGirl wanted to keep users out of one of two databases in the same instance during weekend maintenance. Jonathan Leffler suggested opening it exclusively with the database ... exclusive statement in a dbaccess session, or revoking connect, resource and dba rights (keeping a record to restore them, after verifying table-level rights survive). Neil Truby suggested renaming the database. sumGirl thanked them.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi all. I have a server that has a single instance of informix but houses two production databases. I have a bunch of maintenance to do on one of the databases this weekend that will not affect the other database in any way. I am wondering if theres a good way or even a hack that would allow me to keep everyone out of one database but allow them in the other?
sumGirl wrote:
> Hi all. I have a server that has a single instance of informix but
> houses two production databases. I have a bunch of maintenance to do
> on one of the databases this weekend that will not affect the other
> database in any way. I am wondering if theres a good way or even a
> hack that would allow me to keep everyone out of one database but
> allow them in the other?
Presumably, you've considered:
dbaccess - - <<!
database locked_out exclusive;
...the rest of your work...
!
And rejected it.
You can also do:
dbaccess locked_out - <<!
revoke connect from public;
revoke dba from [...list of DBAs...];
revoke resource from [...list of ex-DBAs and Resourceful users...]
revoke connect from [...list of ex-DBAs and ex-Resourceful users andconnectable users...]
!
You'd have to keep records of who had which permission, of course.
AFAICR, revoking access rights like this does not damage the
table-level access rights, so when you reinstate the previous rights,
once you're done, you're home. You should verify that first, of course.
It isn't ideal - but it's about the best I can come up with on the
spur of the moment.
Even a single-user admin mode would keep people out of the instance
rather than out of the database (singular).
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Or how about renaming the database you want to be locked out for the
duration of your work?
"Jonathan Leffler" <jleffler@earthlink.net> wrote in message
news:s9B1d.4$0i5.0@newsread3.news.pas.earthlink.net...
> sumGirl wrote:
>
> > Hi all. I have a server that has a single instance of informix but
> > houses two production databases. I have a bunch of maintenance to do
> > on one of the databases this weekend that will not affect the other
> > database in any way. I am wondering if theres a good way or even a
> > hack that would allow me to keep everyone out of one database but
> > allow them in the other?
>
> Presumably, you've considered:
>
> dbaccess - - <<!
> database locked_out exclusive;
> ...the rest of your work...
> !
>
> And rejected it.
>
> You can also do:
> dbaccess locked_out - <<!
> revoke connect from public;
> revoke dba from [...list of DBAs...];
> revoke resource from [...list of ex-DBAs and Resourceful users...]
> revoke connect from [...list of ex-DBAs and ex-Resourceful users and> connectable users...]
> !
>
> You'd have to keep records of who had which permission, of course.
> AFAICR, revoking access rights like this does not damage the
> table-level access rights, so when you reinstate the previous rights,
> once you're done, you're home. You should verify that first, of course.
>
> It isn't ideal - but it's about the best I can come up with on the
> spur of the moment.
>
> Even a single-user admin mode would keep people out of the instance
> rather than out of the database (singular).
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
More than I had hoped for, thanks Jonathan!
\\Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<s9B1d.4$0i5.0@newsread3.news.pas.earthlink.net>...
> sumGirl wrote:
>
> > Hi all. I have a server that has a single instance of informix but
> > houses two production databases. I have a bunch of maintenance to do
> > on one of the databases this weekend that will not affect the other
> > database in any way. I am wondering if theres a good way or even a
> > hack that would allow me to keep everyone out of one database but
> > allow them in the other?
>
> Presumably, you've considered:
>
> dbaccess - - <<!
> database locked_out exclusive;
> ...the rest of your work...
> !
>
> And rejected it.
>
> You can also do:
> dbaccess locked_out - <<!
> revoke connect from public;
> revoke dba from [...list of DBAs...];
> revoke resource from [...list of ex-DBAs and Resourceful users...]
> revoke connect from [...list of ex-DBAs and ex-Resourceful users and> connectable users...]
> !
>
> You'd have to keep records of who had which permission, of course.
> AFAICR, revoking access rights like this does not damage the
> table-level access rights, so when you reinstate the previous rights,
> once you're done, you're home. You should verify that first, of course.
>
> It isn't ideal - but it's about the best I can come up with on the
> spur of the moment.
>
> Even a single-user admin mode would keep people out of the instance
> rather than out of the database (singular).