Command to drop orphaned user

mdx34 · #1 (**permalink**) 03-26-07, 10:09

What is the command to delete/drop a database user from a database ? I know I can delete them from the GUI but is there a command to delete them from a command line ?

AIX 5.2 DB2 8.2 FP3

Thanks.

ARWinner · #2 (**permalink**) 03-26-07, 13:31

What do you mean delete/drop a user from a database? Do you mean break their connection? Or do you mean revoke the user from total access to the DB (i.e. delete their username/password)?

Andy

mdx34 · #3 (**permalink**) 03-26-07, 14:12

I have backed-up a database on server1 and restored it to server2. The user that belongs to the server1 is still in the database even though it doesn't have access to the database anymore on the server2 since the same O/S user doesn't exist on the server2 (The user now is orphaned).

How can I remove/delete this user using command line ?

ARWinner · #4 (**permalink**) 03-26-07, 14:25

It depends on what that user is defined through GRANT to be able to do. You will need to execute 1 or more REVOKE commands based on what the user has right on. This is what the GUI is doing.

Andy

mdx34 · #5 (**permalink**) 03-26-07, 14:55

May be I am not making myself clear. If you have access to GUI, you can open the Control Center and drill down to the "Users and Group objects" then select "DB Users" on a database. On the right of the page you have the database users listed under the "Name". Then, you can just right click on the username and choose "Remove" which removes the user from the database.

Is there a command that would do the remove part through the command line ?. The database user is not the db2 instance owner but any other user.

ARWinner · #6 (**permalink**) 03-26-07, 15:17

I understand what you were doing in the GUI. I did the same thing, but it does not have an option to "View SQL" like the GUI does in a lot of other places. So I have a monitor tool that I used to monitor the dynamic SQL. The GUI actually checks what rights the user has been granted and then revokes them with as many revoke statements as necessary.

Andy