8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for Schema Owners and Application Users


Lesio said...

You should publish every week :) why do I have to wait so long for new article !

Your Fan
Lesio

Tim... said...

Hi.

It depends how inspired I'm feeling. :)

Cheers

Tim...

Max said...

I would not even grant any privileges (except quota) to but use a "special" account for schema setup.

Gary said...

Why not use public synonyms for your schema objects? Individual private synonyms become a nightmare. Create public synonyms for the schema objects, and grant the necessary privs to the roles. Am I missing something here? Public synonyms can be a security concern - but there are no privs associated with them directly (other than revealing the existence of the object to the whole database).

Tim... said...

Hi.

As I said in the article, what happens when you have multiple schema in a single instance. In our comany we have many schema on our development server, many of which have commonly named tables, but with different purposes. In this case, we might need 4 different synonyms with the same name, pointing to different objects. You can't do that with public synonyms.

With private synonyms you can always drop the synonyms and point to a new location.

Cheers

Tim...

Muzammil said...

Hi,

A quick question.. if we are having a trigger on logon to alter the session to the SCHEMA_OWNER, how will the audit work if I want to turn on the Audit Trail for the logged in user. I mean if app_user is logging in, will the Audit Trail log all the activites under app_user username or under schema_owner username in dba_audit_trail table.

Thanks.

Adam said...

Thank you, I found your article very useful and straightforward.

iic1tls said...

Nice article...

However, I wanted to write and let you know that the Current Schema approach does not work in the Ora11gR2 environment. Specifically, I can perform a DESCRIBE against the table as either user account, but I can not perform an INSERT as the schema owner account, and perform a SELECT as the user account. I receive the 'no rows selected' when I try to perform a select.

Can you please update this otherwise great article to include instructions on how to get the code to allow me to perform a select as the user account? THANK YOU

iic1tls said...

I apologize for my prior post. I was able to localize the problem.

I have observed that when schema_owner performs an insert into a table, the insert has to be followed with a COMMIT in order for the user account to see the updated contents of the table. My mistake. THANK YOU for this excellent article.

Tim... said...

Hi.

No worries. I just tried it out and was about to post saying it works fine when I saw your second message. Easy done. :)

Cheers

Tim...

AbhishekSahu said...

Explained the Synonym and current_schema concept very nicely. Thanks for this article.

Cheers,
Abhishek

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!