Oracleで読み取り専用アカウントを作成する詳細手順

-- Create a read-only account Step 1
CREATE USER htreader identified by 123456;

--give htreader general privileges such as connect
grant connect to htreader;
grant create view to htreader;
grant create session to htreader;
grant create synonym to htreader;

Get all query table permissions for the original HEPSUSR user
select 'grant select on '||owner||'. ||object_name||' to htreader;'
from dba_objects
where owner in ('HEPSUSR')
and object_type='TABLE';

-- The query results in an assignment statement for the new account, as follows

Execute under the original account HEPSUSR to assign the query privileges of the original account to the new account
-------
grant select on HEPSUSR.ENTRY_CERT to htreader;
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;

-- execute on the HEPSUSR side of the original account to get the name of the table that needs to be displayed
select 'create or replace SYNONYM htreader.'||object_name|| ' for ' ||owner|| '.' ||object_name|| ';'
from dba_objects
where owner in ('HEPSUSR')
and object_type='TABLE'

Execute on the read-only account HTREADER side: add to display individual table information; no display in SYSNONYM directory, no display in tables directory
create or replace SYNONYM htreader.VIN_LIST for HEPSUSR;
create or replace SYNONYM htreader.SUB_SWAP for HEPSUSR;
create or replace SYNONYM htreader.SUB_MESSAGE_INFO for HEPSUSR;
create or replace SYNONYM htreader.IQ_CERT for HEPSUSR;
create or replace SYNONYM htreader.IQ_APPEND for HEPSUSR;
create or replace SYNONYM htreader.ENTRY_WORKFLOW for HEPSUSR.ENTRY_WORKFLOW;
ENTRY_LIST for HEPSUSR.ENTRY_LIST. create or replace SYNONYM htreader;
create or replace SYNONYM htreader.ENTRY_HEAD for HEPSUSR;
ENTRY_GOODS_TAX for HEPSUSR.ENTRY_GOODS_TAX; create or replace SYNONYM htreader;
create or replace SYNONYM htreader.ENTRY_FEES for HEPSUSR;
create or replace SYNONYM htreader.ENTRY_DOCU for HEPSUSR.ENTRY_DOCU;
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;
create or replace SYNONYM htreader.ENTRY_CERT for HEPSUSR.ENTRY_CERT;

1. To view all users.
select * from dba_users;
select * from all_users;
select * from user_users;

2. To view user or role system privileges (system privileges assigned directly to the user or role).
select * from dba_sys_privs;
select * from user_sys_privs;

3. View the permissions contained in the role (only the role owned by the logged-in user can be viewed)
sql>select * from role_sys_privs;

4. View user object permissions.
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

5. To view all roles.
select * from dba_roles;

6. To see the roles owned by a user or role: select
select * from dba_role_privs;
select * from user_role_privs;