As the database administrator, you provide the support that users need to gain access to your DB2 Server for VSE & VM application server and the data it manages.
This chapter describes the tasks involved in supporting new users, and removing the data and access of those who have left.
To a DB2 Server for VSE & VM Application Server:
The following are the steps involved in adding new users to DB2 Server for VSE & VM application servers.
New users need CONNECT authority on the application server (alternatively, this may be granted to ALLUSERS). To add a new user to the system, perform the following tasks:
To a Non-DB2 Server for VSE & VM Application Server:
To enable a user to access a non-DB2 Server for VSE & VM application server perform the following tasks:
Notes:
Note: A new user ID and password may be required at some of the remote application servers, depending on the LU 6.2 security level that is required for the connection. See the Distributed Relational Database Connectivity Guide manual.
|In VSE, set up a new entry in the DBNAME Directory for the remote |application server (if it has not already been done).
To set up a new ISQL user to access the resources of an application server, run the SQLDBA.ARINEWUS routine supplied by IBM and previously loaded into the SQLDBA.ROUTINE table during database generation.
Start ISQL and connect as SQLDBA (or some other user ID with DBA authority), then type:
RUN SQLDBA.ARINEWUS (newuser newuserpw)
For newuser, specify:
For newuserpw, specify a password for the new user.
The ARINEWUS routine does the following:
NAME SEQNO COMMAND REMARKS ------- ----- -------------- ------- PROFILE 10 SET VARCHAR 35 NULL PROFILE 20 SET CASE UPPER NULL
When you run ARINEWUS, you will be prompted to enter either COMMIT or ROLLBACK. If no errors occurred, enter COMMIT; otherwise, enter ROLLBACK.
The ARINEWUS routine sets up the new user only in the application server that you are connected to when you invoke the routine. If a user is to have access to more than one application server, connect to these other application servers and run ARINEWUS again for each one.
A new user with a user ID of ALEX and a password of ALEXPW is defined. Alex does application development work and needs access to two application servers: PROD and TEST.
Do the following in a VM system:
CONNECT SQLDBA IDENTIFIED BY sqldbapw
Assume you know the password of the SQLDBA user ID for both application servers
RUN SQLDBA.ARINEWUS (ALEX ALEXPW)
CONNECT SQLDBA IDENTIFIED BY sqldbapw TO TEST
RUN SQLDBA.ARINEWUS (ALEX ALEXPW)
Do the following in a VSE system:
CONNECT SQLDBA IDENTIFIED BY sqldbapw TO PROD
Assume you know the password of the SQLDBA user ID for both application servers
RUN SQLDBA.ARINEWUS (ALEX ALEXPW)
CONNECT SQLDBA IDENTIFIED BY sqldbapw TO TEST
RUN SQLDBA.ARINEWUS (ALEX ALEXPW)
Alex is now set up to use both application servers.
If you want to review the contents of the ARINEWUS routine before you invoke it, issue the following SELECT statement on either application server:
SELECT COMMAND FROM SQLDBA.ROUTINE WHERE NAME = 'ARINEWUS'
Once you have run ARINEWUS, your new user has CONNECT authority to the application server. This is the lowest level of authority. To decide if this is the appropriate level for this user, and to change it if not, see Chapter 5, Providing Security.
After providing new users with CONNECT authority, you can do any of the following:
Before VM users can access an application server, a default application server needs to be established. Users must process the SQLINIT EXEC to specify the application server they intend to access. For example, if the user intends to access the TEST application server, he or she must enter:
SQLINIT DBNAME(TEST)
Users only need to re-process the SQLINIT EXEC if they want to explicitly change the current SQLINIT options. The most current SQLINIT information is stored on each user's A-disk. For more information, see SQLINIT EXEC.
New users likely have existing files of data that they want to store in the database. If the files are short, the data they contain can be typed in at the terminal using ISQL statements. This method, however, is not suitable for large files. Here, you can use the DBS Utility to transfer data into a database. For information on how to use the DBS Utility, see Loading Data into Tables.
It is your responsibility to assist new users with the DB2 Server for VSE & VM database manager, and to deal with their questions and problems. Ensuring new users are adequately trained will reduce your problem-solving duties.
When users leave your area, both their access to the application server and any unwanted data should be removed. You should try to get people to remove their own data before they leave; however, you will often have to do so yourself.
The following steps describe how to remove a user's access to an application server. If a user was using multiple application servers, you must perform this process for each server. You must have DBA authority to perform these steps.
If you have DBA authority, you can revoke a user's authority to access the application server at any time by issuing the REVOKE CONNECT statement listing the user(s) affected.
REVOKE CONNECT FROM JOHN,KAREN,ALICE
Revoking a user's CONNECT authority prevents that user ID from accessing the application server. This action only removes the user IDs from the SYSTEM.SYSUSERAUTH catalog table; it does not affect any objects (for example, tables) in the database which those users may have created, nor does it affect any privileges that may have been granted to them.
An employee whose user ID was SMITH has left the company. To remove SMITH's database objects, do the following:
SELECT DBSPACENAME FROM SYSTEM.SYSDBSPACES
WHERE OWNER='SMITH'
SELECT TNAME,DBSPACENAME FROM SYSTEM.SYSCATALOG
WHERE CREATOR='SMITH'
AND TABLETYPE='R'
This command displays the names of the tables that SMITH created, and the dbspaces where they were created. The TABLETYPE='R' (R stands for real table) indicates that you want to see only the tables at this point; you do not yet want to see any views that SMITH defined. Record those tables that are in PUBLIC dbspaces for later use in step 8.
SELECT TNAME, TCREATOR, REFTNAME, REFTCREATOR FROM SYSTEM.SYSKEYS WHERE (TCREATOR ¬= 'SMITH' AND REFTCREATOR = 'SMITH') OR (TCREATOR = 'SMITH' AND REFTCREATOR ¬= 'SMITH')
This command displays tables created by others that reference tables created by SMITH, as well as tables created by SMITH that reference tables created by others. Make note of the tables you want to save.
For each PRIVATE dbspace owned by SMITH, type:
SELECT TNAME,CREATOR FROM SYSTEM.SYSCATALOG
WHERE CREATOR¬='SMITH'
AND DBSPACENAME='dbspacename'
AND TABLETYPE='R'
This command lists the names of all tables in dbspacename that SMITH did not create, along with the names of who created them. The TABLETYPE='R' (R stands for real table) indicates that you want to see only the tables at this point, not views.
There are many ways to transfer (copy) tables to another dbspace. One way is to first create a new table with the same format in a different dbspace; then use an INSERT with Subselect statement to retrieve data from the original table and insert it into the new table.
There are more sophisticated techniques available using the DBS Utility. For information, refer to Maintaining Tables or to the DB2 Server for VSE & VM Database Services Utility manual.
DROP DBSPACE SMITH.dbspacename
DROP TABLE SMITH.tablename
SELECT VIEWNAME FROM SYSTEM.SYSVIEWS
WHERE VCREATOR='SMITH'
DROP VIEW SMITH.viewname
SELECT INAME FROM SYSTEM.SYSINDEXES
WHERE ICREATOR='SMITH'
DROP INDEX SMITH.indexname
SELECT TNAME FROM SYSTEM.SYSACCESS
WHERE CREATOR='SMITH'
DROP PACKAGE SMITH.packagename
DELETE FROM SYSTEM.SYSSYNONYMS
WHERE USERID='SMITH'
To determine these queries, type:
SELECT STMTNAME FROM SQLDBA."STORED QUERIES"
WHERE CREATOR='SMITH'
Then issue a single DELETE statement:
DELETE FROM SQLDBA."STORED QUERIES"
WHERE CREATOR='SMITH'
It is helpful if departing employees remove their own data from the database. Only someone with DBA authority can delete stored queries in the above manner; others use the ISQL ERASE command. For example, to delete a stored query called MYQUERY, SMITH would start ISQL and type:
ERASE MYQUERY
SELECT * FROM SYSTEM.SYSTABAUTH
WHERE GRANTEE='SMITH'
SELECT * FROM SYSTEM.SYSPROGAUTH
WHERE GRANTEE='SMITH'
Contact these users and have them revoke all of SMITH's privileges. Or, if a user is not available, you can explicitly connect with his or her password to revoke them yourself.
If SMITH's VM directory contains IUCV entries or the MAXCONN OPTION for the database resources, these entries should be removed, as well as access to the 195 production disk.
If SMITH accessed a DB2 Server for VM application server from a VSE guest, and used the CICS system, you should remove the transaction IDs used by SMITH in the CICS system. For more information on transaction IDs, see the DB2 Server for VM System Administration manual.