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 !
It depends how inspired I'm feeling. :)
Max said...I would not even grant any privileges (except quota) to
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).
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.
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.
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.
No worries. I just tried it out and was about to post saying it works fine when I saw your second message. Easy done. :)
AbhishekSahu said...Explained the Synonym and current_schema concept very nicely. Thanks for this article.
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!