Understanding DB2: Learning Visually with Examples (2nd Edition)

2017-07-07 02:10:07

10.5. Database Object Privileges

Controlling access to database objects is as important as authenticating users and managing administrative authorities. Privileges give users the right to access each individual database object in a specific way. Privileges can be granted explicitly and implicitly. The following sections list all the supported privileges for each database object and discuss implicit privileges.

If you are not familiar with any database objects discussed in the following sections, see Chapter 7, Working with Database Objects.

10.5.1. Schema Privileges

There are three schema privileges:

CREATEIN allows users to create objects within the schema.

ALTERIN allows users to alter objects within the schema.

DROPIN allows users to drop objects within the schema.

For example, you can specify the GRANT and REVOKE statements against a given schema, as shown in the syntax diagrams in Figures 10.16 and 10.17.

Figure 10.16. GRANT syntax diagram for schema privileges

.-,------------. V | >>-GRANT----+-ALTERIN--+-+--ON SCHEMA--schema-name--------------> +-CREATEIN-+ '-DROPIN---' .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+--------------------> | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'

Figure 10.17. REVOKE syntax diagram for schema privileges

.-,------------. V | >>-REVOKE----+-ALTERIN--+-+--ON SCHEMA--schema-name-------------> +-CREATEIN-+ '-DROPIN---' .-,---------------------------------. V | .-BY ALL-. >--FROM----+-+-------+--authorization-name-+-+--+--------+----->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

Previously we introduced the IMPLICIT_SCHEMA database authority, which allows the grantee to create a schema via the creation of database objects. You can also create a new schema explicitly using the CREATE SCHEMA statement, for example:

CONNECT TO sample USER dbowner; CREATE SCHEMA dev AUTHORIZATION devuser;

The CREATE SCHEMA statement requires that user dbowner has the SYSADM or DBADM authorities on database SAMPLE. This creates a schema called dev where devuser is the schema owner.

You can also create a schema and database objects within that schema in one SQL statement. Figure 10.18 demonstrates a straightforward example. You simply fully qualify the object name with the schema. For example, in Figure 10.18, the table dairyprod and index prodindx are created in the schema of grocery. Schema grocery will be automatically created if it does not already exist.

Figure 10.18. Example of CREATE SCHEMA and DDL in one statement

CREATE TABLE grocery.dairyprod ( prodno SMALLINT NOT NULL , desc VARCHAR(100) , qty INTEGER ); CREATE INDEX grocery.prodindx ON grocery.dairyprod (prodno);

Before moving to the next database object privilege, you may be wondering about the WITH GRANT OPTION in the GRANT statement in Figure 10.16. This option allows the named authorized user to grant the named privileges to other users. It also applies to all other database object privileges (except for indexes).

10.5.2. Table Space Privileges

Tables and table spaces are logical objects, as discussed in Chapter 7, Working with Database Objects. Tables are logically stored in table spaces, and table spaces are associated to physical storage devices. You need some USE privileges to be able to define tables in a table space. Figures 10.19 and 10.20 show the GRANT and REVOKE syntax diagrams with the USE privilege available for a specific table space. When a table space is created, its USE privilege is granted to PUBLIC by default. If you want to restrict usage of the table space, you should revoke the USE privilege from PUBLIC and grant it to selected users or groups individually.

Figure 10.19. GRANT syntax diagram for table space privileges

>>-GRANT--USE--OF TABLESPACE--tablespace-name--TO---------------> .-,---------------------------------. V | >----+-+-------+--authorization-name-+-+------------------------> | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'

Figure 10.20. REVOKE syntax diagram for table space privileges

>>-REVOKE USE OF TABLESPACE--tablespace-name--FROM--------------> .-,---------------------------------. V | .-BY ALL-. >----+-+-------+--authorization-name-+-+--+--------+----------->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

The following examples show how to grant and revoke the table space privileges.

GRANT USE OF TABLESPACE userspace1 TO USER db2admin; REVOKE USE OF TABLESPACE userspace1 FROM PUBLIC;

10.5.3. Table and View Privileges

There are additional privileges for tables and views. Table 10.9 describes these privileges.

Table 10.9. Summary of Table and View Privileges
Table and View Privileges	Descriptions
CONTROL	Provides users with all privileges for a table or view as well as the ability to grant those privileges (except CONTROL) to others.
ALTER	Allows users to alter a table or view.
DELETE	Allows users to delete records from a table or view.
INSERT	Allows users to insert an entry into a table or view.
REFERENCES	Allows users to create and drop a foreign key, specifying the table as the parent in a relationship.
SELECT	Allows users to retrieve data from a table or view.
UPDATE	Allows users to update entries in a table or view. This privilege can also limit users to update specific columns only.
ALL PRIVILEGES	Grants all the above privileges except CONTROL on a table or view.

