Concepts in Oracle: synonyms, sequences, and views

Synonym

CREATE [PUBLIC] SYNONYM synonym For schema. object

Hide the Object Name and owner:

Select count (*) from hr. employees;

Create synonym emp for hr. employees; -- by default, it belongs to the donny user and is the private object of donny.

Select count (*) from emp;

Provides location transparency for remote objects in distributed databases:

When accessing other databases, you must first establish a database connection:

Create database link test_link connect to username identified by pass USING 'orabase ';

Select count (*) from hr. employees @ test_link;

Create synonym link_emp for hr. employees @ test_link;

Select count (*) from link_emp;

Provide public access to objects:

Create public synonym pub_emp for hr. employees;

Pub_emp is a public user and can be accessed by all database users.

Synonym type

-Private emp is actually donny. emp

-Public pub_emp all users can directly access

When a public object and a private object have the same name (because the data is different from the user, it is acceptable), the private object takes precedence. (Similar to local variables)

Desc dba_synonyms/user_synonyms/all_synonyms data dictionary, plural

Tab public Synonym

Create a private tab to view the results.

Delete synonym:

Drop synonym donny. emp;

Drop public synonym pub_emp;

Sequence:

Create sequence donny. seq -- also belongs to a user. The following parameters can be omitted and the default value is used.

Increment by 1 -- specifies the interval between sequences, positive and negative integers. The default value is 1, which is ascending and the negative value is descending.

Start with 1 -- the first serial number. The default value is MINVALUE.

NOMAXVALUE -- set the maximum value, which indicates the 27 power of 10 by default. MAXVALUE 10

NOMINVALUE -- set the minimum value, which indicates the 26 power of-10 by default. MINVALUE 1

NOCYCLE -- or CYCLE; indicates whether to start from scratch after the sequence reaches the maximum or minimum (descending ).

CACHE 10; -- the default CACHE is 20. The number of serial numbers allocated in advance is stored in the memory to increase the speed.

Access sequence:

Oracle provides two pseudo columns for the sequence as its attributes.

Nextval: a new sequence value obtained based on increment. Each execution will get a new value.

Currval: current value, current value, obtained value.

Select seq. nextval from dual;

Select seq. currval from dual;

Sequence:

Insert into t values (seq. nextval );

Modify sequence:

Alter sequence seq ..... Specify parameters again

The start with cannot be modified, unless the reconstruction is deleted.

Delete sequence:

Drop sequence seq;

Data Dictionary:

Desc dba_sequences/user _... /All ....

View:

CREATE [or replace] [FORCE/NOFORCE] VIEW

Create view mytable

As

Select first_name | ',' | last_name

From hr. employees;

[Experiment]: How to use a view as a secure href = "http://safe.it168.com/" target = _ blank> Security Mechanism

1. desc evaluate the knowledge of hr. employees as a company employee information database table.

2. Goal: to enable each employee to access the name, email, and phone_number of all employees in the company to facilitate communication.

3. solution:

A) are all employees authorized to access the hr. employees table? Salary

B) Create a view that only contains the appropriate fields, and grant all employees the permission to access this view, not the table permission.

4. Alter user hr account unlock;

Conn hr/hr

Create view company_phone_book

Select first_name | ',' | last_name name, email, phone_number

From employees;

Grant select on company_phone_book to public;

Desc company_phone_book comparison Column Length

Select * from company_phone_book;

Complexity of hiding data by name

1 2 Next Page