Oracle authorized users to query tables and views under another user

In practice, you will encounter a situation where a user needs to query the table data or view under another user, but when there is no authorization, an error with no permission operation is indicated. Then you need to pass the authorization process, and then the query operation, let's see how it is handled.

First, System permission description:

1. User Rights

CREATE Sessioin Connect to Database

CREATE table creates tables in the user's scenario

Create SEQUENCE creating a sequence in the user's schema

Create view creates views in the user's scenario

CREATE procedure creates a stored procedure, function, or package in the user's schema

1.1. Example: Granting system permissions

DBAs can grant user-specified system permissions

GRANT Create Session,create table,

Create Sequence,create View

to Scott;

II. Create tables and views that users only use to query other user libraries

1. Create user

Create user username identified by password, grant connect,select any table to username, so the user created can connect to the database and only have query permission on any table. Grant connect to username/ /Only Connection permissions

2. Authorization query table and View permissions

2.1, a user authorization to query all tables to B users (a user login to execute the following statement)

select  ' Grant select on a. '  | |  tname | |   '  to b; '  from tab; ' Grantselectona. ' | | tname| | ' TOB; ' ------------------------------------------------------grant select on a.vote_num to b ; grant select on a.tmp_msg to b;grant select on a.vote_ip to  b;grant select on a.question to b;grant select on a.question_ Count to b;grant select on a.record_dict to b;grant select on  a.bm_column to b;grant select on a.bm_column_classify_rel to b;grant  select on a.bm_info_classify to b;grant select on a.bm_module to  b;grant select on a.BM_MODULE_AUTH to b; or select  ' Grant select on   ' | | table_name| | '  to b; '   from user_tables; ' Grantselecton ' | | TABle_name| | ' TOB; ' ----------------------------------------------------grant select on vote_num to b; grant select on tmp_msg to b;grant select on vote_ip to b; Grant select on question to b;grant select on question_count to  b;grant select on record_dict to b;grant select on bm_column  to b;grant select on BM_COLUMN_CLASSIFY_REL to b; Description: Executes the statement under a user, After execution, it generates an assign permission statement for all tables, and copies it to execute it.

2.2, a user authorization to query a single table to B users

Grant Select on A.tablename to B;

2.3, a user authorization to query all the sequence to B users

Select ' Grant Select on ' | | Sequence_name | | ' to B; ' from dba_sequences where sequence_owner= ' A ';

2.4 、--Oracle Query User view

SELECT * from User_views;

select  ' Grant select on a. '  | |  view_name | |   '  to test11; '  from user_views; view query as follows: ' Grantselecton ' | | view_name| | ' TOTEST11; ' ---------------------------------------------------------Grant select on confirm_reservation_ View to test11;grant select on department_reservation_view to test11;grant  select on DEPART_CANCEL_RESERVATION_VIEW to test11;grant select on  Doctor_cancel_reservation_view to test11;grant select on doctor_reservation_view  to test11;grant select on GRPSS to test11;grant select on  Hospital_all_schedule_view to test11;grant select on hospital_department_view to  test11;grant select on hospital_dep_schedule_view to test11;grant select  on hospital_doctor_view to test11;grant select on hospital_doc_schedule_view to test11; ' Grantselecton ' | | view_name| | ' TOTEST11; ' ---------------------------------------------------------Grant select on patient_count_ reservation_view to test11;grant select on patient_reservation_view to  Test11;grant select on patient_reservation_view2 to test11;grant select on  patient_res_view to test11;grant select on prview to test11;grant  select on RES_VIEW to test11;grant select on SS to test11;

Note : Authorization updates, deleted syntax, and authorization queries are similar, except that the keywords are different.

Third, revoke permissions

1, authorized a user to cancel the permission to delete a single table to B user

Revoke delete on A.tablename Fromb;

2, authorized a user to cancel the permission to update a single table to B users

Revoke update on a.tablename from B;

3. The user who has DBA authority cancels the permission to create dblink for User B

Revoke CREATE DATABASE link from B;

4, the user with DBA authority to cancel to the TES11 user to query the permissions of any table

Revoke select any table from test11;

Iv. Examples:

1, under the Rh_test user authorized to query all tables to WD users

select  ' Grant select on rhip_test. '  | |  tname | |   '  to wd; '  from tab; ' Grantselectonrh_test. ' | | tname| | ' Towd; ' ----------------------------------------------------------------Grant select on rh_test.biz_ Code_rel to wd;grant select on rh_test.biz_rmim_dic to wd;grant select  on rh_test.biz_rmim_version to wd;grant select on rh_test.biz_rmim_version _detail to wd;grant select on rh_test.biz_rmim_version_subdetail to wd; Grant select on rh_test.biz_system_login to wd;grant select on rh_test . biz_tree_path to wd;grant select on rh_test. Clinic_transfer to wd;grant select on rh_test. code_system_dic to wd; ' Grantselectonrh_test. ' | | tname| | ' Towd; ' ----------------------------------------------------------------Grant seleCt on rh_test. Etl_patient_index to wd;grant select on rh_test. Hospital_dic to wd;grant select on rh_test. Hospital_subsystem to wd;grant select on rh_test. Mail_record to wd;grant select on rh_test. Medical_record to wd;grant select on rh_test. Patient_index to wd;grant select on rh_test. Rhip_sysconfig to wd;grant select on rh_test. systemlogin to wd; The above statement can be executed.

2, a user authorization to query a single table to test11 users

Select ' GRANT SELECT on ' | | table_name | |  ' to test11; ' The results obtained from User_tables are as follows: GRANT SELECT on Webservice_user to Test11grant SELECT on Userless_patient to Test11, and then the results obtained above are executed individually Again: Grant SELECT on Webservice_user to Test11grant SELECT on userless_patient to test11; The newly created table will have to be accessed by UserA to execute the GRANT statement: Grant S Elect on new table to UserA;

3, authorized a user under authorization to update a single table to B users

Grant Update on a.tablename to B;

4, authorized a user under authorization to delete a single table to B users

Grant Delete on a.tablename to B;

5, the user with DBA authority to create Dblink to B users

Grant CREATE database link to B;

Oracle authorized users to query tables and views under another user