Figures 10.21 and 10.22 show the GRANT and REVOKE syntax diagrams for table and view privileges respectively.

Figure 10.21. GRANT syntax diagram for table and view privileges

.-PRIVILEGES-. >>-GRANT--+-ALL--+------------+---------------------------+-----> | .-,-----------------------------------------. | | V | | '---+-ALTER---------------------------------+-+-' +-CONTROL-------------------------------+ +-DELETE--------------------------------+ +-INDEX---------------------------------+ +-INSERT--------------------------------+ +-REFERENCES--+-----------------------+-+ | | .-,-----------. | | | | V | | | | '-(----column-name-+--)-' | +-SELECT--------------------------------+ .-PRIVILEGES-. >>-GRANT--+-ALL--+------------+---------------------------+-----> | .-,-----------------------------------------. | | V | | '---+-ALTER---------------------------------+-+-' +-CONTROL-------------------------------+ +-DELETE--------------------------------+ +-INDEX---------------------------------+ +-INSERT--------------------------------+ +-REFERENCES--+-----------------------+-+ | | .-,-----------. | | | | V | | | | '-(----column-name-+--)-' | +-SELECT--------------------------------+ '-UPDATE--+-----------------------+-----' | .-,-----------. | | V | | '-(----column-name-+--)-' .-TABLE-. >--ON--+-------+--+-table-name-----+----------------------------> +-view-name------+ '-nickname-------' .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+--------------------> | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'

Figure 10.22. REVOKE syntax diagram for table and view privileges

.-PRIVILEGES-. .-TABLE-. >>-REVOKE--+-ALL--+------------+-+--ON--+-------+---------------> | .-,--------------. | | V | | '---+-ALTER------+-+--' +-CONTROL----+ +-DELETE-----+ +-INDEX------+ +-INSERT-----+ +-REFERENCES-+ +-SELECT-----+ '-UPDATE-----' >--+-table-name-+-----------------------------------------------> +-view-name--+ '-nickname---' .-,---------------------------------. V | .-BY ALL-. >--FROM----+-+-------+--authorization-name-+-+--+--------+----->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

The following examples show how to grant and revoke some table and view privileges.

GRANT ALL PRIVILEGES ON TABLE employee TO USER db2admin WITH GRANT OPTION; GRANT UPDATE ON TABLE employee (salary, comm) TO GROUP db2users; REVOKE CONTROL ON TABLE employee FROM PUBLIC;

You probably noticed that the above GRANT and REVOKE statements also apply to nicknames (database objects that represent remote tables and views residing in different databases). The remote databases can be databases in the DB2 family or non-DB2 databases. This feature is known as federated database support and was briefly discussed in Chapter 2, DB2 at a Glance: The Big Picture.

10.5.4. Index Privileges

Privileges for managing indexes is fairly straightforward: you can only drop an index after it is created. To change an index key, for example, you need to drop the index and recreate it. The CONTROL privilege allows the grantee to drop the index. Figures 10.23 and 10.24 list GRANT and REVOKE statements with index privileges.

Figure 10.23. GRANT syntax diagram for index privileges

>>-GRANT--CONTROL--ON INDEX--index-name-------------------------> .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+------------------->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

Figure 10.24. REVOKE syntax diagram for index privileges

>>-REVOKE CONTROL ON INDEX--index-name--------------------------> .-,---------------------------------. V | .-BY ALL-. >--FROM----+-+-------+--authorization-name-+-+--+--------+----->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

The following examples show how to grant and revoke index privileges.

GRANT CONTROL ON INDEX empind TO USER db2admin; REVOKE CONTROL ON INDEX empind FROM db2admin;

10.5.5. Package Privileges

A package is a database object that contains the data access plan of how SQL statements will be executed. A package needs to be bound to a database before its associated program can execute it. The following are the privileges you use to manage packages.

BIND allows users to rebind an existing package.

EXECUTE allows users to execute a package.

CONTROL provides users the ability to rebind, drop, or execute a package as well as the ability to grant these privileges (except CONTROL) to others.

Figures 10.25 and 10.26 show the GRANT and REVOKE statements for package privileges respectively.

Figure 10.25. GRANT syntax diagram for package privileges

.-,----------------. V | >>-GRANT----+-BIND---------+-+----------------------------------> +-CONTROL------+ '-EXECUTE------' >--ON--PACKAGE-------+--------------+--package-id---------------> '-schema-name.-' .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+--------------------> | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'

Figure 10.26. REVOKE syntax diagram for package privileges

.-,----------------. V | >>-REVOKE----+-BIND---------+-+---------------------------------> +-CONTROL------+ '-EXECUTE------' >--ON--PACKAGE-------+--------------+--package-id---------------> '-schema-name.-' .-,---------------------------------. V | .-BY ALL-. >--FROM----+-+-------+--authorization-name-+-+--+--------+----->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

