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

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.