specifications

Existing database account: HEPSUSR: complete permission, add, delete, modify and check.

You need to create a database account, HTREADER, with read-only access to all tables under the HEPSUSR account.

Step 1: Create a read-only account

CREATE USER htreader identified by 123456;Copy the code

Step 2: Grant basic permissions such as database connection to the account

Grant connect to htreader; grant create view to htreader; grant create session to htreader; grant create synonym to htreader;Copy the code

Step 3: Obtain the query permission of the original account

Get all the query table of the original account HEPSUSR user permissions to select 'grant select on' | | owner | | '. '| | object_name | |' to htreader; ' from dba_objects where owner in ('HEPSUSR') and object_type='TABLE'; -- The query result is the assignment statement of the new account, as shown in the figure belowCopy the code

​​

​

Step 4: Assign the permission of the original account to the new account

Grant select on HEPSUSR.ENTRY_CERT to htreader ------- grant select on HEPSUSR. grant select on HEPSUSR.SUB_MESSAGE_INFO to htreader; grant select on HEPSUSR.ENTRY_CERT_RELATION to htreader; grant select on HEPSUSR.ENTRY_CERT_RELATION to htreader; grant select on HEPSUSR.ENTRY_DECL_TAX to htreader; grant select on HEPSUSR.ENTRY_DOCU to htreader; grant select on HEPSUSR.ENTRY_FEES to htreader; grant select on HEPSUSR.ENTRY_GOODS_TAX to htreader; grant select on HEPSUSR.ENTRY_HEAD to htreader; grant select on HEPSUSR.ENTRY_LIST to htreader; grant select on HEPSUSR.ENTRY_WORKFLOW to htreader; grant select on HEPSUSR.IQ_APPEND to htreader; grant select on HEPSUSR.IQ_CERT to htreader; grant select on HEPSUSR.SUB_SWAP to htreader; grant select on HEPSUSR.VIN_LIST to htreader;Copy the code

Step 5: Create a peer display table on the new account

Because the newly created read-only account is empty in the Tables bar, we need to add a display element in the PL/SQL display bar for the login interface of the new account, as follows:

-- Execute at the HEPSUSR end of the original account, Access to display the table name select 'create or replace SYNONYM htreader.' | | object_name | | 'for' | | owner | | '. '| | object_name | |'; ' from dba_objects where owner in ('HEPSUSR') and object_type='TABLE'Copy the code

​​

​

Step 6: Query the result on the new account

In the read - only account HTREADER side: add display each table information; Create or replace SYNONYM htreader.VIN_LIST for HEPSUSR.VIN_LIST; create or replace SYNONYM htreader.SUB_SWAP for HEPSUSR.SUB_SWAP; create or replace SYNONYM htreader.SUB_MESSAGE_INFO for HEPSUSR.SUB_MESSAGE_INFO; create or replace SYNONYM htreader.IQ_CERT for HEPSUSR.IQ_CERT; create or replace SYNONYM htreader.IQ_APPEND for HEPSUSR.IQ_APPEND; create or replace SYNONYM htreader.ENTRY_WORKFLOW for HEPSUSR.ENTRY_WORKFLOW; create or replace SYNONYM htreader.ENTRY_LIST for HEPSUSR.ENTRY_LIST; create or replace SYNONYM htreader.ENTRY_HEAD for HEPSUSR.ENTRY_HEAD; create or replace SYNONYM htreader.ENTRY_GOODS_TAX for HEPSUSR.ENTRY_GOODS_TAX; create or replace SYNONYM htreader.ENTRY_FEES for HEPSUSR.ENTRY_FEES; create or replace SYNONYM htreader.ENTRY_DOCU for HEPSUSR.ENTRY_DOCU; create or replace SYNONYM htreader.ENTRY_DECL_TAX for HEPSUSR.ENTRY_DECL_TAX; create or replace SYNONYM htreader.ENTRY_CONTAINER for HEPSUSR.ENTRY_CONTAINER; create or replace SYNONYM htreader.ENTRY_CERT_RELATION for HEPSUSR.ENTRY_CERT_RELATION; create or replace SYNONYM htreader.ENTRY_CERT for HEPSUSR.ENTRY_CERT;Copy the code

Step 7: Log in to the new account and check the result

The new account can query all table structures of the original account, but cannot be added, deleted or modified

​

Step 8: Perform delete and modify SQL statement tests

​

Appendix: Oracle Query account and permission details statement

1. View all users: select * from dba_users; select * from all_users; select * from user_users; 2. View the system permissions of the user or role (directly assigned to the user or role) : select * from dba_sys_privs; select * from user_sys_privs; SQL >select * from role_sys_privs; 3. 4. View user object permissions: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 5. Query all roles: select * from dba_roles; 6. Run the select * from dba_role_privs command to view the role owned by the user or role. select * from user_role_privs;Copy the code