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!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.