0

My team will develop an internal (known users) application that has an architecture based on Java as front-end and PL/SQL as back-end. So, currently we are thinking in a better solution to manage the user/permissions, and we have two options:

  1. Each user has their own database account, granted with the permissions. Currently the legacy system use this approach and I don't like it because it manages permissions based on database objects' granularity. So, I believe it is a bad choice to have a database connection per user. Can you see more cons here?

  2. Build some tables at database to store the users and theirs permissions/profiles and build a PL/SQL procedure to do the login, generating a token and include a parameter to all others PL/SQL to verify this token and then authorize (or not) the execution.

So, you can ask me: why not just manage your permissions in your web-application? Answer: Those PL/SQL are already done and are used by all legacy systems, and this web-application should behave according it (ie. User permissions should be managed by the PL/SQL and its granularity based in please.)

How do you proceed in this case?

APC
  • 144,005
  • 19
  • 170
  • 281
irobson
  • 825
  • 8
  • 20
  • If there's a way to use your apostrophe key (') to make proper English spelling, that would help. Don't spell it "dont", that makes it hard to read. Capital letters at the beginning of sentences help make it easier to read, also. Please edit your question to be easy-to-read. – S.Lott Sep 16 '11 at 01:40
  • Sorry, i´ll remember that next time. – irobson Sep 16 '11 at 14:02

1 Answers1

1

I think using the database's built-in mechanism is always to be preferred over rolling our own. And that applies to logging in users as much as anything else.

The biggest single advantage of dedicated user accounts is that we can link a given session with a named user. Well, yes, duh. But the point is, doing thinks like auditing user activity or tracing a performance issue in some process is way more difficult in web applications with generic accounts.

To address your main objection, we don't have to manage database privileges at the user level. That's why we have roles. For normal users, a role will provide sufficient privileges.

So:

  1. define a set of roles which match the various business jobs your application serves.
  2. grant system and object permissions to those roles; remember that roles can be additive (i.e. we can grant privileges on a role to another role).
  3. grant roles to the users.

Find out more.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I really understand the roles of a database´s user, but i´m not sure if it is the better approach to manage user applications permissions - i believe that it is a different kinds of roles, where database´s user has roles to database´s objects and application´s users has permissions to business roles. Am i still confused? I´ll check the link, thanks. – irobson Sep 16 '11 at 14:00
  • In other words, it mean that i will need to create a web datasource per user logOn. – irobson Sep 16 '11 at 14:10