2.13 Privileges

The majority of scripts you develop will fall into one of two categories:

• Scripts to execute a specific function, such as loading test data into a table.

• Reporting scripts (e.g., a report on how much space your tables and indexes are using).

Report scripts tend to bring in to question your database privileges, particularly with regard to querying the data dictionary. There are three general categories of data dictionary views ”these categories are designated with the prefixes: " USER ," "ALL," and "DBA" ”these categories are fully described in Chapter 5. In addition to these views, you also have the Oracle "real-time" performance views. The performance views contain dynamic information that is essentially "real-time" data on the current instance.

It is reasonable to write scripts that query the data dictionary and if you have the most basic Oracle roles, CONNECT and RESOURCE, you will have some limitations.

To query the data dictionary beyond the scope of the basic views ”those that have the "USER" prefix ”you need one of the following:

You can have the DBA role, which may be unobtainable if the database is a production or test database. If the database is your own desktop database or a development environment, you can issue the following grant:

 GRANT DBA TO username; 

Even when you are experimenting with your own personal database, it is unwise to connect as SYSTEM of SYS to do regular development. These two accounts certainly give you open access but, still, it is not a good idea to use these on a regular basis.

Two other options are:

 GRANT SELECT_CATALOG_ROLE TO username; GRANT SELECT ANY TABLE TO username; 

Any of the aforementioned grants allows you to write unrestricted scripts against the data dictionary. The following script illustrates a useful query against the dynamic view V$INSTANCE. This script reminds you what database you are connected to ”this is helpful if you connect, on a regular basis, to several databases.

  --  Filename check_inst.sql SET HEADING OFF SELECT 'User='user'. You are connected to instance ' instance_name ' on 'host_name'.' FROM v$instance; SET HEADING ON  SQL>  @check_inst  User=SCOTT. You are connected to instance ora10 on mercury .   SQL>  

The aforementioned script reports that we are connected to the "ora10" instance on a host machine named "mercury" and you are connected as user SCOTT.