The Oracle database accesses tables under other users, without the implementation of the user name that the table belongs to

A. Question:

How to implement a user name that does not need to be added to a table when accessing other users ' tables in the Oracle database

Two. For example:

User A in Oracle needs to access User B's table with a prefix of User B, such as access to User B's TEST table, which requires access to the select * from B.test; now is the question of how to prefix a user name without adding a username.

Three. Reason:

1. Easy access to common tables
2. User who hides a table

Four. Solution:1. Create synonyms (the best way)

Grammar:

    CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.] object[@db_link];

Follow the example to extend the demo:

 （1）登录sysdba为用户B授予创建同义词的操作权限：    sqlplus /nolog     conn /as sysdba;     grant create synonym to B;            （2）登录B用户为A授予访问特定表TEST的权限：    //注意：这是A用户能够使用同义词访问B的TEST表的前提    grant select on TEST to A;    //撤销的方式:    revoke select on TEST from A; （3）创建同义词    create synonym SY_TEST for B.TEST; （4）登录用户A进行测试.

2. Create a view (this method is also good)

    create view VW_TEST select * from B.TEST;

3. Modify the Current_schema parameter

The schema used to toggle the current session. When object name resolution is performed, Oracle automatically queries for matching objects in this schema if the schema name is not qualified before the object name.

For example, when a Scott user executes a select * from emps; statement, Oracle queries the Emps table under the Scott schema by default, but if the alter session set current_schema=xx; current schema of the session is changed, Oracle queries the Emps table under the XX schema when the statement is executed.

        登录用户A时,临时修改current_schema参数:            SQL> alter session set current_schema=B;        Session altered.        SQL> show user;        USER is "A"        SQL> select SYS_CONTEXT(‘USERENV‘,‘CURRENT_SCHEMA‘) CURRENT_SCHEMA from dual;        CURRENT_SCHEMA                                                                          --------------------------------------------------------------------------------        B                                                                                SQL>         SQL> spool off;

This method is only applicable to the current session, and once the session is closed, it will not work the next time. So this is a viable option, but GM can't.

The Oracle database accesses tables under other users, without the implementation of the user name that the table belongs to