Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.



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...

DO NOT ask technical questions here, that's what my forum is for!

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)