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

Data dictionary

Data dictionary is the most important part of oracle database. It provides some system information of database.

The data dictionary records the system information of the database. It is a collection of read-only tables and views. The owner of the data dictionary is the sys user.

Users can only perform query operations on the data dictionary, and its maintenance and modification are completed automatically by the system.

The data dictionary includes the data dictionary base table and the data dictionary view, in which the base table stores the basic information of the database. Ordinary users cannot directly access the base table of the data dictionary. The data dictionary view is a view based on the data dictionary base table. Ordinary users can query the data dictionary view to obtain system information.

The data dictionary view mainly includes three types of user_xxx,all_xxx,dba_xxx

The dynamic performance view is crowded with relevant information (frequently changing information) after the routine starts.

The data dictionary consists of a data base table and a dynamic view

The data dictionary base table stores static data.

The data dictionary dynamic view stores dynamic information.

The data dictionary records all the system information of the oracle database, which can be obtained by querying the data dictionary

Take a look at system information, such as:

1. Object definition

2. Size of space occupied by objects

3. Column information

4. Constraint information

Dynamic performance View

The dynamic performance view is used to record the activity information of the current routine. When oracle server is started, the system

A dynamic performance view is established and deleted when oracle server is stopped. Oracle

All dynamic performance views of start with vault $, and oracle provides synonyms for each dynamic performance view

And the synonym begins with V$, for example, the synonym of v_$datafile is v$datafile

The owner of the dynamic performance view is sys. In general, the dynamic performance view is queried by dba or privileged users

User_tables: used to display all the tables owned by the current user. It only returns all tables of the scheme corresponding to the user.

SQL > desc user_tables

Name Null? Type

-

TABLE_NAME NOT NULL VARCHAR2 (30)

TABLESPACE_NAME VARCHAR2 (30)

CLUSTER_NAME VARCHAR2 (30)

IOT_NAME VARCHAR2 (30)

STATUS VARCHAR2 (8)

Used to display all table information for the current scenario

SQL > select table_name from user_tables

TABLE_NAME

-

DEPT

EMP

BONUS

SALGRADE

STUDENT

All_tables: used to display all tables that the current user can access, it not only returns all tables of the current user scenario

It also returns a table of other scenarios that the current user can access.

For example, a user authorizes a table to a specified user to query and other operations

SQL > select table_name from all_all_tables

Dba_tables

It displays the database tables owned by all scenarios, but querying this database dictionary view requires that the user must be a dba role

Or have select any table system permission

For example, when the database dictionary view dba_tables is queried by system users, system and sys,scott are returned.

The database table corresponding to the scheme

Use system users

Select table_name from dba_tables

MYEMP

TEST

WRI$_ADV_OBJSPACE_TREND_DATA

MYTABLE

2729 rows selected

User name, permission, role

When creating a user, oracle stores the user's information in the data dictionary, when granting permissions or roles to the user

Oracle stores information about permissions and roles in the data dictionary

You can display the details of all database users by querying dba_users-- the system query used

SQL > desc dba_users

Name Null? Type

-

