Shulou(Shulou.com)06/01 Report--

This article introduces you how to write catalog.sql script, the content is very detailed, interested friends can refer to, hope to be helpful to you.

The script $ORACLA_HOME/rdbms/admin/catalog.sql script was executed after the database was created by manual creation, and the purpose of this script is highlighted here.

The user of this script creates oracle dynamic performance views, such as vaccounsessiondir, vaccounsessionsession, waitjournal, vportable transactoin, and so on. This kind of performance view is mainly used to monitor the running status of the oracle instance, and the comments at the beginning of the script file have already explained its role:

Rem NAME

Rem CATALOG.SQL

Rem FUNCTION

Rem Creates data dictionary views.

Rem NOTES

Rem Must be run when connected AS SYSDBA

Users who must be in the sysdba role can execute the script.

Let's analyze the commonly used v$session.

Create or replace view v_$session as select * from v$session

Create or replace public synonym v$session for v_$session

Grant select on v_$session to select_catalog_role

You can see that the database further encapsulates the underlying v$session view by executing the script.

By creating a new view v_$session and assigning query permissions for that v_$session view

In the role of select_catalog_role, any user can access the v_$session view of sys users through sys.v_$session as long as it has the role of select_catalog_role. However, considering the inconvenience of user access and the encapsulation of the corresponding view of sys users, a public synonym is created.

The user with the select_catalog_role role actually accesses only the public synonym v$session, which encapsulates and hides the underlying v$session view, and the user does not know whether the sys user is another user at the time of the view.

Looking down further, all dynamic performance views are encapsulated and hidden from the views under sys users in this way. We can refer to this approach to achieve similar encapsulation.

SQL > conn / as sysdba

SQL > create table test (t number (10))

SQL > conn scott/tiger

SQL > select count (*) from test

ERROR at line 1:

ORA-00942: table or view does not exist

Switch back to sys user

SQL > conn / as sysdba

SQL > create view test_ as select * from test

View created.

SQL > create public synonym test for test_

Synonym created.

SQL > grant select on test_ to tiger

Grant succeeded.

The middle view test_ query permissions have been assigned. Switch back to scott user and try

SQL > conn scott/tiger

Connected.

SQL > select count (*) from test

COUNT (*)

-

0

It can be seen that scott users can query the test table under sys users. But in fact, the user is only querying this public synonym.

It is also important to note in the catalog script that all the query permissions for the vault $view are assigned to the select_catalog_role role, meaning that as long as the user is assigned to that role, all the v$ performance views in the script can be queried.

SQL > grant select_catalog_role to scott

Grant succeeded.

SQL > conn scott/tiger

Connected.

SQL > select count (*) from v$session

COUNT (*)

-

seventeen

Scott users can already query v$session views, otherwise scott users are not allowed to query performance views such as v$ after they are created according to utlsampl.sql.

The select_catalog_role role is created automatically when the script to create the oracle database is executed, because the $ORACLE_HOME/rdbms/admin/sql.bsq script is automatically executed by oracle when the database is created. Through this script, we can see that it is as follows:

/

Create role select_catalog_role

/

Create role execute_catalog_role

/

Create role delete_catalog_role

/

Grant select_catalog_role to dba with admin option

/

Grant execute_catalog_role to dba with admin option

/

Grant delete_catalog_role to dba with admin option

You can see that oracle automatically creates the select_catalog_role role when the library is built, assigns the role to the dba role, and allows users of the dba role to grant permissions.

On how to write the catalog.sql script to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.