- Controlling Database Access
- Authentication
- Authorities and Privileges
- Granting Authorities and Privileges
- Authorities and Privileges Needed to Perform Common Tasks
- Practice Questions
- Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 is assigned to an individual, based on their job title and/or their job responsibilities. Then, as the individual begins to work with the database, the set of authorities and privileges they have is modified as necessary. 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
|
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. |
|
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 same databases. |
|
System Operator |
Monitors the database and performs routine backup operations. Also performs recovery operations if needed. |
System Maintenance (SYSMAINT) 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 CREATETAB privilege for one or more databases, BINDADD and BIND privilege on one or more existing packages, one or more schema privileges for one or more schemas, and one or more table privileges for one or more tables. |
|
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 6 on Pages 261–262 of the IBM DB2 Administration Guide – Implementation manual. |