When you create a table, you become its owner. Only you and a person with DBA authority can use the table. However, you may want to share access to your tables.
You can give access to your data using an SQL GRANT statement, and you can take away access using the SQL REVOKE statement. See the DB2 Server for VSE & VM SQL Reference manual for more information.
If you want to grant the identical capabilities to a group of users, you use a single GRANT statement. For example, assume Mona is part of a department responsible for maintaining the quarter information. Users Jim, Dan, and Dee also need update privileges on the QUARTER column of the EMPL table. You can extend the update capability to all three by typing:
grant update(quarter) - on empl - to jim,dan,dee
You can also grant privileges to the public within a list of grantees as illustrated below:
grant update(quarter) - on empl - to jim,public,dee
Views can be used to restrict a privilege to specific rows of your tables. For example, assume Jim and Dee maintain quarter information for different groups of employees based on level of education. The following view supplies the information Jim needs for employees of education level 16 or less:
create view to16 - as select empno,lastname,edlevel,birthdate,quarter - from empl - where edlevel <= 16
Similarly, the following view supplies all the information needed by Dee:
create view past16 - as select empno,lastname,edlevel,birthdate,quarter - from empl - where edlevel > 16
The next step is to grant Jim and Dee privileges on the views. Type the following GRANT statements:
grant select,update(quarter) - on to16 - to jim grant select,update(quarter) - on past16 - to dee
Views can also be used to restrict the columns on which a user can type SELECT or INSERT statements. For example, you can give Jim and Dee the capability to select salary information from the EMPLOYEE table, but restrict them from viewing commission information simply by specifying the SALARY column and omitting the COMM column when you create the view. Create the view using:
create view blindempl - as select salary - from employee
Now give Jim and Dee table privileges:
grant select - on blindempl - to jim,dee
In an earlier section of this chapter, a private dbspace as a section of the database reserved for your tables was described. A private dbspace is suitable for your personal tables and is not really appropriate for tables that are to be shared.
Instead, a public dbspace should be used. When a table is inserted into a public dbspace, multiple users can update the table simultaneously. No two users, however, can use the same row at the same time.
To insert a table into a public dbspace, provide the database manager with the name of the dbspace using the IN clause of the CREATE TABLE statement. For example, the EMPL table can be created in a public dbspace named SAMPLEDB by typing:
create table empl - (empno char(6), - lastname varchar(15), - edlevel smallint, - birthdate date, - quarter integer) - in sampledb
When the dbspace name is not supplied on the CREATE TABLE statement, the table is created in one of your private dbspaces.
When you refer to a table (or view) in an SQL statement, the database manager assumes you are referring to a table that you own. If you want to access a table that belongs to another user, you must identify the user and the table name. This is done by inserting the owner's authorization ID before the table name and separating the two with a period.
For example, you can access the system copy of the EMPLOYEE table (created during the installation of the database manager) by using the following query:
select * - from sqldba.employee
This query retrieves the copy of the EMPLOYEE table that is owned by the sample user, SQLDBA. Your personal copy of the EMPLOYEE table is ignored. Similarly, other users have to use this method to access your EMPL table.
If an authorization ID does not begin with a letter, number, $, #, or @, you must enclose it in double quotation marks. For example:
select * - from "%A23C".payroll
For detailed information about identifier naming conventions, see the DB2 Server for VSE & VM SQL Reference manual.
To avoid specifying an authorization ID for another user's table or view that you access frequently, you can create synonyms for their tables and views. For example, you can assign a synonym to be used in place of SQLDBA.EMPLOYEE by typing:
create synonym dbaemp - for sqldba.employee
Now you can refer to SQLDBA.EMPLOYEE by the synonym DBAEMP. For example, if you type:
select * - from dbaemp
The database manager displays all information from the EMPLOYEE table.
You can also use the CREATE SYNONYM statement to assign a synonym to one of your own tables. For example, if you are user JOHN, you can assign the synonym ih to your EMPL table by typing:
create synonym ih - for john.empl
When you finish using the table or view for which you have defined the synonym, you should drop the synonym. To drop the synonym ih, for example, type:
drop synonym ih
The table or view on which the synonym was defined is not affected by this command.
Note: | Because the performance of a DROP TABLE or DROP VIEW statement does not drop associated synonyms, you must drop the synonym(s) yourself. |