How to achieve that user B in Oracle can only access user A's View

We have the following requirement: create two users in the database. User A creates some views and directly accesses the tables in one mode in the database, and access tables in other databases through the database link. Another user B can access the views in user A, and can only access views, and the number of access connections is limited.

This user B is used to access other systems. Therefore, there must be restrictions on permissions and resource usage.

This requirement is common in many industries. If this is an interview question, how do you answer it?

I use the following method to answer this question.

Step 1: Create new users A and B.

Here, the user names are ryd_interface_src and ryd_interface respectively, which grant very limited permissions to both users.

Drop user ryd_interface_src cascade;

Create user ryd_interface_src identified by ryd_interface_src;

Grant connect, create view to ryd_interface_src;

 

Drop user ryd_interface cascade;

Create user ryd_interface identified by ryd_interface;

Grant connect, create synonym to ryd_interface;

Step 2: log on to the database in A mode and authorize user A so that user A can create A view

Conn qlzqclient/qlzqclient

Grant select on INVEST_CLOCK to ryd_interface_src with grant option;

Grant select on INVEST_LOG to ryd_interface_src with grant option;

Here, a with grant option is added to the authorization method. Please note that.

Step 3: log on to Database User A and create A view

Conn ryd_interface_src/ryd_interface_src

Create or replace view run_views

Select id as doc_id, title, fbsj as upload_date, ''as branch_code from qlzq. runs_lantern @ CLIENT_QLZQWEB

Where EXT1 = '1' and sysdate> START_TIME

And sysdate <SOLID_TIME

Union

Select a.doc _ id, a. title, a. upload_date, a. branch_code from qlzq. cms_doc_single_attr @ CLIENT_QLZQWEB

Left join qlzq. cms_doc_category_map @ CLIENT_QLZQWEB B on a.doc_id1_ B .doc _ id

Where

A. state = 1 and a. is_delete = 0

And a. upload_date> sysdate-90

And B. cat_id = 4;

 

Create or replace view INVEST_CLOCK_VIEWS

Select * from qlzqclient. INVEST_CLOCK;

 

Create or replace view INVEST_LOG_VIEWS

Select * from qlzqclient. INVEST_LOG;

1 2 Next Page