USERNAME NOT NULL VARCHAR2 (30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2 (30)

ACCOUNT_STATUS NOT NULL VARCHAR2 (32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2 (30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2 (30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2 (30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2 (30)

EXTERNAL_NAME VARCHAR2 (4000)

PASSWORD_VERSIONS VARCHAR2 (8)

EDITIONS_ENABLED VARCHAR2 (1)

AUTHENTICATION_TYPE VARCHAR2 (8)

The field is username, that is, the database user name, so you can find out how many users there are in the database

SQL > select username from dba_users

USERNAME

-

SYS

SYSTEM

XIAOBAI

XIAOMING

SCOTT

OUTLN

MGMT_VIEW

By querying the data dictionary view dba_sys_privs, you can display the system permissions that the user has

SQL > desc dba_sys_privs; system permissions table

Name Null? Type

-

GRANTEE NOT NULL VARCHAR2 (30)

PRIVILEGE NOT NULL VARCHAR2 (40)

ADMIN_OPTION VARCHAR2 (3)

SQL > select * from dba_sys_privs where grantee='SCOTT'

GRANTEE PRIVILEGE ADM

SCOTT UNLIMITED TABLESPACE NO

You can query the system permissions that each user has and whether he has configured with admin option.

SQL > select * from dba_sys_privs where grantee='SYSTEM'

GRANTEE PRIVILEGE ADM

SYSTEM GLOBAL QUERY REWRITE NO

SYSTEM CREATE MATERIALIZED VIEW NO

SYSTEM CREATE TABLE NO

SYSTEM UNLIMITED TABLESPACE YES

SYSTEM SELECT ANY TABLE NO

By querying the data dictionary view dba_tab_privs, you can display the object permissions that the user has

SQL > desc dba_tab_privs

Name Null? Type

-

GRANTEE NOT NULL VARCHAR2 (30)

OWNER NOT NULL VARCHAR2 (30)

TABLE_NAME NOT NULL VARCHAR2 (30)

GRANTOR NOT NULL VARCHAR2 (30)

PRIVILEGE NOT NULL VARCHAR2 (40)

GRANTABLE VARCHAR2 (3)

HIERARCHY VARCHAR2 (3)

By querying the data dictionary dba_col_privs, you can display the column permissions that the user has

SQL > desc dba_col_privs

Name Null? Type

-

GRANTEE NOT NULL VARCHAR2 (30)

OWNER NOT NULL VARCHAR2 (30)

TABLE_NAME NOT NULL VARCHAR2 (30)

COLUMN_NAME NOT NULL VARCHAR2 (30)

GRANTOR NOT NULL VARCHAR2 (30)

PRIVILEGE NOT NULL VARCHAR2 (40)

GRANTABLE VARCHAR2 (3)

By querying the database dictionary view dba_role_privs, you can show the roles a user has-"how do I view a user and what role does he have?"

SQL > desc dba_role_privs

Name Null? Type

-

GRANTEE VARCHAR2 (30)

GRANTED_ROLE NOT NULL VARCHAR2 (30)

ADMIN_OPTION VARCHAR2 (3)

DEFAULT_ROLE VARCHAR2 (3)

Query the roles of Scott users?

SQL > select granted_role from dba_role_privs where grantee='SCOTT'

GRANTED_ROLE

-

RESOURCE

The only two roles of CONNECT

The role of the user system

SQL > select granted_role from dba_role_privs where grantee='SYSTEM'

GRANTED_ROLE

-

AQ_ADMINISTRATOR_ROLE

MGMT_USER

DBA

1. How to query the permissions contained in a role?

A role contains system permissions the system permissions table is dba_sys_privs

SQL > desc dba_sys_privs

Name Type Nullable Default Comments

--

GRANTEE VARCHAR2 (30) Grantee Name, User or Role receiving the grant

Grantee: value is user or role

Query the system permissions of the role connect?

SQL > select * from dba_sys_privs where grantee='CONNECT'

GRANTEE PRIVILEGE ADM

CONNECT CREATE SESSION NO

So the system permission create session is the right to log in to the database, that is, the connect role.

There is also a table: role_sys_privs role system permissions table

SQL > desc role_sys_privs

Name Null? Type

-

ROLE NOT NULL VARCHAR2 (30)

PRIVILEGE NOT NULL VARCHAR2 (40)

ADMIN_OPTION VARCHAR2 (3)

A role contains the object permissions object permissions table: dba_tab_privs

SQL > desc dba_tab_privs; system object permissions table

Name Null? Type

-

GRANTEE NOT NULL VARCHAR2 (30)

OWNER NOT NULL VARCHAR2 (30)

TABLE_NAME NOT NULL VARCHAR2 (30)

GRANTOR NOT NULL VARCHAR2 (30)

PRIVILEGE NOT NULL VARCHAR2 (40)

GRANTABLE VARCHAR2 (3)

HIERARCHY VARCHAR2 (3)

SQL > select privilege from dba_tab_privs where grantee='CONNECT'

No rows selected

The permissions contained in a role come from system permissions and object permissions

2. How many roles does oracle have? Query table dba_roles

SQL > desc dba_roles

Name Null? Type

-

ROLE NOT NULL VARCHAR2 (30)

PASSWORD_REQUIRED VARCHAR2 (8)

AUTHENTICATION_TYPE VARCHAR2 (11)

SQL > select count (*) from dba_roles

COUNT (*)

-

fifty-five

SQL > select role from dba_roles

ROLE

-

CONNECT

RESOURCE

DBA

SELECT_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

LOGSTDBY_ADMINISTRATOR

DBFS_ROLE

AQ_ADMINISTRATOR_ROLE

There are 55 roles.

3. Query all system permissions in oracle?

Select * from system_privilege_map order by name

SQL > desc system_privilege_map

Name Null? Type

-

PRIVILEGE NOT NULL NUMBER

NAME NOT NULL VARCHAR2 (40)

PROPERTY NOT NULL NUMBER

SQL > select count (*) from system_privilege_map

COUNT (*)

-

two hundred and eight

4. Query the permissions of all objects in oracle?

Select distinct privilege from dba_tab_privs

5. Query the tablespace of the database? Query table system tablespace dba_tablespaces

SQL > desc dba_tablespaces

Name Null? Type

-

TABLESPACE_NAME NOT NULL VARCHAR2 (30)

BLOCK_SIZE NOT NULL NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NUMBER

MAX_SIZE NUMBER

PCT_INCREASE NUMBER

MIN_EXTLEN NUMBER

STATUS VARCHAR2 (9)

CONTENTS VARCHAR2 (9)

LOGGING VARCHAR2 (9)

FORCE_LOGGING VARCHAR2 (3)

EXTENT_MANAGEMENT VARCHAR2 (10)

ALLOCATION_TYPE VARCHAR2 (9)

PLUGGED_IN VARCHAR2 (3)

SEGMENT_SPACE_MANAGEMENT VARCHAR2 (6)

DEF_TAB_COMPRESSION VARCHAR2 (8)

RETENTION VARCHAR2 (11)

BIGFILE VARCHAR2 (3)

PREDICATE_EVALUATION VARCHAR2 (7)

ENCRYPTED VARCHAR2 (3)

COMPRESS_FOR VARCHAR2 (12)

SQL > select tablespace_name from dba_tablespaces

TABLESPACE_NAME

-

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

Displays the full name of the current database

Select * from global_name

SQL > select * from global_name

GLOBAL_NAME

ORCL11G.US.ORACLE.COM

Displays all data dictionary views that the current user can access

Select * from dict where comments like'% grant%'

SQL > desc dict

Name Null? Type

-

TABLE_NAME VARCHAR2 (30)

COMMENTS VARCHAR2 (4000)

SQL > select * from dict where comments like'% grant%'

TABLE_NAME COMMENTS

-

USER_AUDIT_STATEMENT Audit trail records concerning grant, revoke, audit, noaudit and alter system

USER_COL_PRIVS

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.