The following examples show how to grant and revoke package privileges:

GRANT EXECUTE, BIND ON PACKAGE emppack1 TO GROUP db2grp WITH GRANT OPTION; REVOKE BIND ON PACKAGE emppack1 FROM USER db2dev;

10.5.6. Routine Privileges

To be able to use a routine, a user must be granted with its associated EXECUTE privilege. As illustrated in Figures 10.27, and 10.28, EXECUTE is the only routine privilege, but it applies to all types of routines: functions, methods, and stored procedures.

Figure 10.27. GRANT syntax diagram for routine privileges

The following examples show how to grant and revoke routine privileges:

GRANT EXECUTE ON PROCEDURE salary_increase TO USER db2admin WITH GRANT OPTION; REVOKE EXECUTE ON PROCEDURE salary_increase FROM USER db2admin;

Figure 10.28. REVOKE syntax diagram for routine privileges

10.5.7. Sequence Privileges

A sequence object generates sequential numeric values. By default, the group PUBLIC can use any sequence object unless they are controlled by the USAGE privilege, as shown in Figure 10.29. You can restrict usage of certain sequence object by revoking USAGE from PUBLIC.

There may also be cases where you want to change the sequence object definition, such as the minimum, maximum, and incremental values. You probably want to limit the ability to alter a sequence object to only a few users. Use the ALTER privilege (shown in Figures 10.29 and 10.30) to do that. RESTRICT is the default behavior that prevents the sequence from being dropped if dependencies exist.

Figure 10.29. GRANT syntax diagram for sequence privileges

.-,---------. V | >>-GRANT----+-USAGE-+-+--ON SEQUENCE--sequence-name-------------> '-ALTER-' .-,---------------------------------. V | >--TO----+-+-------+--authorization-name-+-+--------------------> | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------' >--+-------------------+--------------------------------------->< '-WITH GRANT OPTION-'

Figure 10.30. REVOKE syntax diagram for sequence privileges

.-,---------. V | >>-REVOKE----+-ALTER-+-+--ON SEQUENCE--sequence-name------------> '-USAGE-' .-,---------------------------------. V | .-RESTRICT-. >--FROM----+-+-------+--authorization-name-+-+--+----------+--->< | +-USER--+ | | '-GROUP-' | '-PUBLIC------------------------'

The following examples show how to grant and revoke sequence privileges.

GRANT USAGE, ALTER ON SEQUENCE empseq TO USER d2admin WITH GRANT OPTION; REVOKE ALTER ON SEQUENCE empseq FROM db2admin RESTRICT;

10.5.8. Implicit Privileges

As discussed previously, DB2 privileges usually are granted explicitly with GRANT statements. In some cases users may also obtain privileges implicitly or indirectly by performing certain operations. You should pay attention to these privileges and determine whether they are valid per the security policies in your company.

A user who is granted DBADM authority is also implicitly granted BINDADD, CONNECT, CREATETAB, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA privileges.

When a user creates a database, the following authorities and privileges are also granted implicitly:
- DBADM authority is granted to the database creator.
- CONNECT, CREATETAB, BINADD, and IMPLICIT_SCHEMA privileges are granted to PUBLIC.
- USE OF TABLESPACE privilege on the table space USERSPACE1 is granted to PUBLIC.
- BIND and EXECUTE privileges on each successfully bound utility are granted to PUBLIC.
- EXECUTE privileges WITH GRANT OPTION on all functions in the SYSFUN schema is granted to PUBLIC.

A user who creates a table, view, index, schema, or package automatically receives CONTROL privilege on the database object he or she creates.

If a program is coded with static SQL statements, packages that contain data access plans are generated and bound to the database at compile time. When a user executes the package, explicit privileges for database objects referenced in the statements are not required. The user only needs EXECUTE privilege on the package to execute the statements. However, this does not mean that the user has direct access to the underlying database objects.

Consider the example illustrated in Figure 10.31. A package dev.pkg1 containing UPDATE, SELECT, and INSERT statements are bound to the database. A user who only has EXECUTE privilege on dev.pkg1 can only manipulate table t1 through the package. He cannot issue SELECT, UPDATE, and INSERT statements directly to t1.

Figure 10.31. Example of controlling database object access via packages

As mentioned earlier in this chapter, when a privilege is revoked, there is no cascade effect to also revoke the implicit privileges. For example, if user bob is granted DBADM authority, he also implicitly receives the privileges BINDADD, CONNECT, CREATETAB, CREATE_NOT_ FENCED, and IMPLICIT_SCHEMA. Assuming, for some reason, DBADM is revoked from bob with this statement:

REVOKE dbadm FROM USER bob

bob no longer has DBADM authority, but he still has BINDADD, CONNECT, CREATETAB, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA authorities. Each of them must be explicitly revoked if you want to remove all authorities from bob.