Previous | Table of Contents | Next

Page 139

Privilege Description
Create public synonym The ability to create a synonym available for all user id's.
Create role The ability to create a role.
Create rollback segment The ability to create a rollback segment.
Create session The ability to create a session for a user id
Create sequence The ability to create a sequence for a user id
Create snapshot The ability to create a snapshot for a user id
Create synonym The ability to create a synonym for a user id
Create table The ability to create a table on a user id
Create tablespace The ability to create a tablespace.
Create trigger The ability to create a trigger on a user id
Create user The ability to create a user id
Create view The ability to create a view on a user id
Delete any table The ability to delete a table on any user id
Drop any cluster The ability to drop a cluster from any user id
Drop any index The ability to drop an index from any user id
Drop any procedure The ability to drop a procedure from any user id
Drop any role The ability to drop a role from any user id
Drop any sequence The ability to drop a sequence from any user id
Drop any snapshot The ability to drop a snapshot from any user id
Drop any synonym The ability to drop a synonym from any user id
Drop any table The ability to drop a table from any user id
Drop any trigger The ability to drop a trigger from any user id
Drop any view The ability to drop a view from any user id
Drop profile The ability to drop a profile.
Drop public database link The ability to drop a public database link.
Drop public synonym The ability to drop a public synonym.
Drop rollback segment The ability to drop a rollback segment.
Drop tablespace The ability to drop a tablespace.
Drop user The ability to drop a user id
Execute any procedure The ability to run a procedure from any user id
											continues

Page 140

Table 6.3 Continued

Privilege Description
Force any transaction The ability to force a commit or rollback for a
pending transaction.
Force transaction The ability to force a commit or rollback on a user id
Grant any privilege The ability to grant a database privilege.
Grant any role The ability to grant a role to any user id
Insert any table The ability to insert records into a table on any user id
Lock any table The ability to lock any table.
Manage tablespace The ability to manage any table.
Readup The ability to query data with a higher access class
than the current session normally allows.
Restricted session The ability to logon during the restricted access
mode by SQL*DBA.
Select any sequence The ability to select a sequence on any user id
Select any table The ability to select records from a table on any user id
Unlimited tablespace The ability to surpass the assigned limits.
Writedown The ability to create, alter, drop, insert, update,
or delete database objects with access ratings lower than
the current session.
Writeup The ability to create, alter, drop, insert, update, or delete
database objects with access ratings higher than
the current session.

The privileges that contain the word any, such as Select any table, enable the user to perform the function on all objects. When the privilege does not contain this word, the user can only perform the function on the database object he owns or has created.

You can use the Revoke command to take away privileges. Figure 6.5 illustrates the syntax and an example of the command. You can revoke privileges or in one group by using the option all. After you revoke privileges from a user, they are also revoked from any user granted a privilege from the revokee.

Granting Access to Your Tables

The tables you create are initially available only to users of the Oracle ID or account that originally created them. You might, however, want other users to have access to the table. Other

Page 141

users can gain access to the tables with their Oracle ID'S through the use of the Grant command. There are several object privileges that can be granted. Listing 6.21 illustrates several Grant common grant commands.

FIG. 6.5
Revoke privilege system
and example.


Listing 6.21 L_06_21.sql—Granting and Revoking Privileges

SQL> grant select on employee to ostu1;

Grant  I succeeded.

SQL> grant insert, update, select, delete
  2    on employee
  3    to ostu2, ostu3;

Grant succeeded.

SQL> grant insert, update, select, delete
  2    on employee
  3    to public;

Grant succeeded.

SQL> revoke insert, update, select, delete
  2    on employee
  3    from public;

Revoke succeeded.

SQL> revoke insert, update, select, delete
  2    on employee
  3    from ostu2, ostu3;

Revoke succeeded.

SQL> revoke select on employee from ostu1;

Revoke succeeded.

The Grant command has three components: the privilege granted, the name of the table, and user i.d.(s) gaining the privilege. You can extend four privileges: select, insert, update, and delete. The select privilege enables the user to perform select statements against the table. The insert option enables the user to add new records. Update enables the user to modify values in an existing record, and delete enables the user to remove records from the table.

Page 142

You can set multiple privileges at one time to multiple users. Each user id can have different privileges if needed. In addition, all ids can be given the privilege when the word public is used. To take the privileges away, use the revoke command. That command has the same syntax as the Grant command.

You also use the Grant command to extend a variety of privileges. This chapter discusses these privileges in more detail in later sections.

Creating the Tablespace

The tablespace is an area of the disk comprised of one or more files. The tablespace is where Oracle keeps the tables, indexes, and clusters that have been defined. The tablespace has a fixed size and can become filled. When this occurs, you can expand it by creating a new disk file and adding it to the tablespace. Oracle then begins using this file along with the existing ones for the objects it holds. The database administrator creates tablespaces. Listing 6.22 illustrates the syntax of the create tablespace command. EMPLOYEE is the name of the tablespace. The tablespace will initially contain one file `emp.tab' with a maximum space of 1000K or 1,024,000 bytes. The K symbol means 1,024 bytes, and the M symbol means 1,048,576 bytes. The default storage is used as the initial table storage size when it is not defined in the create table command.

Listing 6.22 L_06_22.sql—Creating Tablespace Employee

SQL> create tablespace employee datafile `emp.tab' size 1000 k
  2    default storage (initial 25 k next 10 k
  3    minextents 10 maxextents 100);

Tablespace created.

SQL> drop tablespace employee;
Tablespace dropped.

When a table is first created, an area of disk space (extent) is set aside in the tablespace. This area is called the initial extent. In the example, this extent is defined as 25K and the next extent is defined at 10K. Additional extents are created automatically when the existing extents are filled up until the entire tablespace is full. The minextents option enables you to set aside a number of additional extents at the time the table is created. The maxextents setting is the limit to the number of extents the table can use. The pct increase option is a growth factor for extents. In the example, it is set at 25. This means that each additional extent will be 25 percent larger than the one before it.

Figure 6.6 illustrates the syntax of the altertablespace command and settings. The add datafile option enables you to increase the tablespace size by adding one or more files described according to the file_definition to the tablespace. The format of the file definition is: `filename' [size integer [K | M]] [reuse]. The name of the file is followed by the size of the file and the

Previous | Table of Contents | Next