Database Security

Meanwhile, in a space-time continuum unknown to Earth, dongwei_at_creek.eel.ufl.edu wrote:

> In our application code we would like to have different roles
> enabled at different time.

Hello, Yisheng -

An interesting problem. I assume you will not be distributing compiled code.

I have seen this level of security created using the database itself, i.e., a tabular approach where the application, via package, checks a user's current status and if the parameters fit, executes. This gets around creating a zillion roles, though you might still want three or four broad-based ones. For example, is there a class of users who can never update? who can update 'if A'? who can update at any time? etc. One assigns users to these broad roles, then uses application logic for the 'if A' role and executes the stored procedure(s) as appropriate.

The downside is the application must track what a user is doing, rather than leaving security to the database. However, all a user could see from the source is that you are tracking where he is in the app. If you are truly paranoid, you could create a particular ID to own the packages and procedures and isolate that ID's privileges. That will baffle 99% of the user population. The other 1% usually disappears under mysterious circumstances :-).

hth -

jh

--
Jared Hecker                                  |  Oracle Architect, DBA
hecker_at_panix.com                       |      - consulting in the 
76276.740_at_compuserve.com   |        NY/NJ region