Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Answered: QUIESCE db doesnt work

    I want to make one db unavailable but not drop it. In LUW 10.5.3

    this is what I did:
    Code:
    db2 connect to dbbn1
    db2 revoke dbadm on database from cadm
    db2 revoke QUIESCE_CONNECT on database from cadm
    
    db2 connect to dbbn1 user cadm      <----- suceed
    but I still can connect with CADM

    catsys.dbauth:

    Code:
     GRANTOR  	GRANTORTYPE 	GRANTEE  	GRANTEETYPE 	BINDADDAUTH 	CONNECTAUTH 	CREATETABAUTH 	DBADMAUTH 	EXTERNALROUTINEAUTH 	IMPLSCHEMAAUTH 	LOADAUTH 	NOFENCEAUTH 	QUIESCECONNECTAUTH 	LIBRARYADMAUTH 	SECURITYADMAUTH 	SQLADMAUTH 	WLMADMAUTH 	EXPLAINAUTH 	DATAACCESSAUTH 	ACCESSCTRLAUTH 	CREATESECUREAUTH
     -------- 	----------- 	-------- 	----------- 	----------- 	----------- 	------------- 	--------- 	------------------- 	-------------- 	-------- 	----------- 	------------------ 	-------------- 	--------------- 	---------- 	---------- 	----------- 	-------------- 	-------------- 	----------------
     SYSIBM  	S          	DB2INST1	U          	N          	N          	N            	Y        	N                  	N             	N       	N          	N                 	N             	Y              	N         	N         	N          	Y             	Y             	N
     SYSIBM  	S          	PUBLIC  	G          	Y          	N          	Y            	N        	N                  	Y             	N       	N          	N                 	N             	N              	N         	N         	N          	N             	N             	N
     DB2INST1	U          	CADM 	U          	N          	N          	N            	N        	N                  	N             	N       	N          	N                 	N             	N              	N         	N         	N          	Y             	Y             	N
    Last edited by AntiLopa; 11-30-15 at 01:28.

  2. Best Answer
    Posted by mark.b

    "Hi,

    what's the result of the following query?
    Code:
    select *
    from table(AUTH_LIST_AUTHORITIES_FOR_AUTHID('CADM', 'U'))
    where authority in ('SYSADM', 'SYSCTRL', 'SYSMAINT', 'DBADM', 'QUIESCE_CONNECT')
    order by authority
    "


  3. #2
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    In one place you are referring to coreadm and in another cadm. What groups does cadm belong to? i.e. is it in the sysadm, sysctrl or sysmaint group? If you want it unavailable but not dropped then you can also uncatalog it
    And did you actually do the quiesce?
    Last edited by tafster; 11-29-15 at 18:08.
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  4. #3
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    It was a typo error here.

    In any case, yes I tried the command but I still can connect using this user...
    Plus uncatalog works for me if I tried to connect from inside the server, but if I tried to connect using ibm data studio - it connects..

  5. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    what's the result of the following query?
    Code:
    select *
    from table(AUTH_LIST_AUTHORITIES_FOR_AUTHID('CADM', 'U'))
    where authority in ('SYSADM', 'SYSCTRL', 'SYSMAINT', 'DBADM', 'QUIESCE_CONNECT')
    order by authority
    Regards,
    Mark.

  6. #5
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    the output is:

    Code:
     AUTHORITY       	D_USER 	D_GROUP 	D_PUBLIC 	ROLE_USER 	ROLE_GROUP 	ROLE_PUBLIC 	D_ROLE
     --------------- 	------ 	------- 	-------- 	--------- 	---------- 	----------- 	------
     DBADM          	N     	N      	N       	N        	N         	N          	*
     QUIESCE_CONNECT	N     	N      	N       	N        	N         	N          	*
     SYSADM         	*     	Y      	*       	*        	*         	*          	*
     SYSCTRL        	*     	N      	*       	*        	*         	*          	*
     SYSMAINT       	*     	N      	*       	*        	*         	*          	*

  7. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Your user has SYSADM authority.
    You can achieve what you want if you exclude this user from the group with name returned by the following query:
    Code:
    select value from sysibmadm.dbmcfg where name='sysadm_group'
    Regards,
    Mark.

  8. #7
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    Can i do it in DB level?
    this OS user (with that group) is the only user for all the DBs on that instance (dev env..)

  9. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    No, you can't.
    You can't restrict a user having SYSMAINT, SYSCTRL or SYSADM authority from connecting to a quiesced database.
    Regards,
    Mark.

  10. #9
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    how can I revoke this from the group?
    i dont want to change the group of the user

  11. #10
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    You haven't read Mark's reply. You can't revoke quiesce connect from the sysadm group. You have to remove the user from the group. The sysadm group will be the primary group of the instance id and you don't want to change that. I presume that cadm is NOT the instance id.
    You also didn't read the notes in the knowledge center regarding the uncatalog database command re caching
    Last edited by tafster; 11-30-15 at 16:30.
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  12. #11
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    The only way is to set the dbm parameter SYSADM_GROUP to some another group name. But l'd strongly not recommend you to do this since it's not a good idea to set this parameter to something different from the instance owner's primary group name.
    If you really want to do this, you can try to stop the instance, place your instance owner to some different primary group, run the db2iupdt utility against this instance, update the dbm parameter mentioned above to the name of this group.
    But, please, try this on some test instance first, since I'm not sure if it will work.
    Regards,
    Mark.

  13. #12
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I agree with Mark and the underlying question is ..... why is this userid in the sysadm group to start with? If it's not an id to be used by DBA then it shouldn't be there - well that's my opinion anyway and our practice.
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  14. #13
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    the user is not the instance owner.
    i change the user group.
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •