• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How Would You Setup Oracle User Accounts

 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My application consists of struts+servlet in web tier. The biz logic is in POJOs inside web container (no ejb). Database is accessed by JDBC (no OR mapping). Database is Oracle9i. Some persistence logic is written in PLSQL stored proc+trigger.

Could anyone share the way they create user account(s) in Oracle to store the tables, packages, procedures, functions and triggers. Since user is not directly accessing the db, I think just one or two user accounts would do the job plus some extra dba accounts. Am I right?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alec,
Yes! We create an "application id" and any DBA ids needed. The application id access the database on behalf of the real users.
 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne,

How many 'application id' do you create and would you store Oracle tables and packages/procedure/functions under different accounts.

Because one scheme I heard of is to store tables in one schema(user1) and procedures in another(user2) and create an account for JDBC client(user3).
The way the accounts interact is like this: user3(JDBC client) is given EXECUTE object privileges to user2's procedures and the procedures are having Definer's rights(so when the procedures access the tables, they are under user2's right). The actual tables are stored in user1 and shielded from user3 direct access.

This sounds a bit complicated. One disadvantage is that JDBC client cannot issue direct SQL against the tables, althought this increase the security as the user's password usually exposed in the JDBC URL string and it would not be a good thing if tables are directly exposed.

I would be happy if anybody could share their experience in setting up the accounts. Thx for your advice.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alec,
We have a schema owner id in addition to the application id I described. We don't have stored procs, so that part doesn't apply. It sounds like we are doing the same thing!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic