Options to run different schema users

[zircon06]

I'm in dilemma how to run different schema users using single login. I'm not favourable of running by system, sys users. I have customer,audit,users schemas how to run these schema using single login instead of schema user itself. Just for though by giving dba permission to that user will I able to accomplish the task?

I appreciate anyone input in removing conclusion of this process.

Thanks in advance

 

[SantaMufasa]

Zircon,

You can do what you want by GRANTing privileges. I recommend your doing the following:

1) CREATE ROLE <role name>;
2) GRANT <role name> TO <1 or more Oracle users>;
3) GRANT <privilege(s)> ON <table name> TO <role name>;

Log in as the owner of your application tables, then do step #3 for as many tables as you need to grant access for your application.

At that point, you can use a single user login (which is a member of <role name>) to access all of the objects that <role name> can access.

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[zircon06]

Hi Mufasa !!!! Thanks for your note. I will try let know you

 

[zircon06]

It didn't work for me. For testing purpose I have customer schema and given grant select, insert,delete, updateon customer.table to role called user_role. when I have granted user_role to other user "app_user" and try to login with other user I'm unable to see that customer.table. What I'm missing here

Thanks for your help in advance

 

[SantaMufasa]

Zircon,

Let's use your scenario to illustrate the issue you are having and its simple resolution.

Given:
[tt]
User schema: "Customer"
User schema: "App_user"
Role : "User_role"
App Table : "App_Tab"
[/tt]
Actions:

1) Customer issues "GRANT select, insert,delete, update on App_tab to User_role;"

2) "Grant user_role to app_user;"

3) app_user issues: "Select * from app_tab;" and receives error: "ERROR at line 1: ORA-00942: table or view does not exist"

4) app_user re-issues query: "Select * from customer.app_tab;"...this time it is successful with explicit schema qualification.

5) app_user issues: "create synonym app_tab for customer.app_tab;"

6) app_user re-issues query: "Select * from app_tab;"...this time is is successful without explicit qualification.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]