Doubt user creation for consultation

[ThiagolRamos]

Good morning SRS,

I have a question that is probably simple, but I did not find the answer in another place.

In the ERP database we have a created user, it has as standard the ERP schema, with this, when accessing the bank using this user to make some query simply enter the name of the Table in Select:



I have a new application and for security I created a new user, I put the ERP schema as default too, and I gave permission only Select and in some tables only.

My question is in relation to query, when I try to run the same select, he says he does not find the table, so that the Select works on this user I need to put the full name of the place :

Select all

SELECT * FROM NOMEBANCO.NOMETABELA

Can anyone tell me how I can find the tables in a "normal" ways like the first user?

[spernega]

Good morning Thiago,
See if it helps ...

I create the user in this way:

Select all

create user joao identified by joao 
default tablespace dados 
temporary tablespace temp 
account unlock;

Select all

grant create session, "nome da role do seus usuários" to joao;

in relation to the Owner, it creates synonyms for its object.

Here I create:

Select all

Create or repace public synonym nome_tabela for nome_tabela.

Need to see if in your ERP the synonym was created as a public or specific for any role.

[ThiagolRamos]

Good morning Spernega,

The user had already been created that way.

In case, my problem is being because of these synonyms only?

See, it's not something that will stop me from using the user, just bothers having to put the entire name to make some query.
I wanted to improve this since in the default user does not have.

[spernega]

Thiago,

Try to do a select to see the type of synonym that was created for the object.

Select all

select * from sys.SYNONYMS where creator = 'NOME DO OWNER' and TNAME = "NOME DE UMA TABELA";

If Syntype is not published, take a test connected as the Owner


99] Hence see if you still need to inform the Owner in Select

if we do not solve people think a little more.

[spernega]

Thiago,

If you create the public synonym, you may have to drop before

Drop Synonym Name

[ThiagolRamos]

I did this research, using the ERP user and found nothing. There is no synonym for this user.

I then accessed with the user System and SYS and looked for this time for the TNAME, taking any table, nothing too.

Strange, but I understood the concept and how to be done.

Thanks for the help.

[spernega]

Good morning Thiago,

Just out of curiosity, managed to solve?

[stcoutinho]

Thiago,

Good morning,

a script suggestion:
a) Creation of a role (Create role. .)
b) User Creation

Select all

CREATE USER .. DEFAULT TABLESPACE .. TEMPORARY TABLESPACE

c) Minimal System Permissions

Select all

GRANT CREATE SESSION .. GRANT ALTER SESSION .. QUOTA 0 ON TABLESPACE

d) Assigning selects from some tables to the role

Select all

GRANT SELECT ON  .. TO <NOME_ROLE>

and) Creation of private synonym pointing to ERP objects. Let's say you have created a user query and the ERP schema is called ERP_OW. Your command to create private synonym would be

Select all

CREATE OR REPLACE SYNONYM CONSULTA.<NOME_TABELA> FOR ERP_OW.<NOME_TABELA);

f) I had forgotten, but you also need to assign the role to your user (eg, grant <name_role> to consultulate);
I believe that with this you will not have problems accessing as "query" and do selects, without the need to mention ERP_OW before the table name.

Nothing against a public synonym, but you would have problems if in this instance there were other tables with the same name, but belonging to different schemas (a public synonym only points to a single schema object). With the private synonym, you would avoid problems in situations like this.

Good luck,

Sergio Coutinho

[spernega]

Good stcoutinho,

I was also in doubt about how to create a private synonym.

I learned one more.

Thanks!

[ThiagolRamos]

Good morning,

No, I honestly do not understand because there is no synonymous of these recorded tables. With no user.

then it does not make sense not to use the bank name before with a user and the other no.

But I will use it that way.

Select all

Select * from NOMEBANCO.NOMETABELA

Thanks

[spernega]

Thiago,

I have already seen DBAs create views of other OWNER to another user or role view an object.

Try to do this select and see what returns

Select all

select * from all_objects where object_name = 'NOME DA TABELA'; 
 
Num caso que eu tenho no meu sistema, retorna o seguinte: 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- 
PUBLIC                         IMPORT_CLI                                                        264538                SYNONYM