Page 135
A database link is an object stored in a local database that tells Oracle the name of the remote database that can be accessed through Oracle's SQL*NET. The remote database is the server that contains the table or data. You can access the remote database just like you access local tables, except that you must use the suffix @linkname after the table name. The syntax of this command is contained in Figure 6.2.
FIG. 6.2
The syntax of the
command to access
the remote database.
Database administrators are the only ones that have the ability to create public links. After public links are created, they are available to all users. Links created without this feature are available to the creator only.
To delete the database link, use the drop database link command followed by the name of the link. If the link is public, you must use the word public. Only a database administrator can drop a public database link.
When Oracle is first installed, before you can begin using the product for work, you need to create user accounts and grant privileges to the user. The user account enables the user to log on to Oracle. The privileges enable the user to create tablespaces, begin defining objects such as tables, indexes, and views, and populate the tables with data.
Page 136
The database administrator creates user accounts by using the create command. Figure 6.3 illustrates the syntax and an example of a Create command used to create student I.D.'s for an Oracle class. When the password is supplied, users must supply the password each time they log on. Specifying the externally option, the access is verified through the operating system security. The default tablespace is the place the user creates objects such as tables, unless specified in the create table command. Temporary tablespace is where temporary objects are created. The quota option enables you to limit the amount of space that a user can allocate. Oracle assigns the default profile to the user unless the profile option lists a named profile.
FIG. 6.3
Creating a User
Account command
syntax and example.
In the example, a user account called ostu6 was created. It requires a password of redskins, uses the tablespace oracle_class as the default, and enables the user to allocate 2M or 20,002,048 bytes.
After creating the user account, you must grant the account privileges. Privileges are permissions to perform various functions. One of these privileges is create session. This privilege enables the user to log on to the database. Without granting this privilege, the user account cannot perform any functions.
Two types of privileges exist: system privileges and object privileges. The system privileges enable you to perform data definitions commands such as create table or drop index. You use the object privileges to perform data manipulation commands such as insert, update, and delete.
Page 137
Figure 6.4 displays the two forms of the grant command used to give privileges. The two differ in that the command granting object privileges has the on clause specifying the object. The command can define several privileges at one time to multiple users. The optional with admin option enables the user to grant system privileges to other users.
FIG. 6.4
Granting privileges
syntax and example.
The final statement illustrates a grant command that used to grant privileges to students. It gives the user id ostu1 the ability to perform a number of common functions. Table 6.3 contains privileges available for the users.
Table 6.3 System Privileges
Privilege | Description |
Alter any cluster | The ability to modify a cluster on any user id |
Alter any index | The ability to modify an index on any user id |
Alter any procedure | The ability to modify a procedure on any user id |
Alter any role | The ability to modify a role on any user i.d. |
Alter any sequence | The ability to modify a sequence on any user id |
Alter any snapshot | The ability to modify a snapshot on any user id |
Alter any table | The ability to modify a table on any user id |
Alter any trigger | The ability to modify a trigger on any user id |
continues
Page 138
Table 6.3 Continued
Privilege | Description |
Alter database | The ability to modify the user's database. |
Alter profile | The ability to modify the user's profile. |
Alter resource cost | The ability to modify session costs. |
Alter rollback segment | The ability to modify a rollback segment. |
Alter session | The ability to modify a session. |
Alter system | The ability to modify a system. |
Alter tablespace | The ability to modify a tablespace. |
Alter user | The ability to modify a user id |
Analyze any | The ability to analyze. |
Audit any | The ability to audit. |
Audit system | The ability to audit. |
Become user | The ability to import objects from any user id |
Backup any table | The ability to export objects from any user id |
Comment any table | The ability to record comment on a table on any user id |
Create any cluster | The ability to create a cluster on any user id |
Create any index | The ability to create an index on any user id |
Create any procedure | The ability to create a procedure on any user id |
Create any sequence | The ability to create a sequence on any user id |
Create any snapshot | The ability to create a snapshot on any user id |
Create any synonym | The ability to create a synonym on any user id |
Create any table | The ability to create a table on any user id |
Create any trigger | The ability to create a trigger on any user id |
Create any view | The ability to create a trigger on any user id |
Create cluster | The ability to create a cluster on a user id |
Create database link | The ability to create a database link for a user id |
Create procedure | The ability to create a procedure on a user id |
Create profile | The ability to create a profile on a user id |
Create public database link | The ability to create a database link available for all user id's. |