DBA readonly account

Question

I had a schema in one oracle DB as ui_prod. I asked my DBA team guys to create exactly same schema like ui_prod but as read only and name it ui_prod_readonly. Usually I will use Oracle SQL developer to connect a DB and query directly with table name like below.

--Connect to ui_prod
select * from table

but why I requested to put owner name infront when query for readonly schema they created for me, as without putting it, I get error table not exist.

--Connect to ui_prod_readonly
select * from ui_prod.table

I have project files which hardcode the sql query with only table names and adding owner name in front will cause many changes and effort. Can anyone explain me on this? or provide me any document/link to read. Thanks

mic.sca · Accepted Answer · 2016-07-19 10:08:47Z

1

You should look into synonyms, apparently the user you are connecting to the database as is not the owner of the objects. So to view the object you have to prepend the names with the schema name (the owner of the object themselves).

http://www.techonthenet.com/oracle/synonyms.php

CREATE OR REPLACE  SYNONYM ui_prod_readonly.synonym_name
  FOR ui_prod.object_name

It seems to me that your dbas have not created another set of tables but just granted the existing tables to the user ui_prod_readonly.

answered Jul 19, 2016 at 10:08

mic.sca

1,6882 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2016-07-19 11:06:02Z

When you log in to Oracle, the current schema is the name of the user you used to log in. So if you log in with ui_prod_readonly Oracle checks that schema for the table if you do not qualify it with the owner (=schema).

If you want to change the current schema so that you don't need to fully qualify the tables, you can do that with ALTER SESSION

alter session set current_schema = ui_prod;

Once you have done that, you don't need to fully qualify the table with the owner (=schema).

waleedazam · Accepted Answer · 2017-07-24 15:44:46Z

1

if you need a user to read the data only its simple to create new user and grant it only select privilege

you can create user and grant select privilege using

CREATE USER [user] IDENTIFIED BY [your_password];
grant select on table to [user]

answered Jul 24, 2017 at 15:44

waleedazam

3672 silver badges12 bronze badges

Collectives™ on Stack Overflow

DBA readonly account

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related