Authorities and Privileges Needed to Perform Common Tasks

So far, we have identified the authorities and privileges that are available, and we have examined how these authorities and privileges are granted and revoked. But to use authorities and privileges effectively, you must be able to determine which authorities and privileges are appropriate for an individual user and which are not. Often, a blanket set of authorities and privileges are assigned to an individual, based on his or her job title and/or job responsibilities. Then, as the individual begins to work with the database, the set of authorities and privileges he or she has is modified as appropriate. Some of the more common job titles used, along with the tasks that usually accompany them and the authorities/privileges needed to perform those tasks, can be seen in Table 3-2.

Table 3-2: Common Job Titles, Tasks, and Authorities/Privileges Needed
Open table as spreadsheet

Job Title	Tasks	Authorities/Privileges Needed
Department Administrator	Oversees the departmental system; designs and creates databases	System Control (SYSCTRL) authority or System Administrator (SYSADM) authority (if the department has its own instance)
Security Administrator	Grants authorities and privileges to other users and revokes them, if necessary	System Administrator (SYSADM) authority or Database Administrator (DBADM) authority (Security Administrator [SECADM] authority if label-based access control is used)
Database Administrator	Designs, develops, operates, safeguards, and maintains one or more databases	Database Administrator (DBADM) authority over one or more databases and System Maintenance (SYSMAINT) authority, or in some cases System Control (SYSCTRL) authority, over the instance(s) that control the databases
System Operator	Monitors the database and performs routine backup operations. Also performs recovery operations if needed	System Maintenance (SYSMAINT) authority or System Monitor (SYSMON) authority
Application Developer/Programmer	Develops and tests database/DB2 Database Manager application programs; may also create test tables and populate them with data	CONNECT and CREATE_TAB privilege for one or more databases, BINDADD and BIND privilege on one or more packages, one or more schema privileges for one or more schemas, and one or more table privileges for one or more tables; CREATE_EXTERNAL_ROU-TINE privilege for one or more databases may also be required
User Analyst	Defines the data requirements for an application program by examining the database structure using the system catalog views	CONNECT privilege for one or more databases and SELECT privilege on the system catalog views
End User	Executes one or more application programs	CONNECT privilege for one or more databases and EXECUTE privilege on the package associated with each application used; if an application program contains dynamic SQL statements, SELECT, INSERT, UPDATE and DELETE privileges for one or more tables may be needed as well
Information Center Consultant	Defines the data requirements for a query user; provides the data needed by creating tables and views and by granting access to one or more database objects	Database Administrator (DBADM) authority for one or more databases
Query User	Issues SQL statements (usually from the Command Line Processor) to retrieve, add, update, or delete data (may also save results of queries in tables)	CONNECT privilege on one or more databases, SELECT, INSERT, UPDATE, and DELETE privilege on each table used, and CREATEIN privilege on the schema in which tables and views are to be created
Adapted from Table 78 on pages 608–609 of the IBM DB2 Version 9 for Linux, UNIX, and Windows Administration Guide-Implementation Manual.