How can I create a user in Oracle and grant specific permissions ?, Oracle Permissions

How can I create a user in Oracle and grant specific permissions ?, Oracle Permissions

This article is edited by the Markdown syntax editor.

1. Demand proposal

When HIS is integrated with a third-party system, a third-party system needs to access the view of HIS database rather than all the database tables.

The HIS database is Oracle, so you need to add another user on the basis of the original user in Oracle. This user can only view the views opened for him. This is also for security considerations between systems.

2. Solution

After technical consultation, the solution is as follows:

(1) log on to the oracle database as a system user.

(2) create a user and grant the view permission to the user.

Assume that you want to create a tester user with the logon password "123456 ".

The view to be opened by HIS is in the outpatient of the original user. The view name is v_daily_charge.

The SQL statement to be written is:

-- 01: Create a tester user with the initial password of 123456. create user tester identified by "123456"; -- 02: grant the user the permission to log on to the database. grant create session to tester; -- 03: grant this user the permission to view v_daily_charge under outpatient. grant select on outpatient. v_daily_charge to tester;

Note: when running the third SQL statement, the oracle database may report an error.

ORA-01725.

After research, the cause of the error should be that the tester user wants to access the outpatient user's view, which will view some tables under the outpatient user, such as t_user and t_dept tables, you may also access other users' tables, such as the t_cost table under the emergency user.

To avoid the preceding errors, you must grant the view permission to view outpatient to the tester user, grant the table permissions of other users to be accessed in this view to the tester user.

Therefore, before the third SQL statement, you need to add a few more:

-- Grant the tester user the permission to view the t_user and t_dept tables under the outpatient user and the t_cost table under the emergency user. grant select on outpatient. t_user to tester; grant select on outpatient. t_dept to tester; grant select on emergency. t_cost to tester;