authentication through web?
Harvey, October 15, 2001 - 9:35 am UTC
That is very helpful. Then, I have a slightly different situation. If I have a web page running Java Servlet or JSP connected to our Oracle database (8.1.7), and I want to let only our NT domain user be able to log in. How should I set it? Also, which one is better for this purpose, Java Servlet or JSP?
October 15, 2001 - 4:24 pm UTC
Well, that isn't going to work. You have a client connected via the web to the app server. The app server logs into the database from its machine. The database client here is the app server, not the client running a web browser. You cannot log into the database over the web using an app server and have the credentials passed all the way through.
the java code will have to log in "as itself", you cannot pass the NT user through a "man in the middle".
In 9i, something that may be of interest to you is:
</code>
http://www.oracle.com/pls/db901/db901.to_toc?pathname=java.901/a90211/oci_func.htm#1014235 <code>
You can combine this with your desire to authenticate all of the way through. The client would identify itself to the middle tier, you can then connect on their behalf to the database.
As servelet or JSP -- it is pretty much the same to me. JSPs are mixing html + java, a servelet is something that generates html (generally, could be xml or anything really) from scratch. Depends on what tools you are using and how comfortable you are with either technology.
Good advice for batch jobs...
Andrew, October 15, 2001 - 6:09 pm UTC
Security is always a headache, a list of pros and cons certainly helps.
OS authentication in ASP and UNIX environment
Sujata, January 23, 2003 - 12:38 pm UTC
Hi Tom,
We are using Active Server Pages..(IIS server)..
and database is 8i on unix...
We are trying to move to NT username authentication...
Here is the plan:
There will be a table that has ntusername married to the employee_id.
Application will always move in as one user that has all the rights...instead of multiple databse users that we have now...
All the security is done through the app (using employee_id of the user)..there are security tables already in place that are using security based on emplids....in the existing system
So, no change there....we can return emplid to the app based on the NT user name passed in....
Questions:
What are your thoughts/ways about doing OS authentication in this environment and security in this kind of environment set up..
Also we were currently using triggers on the tables to catch modified by's and modified dates for any dml's
It was easy before..because we used the database user who was logged in to populate modified by column of the table..
but now it's single user environment...so we want to store..
NTUSER name...which only application can now...so the trigger does not have the ease of slipping in "USER" for modified by....so one solution is to change the procedure..and queries to accept a parameter for NTUSER..and change the queries to also update MODIFIEDBY..which they weren't doing before...
Is there another way to do this through trigger.....
Thanks for your help...
proxy authentication
Sujata, January 23, 2003 - 2:36 pm UTC
January 23, 2003 - 7:17 pm UTC
the proxy authentication was available only to OCI programs in 8i.
If you are undergoing such a massive update - you might consider using software written this century? It has the feature you need to make this succesful with *no* changes to the underlying logic/security already in place in the database after all.
proxy authentication 9i
Sujata, January 24, 2003 - 5:32 pm UTC
Tom,
So, you are saying we have to go to 9i to take advantage of "proxy authentication". If we have to go to 9i..then we will just wait for this NT user authentication until we go to 9i..
Could you discuss..please..the caveats of going with the model where..the application goes in as a single user..that has execute on all the packages...so this way...no user need to have any access on any tables...
and then you do specific access through some application roles like "manager" or "employee"....and you can assign these roles to different employees through database tables..
This way..there are no users that have any access to any tables or any packages..there is only a single user...
and so users can not connect through sqlplus or toad...
they can only connect through their NTUSER name...and that NTUSER name will be linked to the employee id's in security tables in the database..and that is where they will be assigned different roles for this particular application.
Please explain...
Thanks.
January 24, 2003 - 6:11 pm UTC
well, with packages no user need to have access on any tables.... either - they shouldn't, just execute on the package.
Lets see what you lose with the single user
o all auditing
o all grants
o roles
o any ability to reuse this stuff in the future when the next great programming paradigm comes along (cause the security is bound in the middle tier now)
o you would have to rewrite your code that already exists to fit into this single user model
o you rewrite the security you already bought and paid for.
And if you think that not being able to access the database via anything other then your application is a bonus, I'm not sure if you've talked to the end users. Everyone I meet has some ad-hoc requirements at some point. That and the "cat's meow of programming paradigms today" -- well, thats today, tomorrow will be a different day just as last year was different and so on....
Just my 2cents worth.
Proxy Authentication
Ray, March 08, 2003 - 12:45 pm UTC
Hi Tom.
I want to authenticate internal users (NT) web application
What do I need to set up proxy authentication
IE which Oracle products
and What do I have to do
Ive read the proxy links and can't make heads or tails out of them.
tia
March 08, 2003 - 1:48 pm UTC
"I want to authenticate internal users (NT) web application"
I've no idea what you mean by that -- I cannot make heads nor tails out of that myself.
is it that you have an application server that allows you to identify users by NT authentication and will pass onto your java application this identity?
or are you asking me how to get an app server to authenticate using nt (which I'll just say "no clues")
authentication
Ray, March 09, 2003 - 11:59 am UTC
Ok. Gotchya. It was the latter and I guess thats a no go, which is reasonable.
Can you describe how you set up authentication for ask tom.
Currently I am using the ONE BIG APP USER approach where the DAD signed the user into ORACLE. The PLSQL/HTML app asks for the signon. (I know yuk) I authenticate the user in a table with encrypted password. I use Application context, a session cookie(encrypted session id timestamp),
and FGAC based on the app (internal user_id). The user gets fucntionality to manage their app accounts, and administer the FGAC.
Extending this to oracle ad hoc users, would entail a Login trigger to set up their context appropriately, otherwise they'd have no access due to the policies.
To use oracle instead of my user table would mean I would be creating Oracle Users on the fly. Can you explain how you do this, and to extend this to Proxy Authentication using OID, what steps would I take and what would it mean what to the application? Would I need a middle tier login?, What would this be?, Would I need Advanced Security option from Oracle? Is there a step by step example available?
tia
Ray
March 09, 2003 - 12:08 pm UTC
I have no authentication for asktom, it is a public website.
For the admin screen, MY interface, i use database authentication. browser puts up login screen and i put in my db username/password.
Creating Oracle users on the fly is easy (i do it on my internal system). It is just "execute immediate 'create user ' || .....". What I do is have people input their email address, generate a password for them, create the account and use utl_smtp to email it to them.
to use proxy authentication -- you would do the same -- create the account but you would also grant connect through to some application account. Then you would write some java or C code that authenticated the user however you wanted, and this java or C code would connect to the database as the application user on behalf of the real user (don't need to know the real users database password -- in fact the user might not even know what the users password in the db is in this case).
You do not need ASO for this.
If you goto the 9i docs on otn.oracle.com and search for proxy authentication, you'll get pointed to examples.
John, April 29, 2003 - 4:19 pm UTC
Hi Tom,
I have two dedicate windows 2000 servers: one has Oracle database 9.0.1, the other has application server runing with JSP which uses jdbc thin connection to the database. There is an oracle user owns the whole application schema. Currently we store this user name and password in a text file on the application server. JSP application uses it for the database accessing.
I am wondering whether there is a way to hide the user name/password.
Thanks.
April 29, 2003 - 9:01 pm UTC
i guess you would encrypt it and decrypt it in java -- but then you have this whole key management thing...
hierarchical user model
A reader, July 09, 2003 - 7:26 pm UTC
thank you Tom for your great service!
Our requirement is to have a user model where we
have a set of application users - we use database users
internally (with roles etc). However, we also need to
assign our own privileges (such as add/delete a particular
logical application entity). These privileges result in
actions performed at applciation level. Also
the entities managed can be grouped - you could then assign
a privilege to group level instead of each individual
entity. users could be given access to a group of
entities (with varying privileges - some db based and
some application defined privileges.)
Current design builds these application
privileges on top of Oracle user accounts (using application logic.) Is that the best way or is there an alternative?)
Thank you.
July 09, 2003 - 8:27 pm UTC
have you looked at fine grained access control via dbms_rls?
yes we have looked at FGAC
A reader, July 10, 2003 - 12:16 pm UTC
and we are using it. However the way we are using it
is that we have defined it on one central table - the
central entity that are to be manipulated by the end users.
We restrict the user from looking at that central entity
in UI by FGAC. Also, any entity that depends on that
entity has to do a join with the central entity table
for the FGAC to take effect (I am begining to suspect
this may not be a good approach?)
I think what you are implying is that we can use FGAC
to create policies - each of which translates to a privilege, is that right? So for example, if a privilege
translates to deleting a record from a table of a particular type then you set up a policy attached to a
user (or a database role) to that effect, right?
So can we create a privilege for any application defined action (say defined in a plsql package procedure) as well
then? What are the kinds of actions that we can be restricted using FGAC.
Thank you!!!
July 10, 2003 - 1:30 pm UTC
yes, that is what I was getting at -- that you can implement with FGAC your policies to allow or not deletion of specific records/groups of records.
<quote>
So can we create a privilege for any application defined action (say defined in
a plsql package procedure) as well
then?
</quote>
didn't quite understand what that was getting at.
you can restrict all DML via FGAC.
thanx - this is what I meant
A reader, July 10, 2003 - 2:03 pm UTC
"So can we create a privilege for any application defined action (say defined in a plsql package procedure) as well
then?"
What I meant was - say the "action" that you want to
restrict is a complicated one (involving some dmls,
some selects etc) and is written in a plsql package.
From what I understand, FGAC does not have a way of
restricting role1 to execute that plsql procedure.
O well - may be this can be done by standard Oracle
role/privilege (say execute on plsql procedure)?:-)
So in summary:
If the app wants to restrict a user or a group of users
from doing a select or a dml on a table - we should use
FGAC.
Let us say, we have a central entity (or a table) (called
central_entity. All entities derive their privileges
from their actions defined on the central_entity or a
group of central entity central_entity_group)
Now, how would you give appropriate privileges at the
central_entity level as well as central_entity_group levels. (central_entity_group is an application defined
concept).
Sorry for the rambling!
Thank you!!
PS : to all readers
--------------
We should all pool money to give Tom a gift for
the tremendous amount of service (can not be valued
by me at least!) that you have given (and are giving)
to us. So what would you like, tom? I am serious!!
July 10, 2003 - 5:15 pm UTC
<quote>
If the app wants to restrict a user or a group of users
from doing a select or a dml on a table - we should use
FGAC
</quote>
yes....
for the other two paragraphs after that -- it is a matter of design. You will be using SQL to restrict what they can see with each DML statement (and hence what they can therefore modify). So, you would design this "privs" table for maximum access performance -- at the cost of insertability into it (eg: you give and take away privs lots lots less often then you look them up - opimize data retrieval at the expense of modification for this).....
excellent, btw
A reader, July 10, 2003 - 5:37 pm UTC
what gift do you want?:)
reg your answers above...
A reader, October 16, 2004 - 11:09 am UTC
"Lets see what you lose with the single user
o all auditing
o all grants
o roles
o any ability to reuse this stuff in the future when the next great programming
paradigm comes along (cause the security is bound in the middle tier now)
o you would have to rewrite your code that already exists to fit into this
single user model
o you rewrite the security you already bought and paid for."
I agree with you that single user for all app users
is not a good idea. I do have some comments /questions
though..
. all auditing
as far as auditing goes, would not a client identifier
combined with a pl/sql package that sets user context
be enough to tell you which end user is doing what?
since client ids get audited - this should work as far
as user authentication goes.
. all grants
Do you mean that this user would have a union
of all grants that all users need on db objects which
violates "least privilege" prinipal?
. all roles
Once again, one user means a fixed set of roles I believe
- or can we dynamically enable disable roles at run time?
Even if you can dynamically enable/disable roles that
would be done at the application level. At the database
level, you would still be insecure. Is this what
you meant?
I definitely agree with the last two comments.
What do you think of the following alternatives - pros and
cons.
Alternative 1:
Create as many users as the number of different roles
exist for your app (e.g. CLERK,SALESMAN etc.)
Basically, your app needs to ultimately execute differnt
sets of Pl/SQl procedures (assuming that is where all data
related logic is).
Use client ids and PL/SQL package based methods to
set the "real" identity of the user in logon triggers.
client ids would get audited - since we used logon triggers
the security is being controlled at db level - hence is
applicable to any different application.
Use proxy authentication for each of the above users.
Alternative 2.
Everything the same as alternative 1, except each end user has a distinct db user. Instead of mapping scott, blake
to CLERK and KING to SALESMAN, we have users scott,blake
as role CLERK and user king as role SALESMAN.
My thinking is that alternative 2 is the better one since
in this case the auditing is naturally there without any
"client id" related logic. Note that you may still
be populating client ids to log other user info such
as IP address etc.
Sorry for the rambling and thank you for this great
one-stop site.
Thank you.
database user.
October 16, 2004 - 12:45 pm UTC
o do you actually set the client_identifier -- do your applications do that. can i audit by "individuals" using that or is it all or nothing. The auditing becomes very much "do it yourself" with a single account for everyone. client identifier can help (if you trust it -- it is easily setable by *anyone* to *any value*).
o totally violates the least principle concept. the single account has ALL privs and is responsible for making sure they are invoked at the proper time. (this is why SQL injection is such a heinous -- yet easy exploit -- the end user is running code as "super user" and super user has no clue what is going on anymore without bind variables...)
o there are application roles, there are password protected roles, there are different roles for different users and so on.
goto otn.oracle.com and search for
Proxy Authentication
in the docs -- alternative 3, similar to 2, but with many of the aspects of "a single user" -- but not the drawbacks.
ok...
A reader, October 16, 2004 - 12:05 pm UTC
"Once again, one user means a fixed set of roles I believe
- or can we dynamically enable disable roles at run time?"
Yes we can as I found out - by using "set role"..
thanx!
A reader, October 16, 2004 - 1:02 pm UTC
"do you actually set the client_identifier -- do your applications do that.
can i audit by "individuals" using that or is it all or nothing. The auditing
becomes very much "do it yourself" with a single account for everyone. client
identifier can help (if you trust it -- it is easily setable by *anyone* to *any
value*)."
Agreed - I am aware of the last drawback of client ids.
You can augment the security of client ids by
combining them with pl/sql package based checks (on sys_context). Still more cumbersome than having
actual db users in the database for each end user.
"o there are application roles, there are password protected roles, there are
different roles for different users and so on.
goto otn.oracle.com and search for
Proxy Authentication"
I did mention proxy auth in both alternatives (1 and 2 )
above. Alternative 1 has as many users as the "roles"
with client ids being used for identifying real
end user.
Alternative 2 has as many db users as the end users.
Both use proxy authentication. Given that out of
the two alternatives what are the pros and cons of
each?
thank you.
October 16, 2004 - 1:21 pm UTC
sorry -- missed the proxy auth part at the bottom.
#2, each user has their own true identity. end to end security is possible.
you need not use database users necessarily, enterprise users could be used (stored centrally, managed centrally). Effective Oracle security by design (knox/oraclepress 2004) writes about this.
thank you
A reader, October 16, 2004 - 1:41 pm UTC
"2, each user has their own true identity. end to end security is possible."
Is there a concrete example of this advantage? If there
is a situation where a privilege to a db user that
correponds to an end user has to be granted directly
without a role, then this may be an advantage. Since
in the first alternative you dont have a db user
for each end user, you have to assume that the user
accounts corresponding to roles are good enough.
Yes - enterprise users is another alternative - still
trying to understand that one - may not be always
feasible since it requires an LDAP server (OID)
from what I understand.
October 16, 2004 - 3:33 pm UTC
you are kidding right -- are not the concrete advantages of knowing at every step of the way "who is who"
think auditing
think privileges
think access control
think authorization
think least privileges
think "less code I have to write"
you can use EU's if you like, but it is certainly not a pre-requisite. But look at it this way -- if you are big enough that creating db accounts for everyone isn't feasible or is an admin issue -- then you are already larger than large enough to be using a centralized directory.
How can you see how they're connecte?
Bob, September 29, 2005 - 10:31 am UTC
SQL> alter user bobtest grant connect through rmaggio;
User altered.
SQL> connect bobtest/bobtest@test
ERROR:
ORA-01045: user BOBTEST lacks CREATE SESSION privilege; logon denied
>>> So he (bobtest) doesn't inherit rmaggio's connect privs?
SQL> connect rmaggio@test
Enter password: *********
Connected.
SQL> grant create session to bobtest;
Grant succeeded.
SQL> connect bobtest/bobtest@test
Connected.
SQL> select * from rmaggio.t;
A
My First String
Another String*
Still An^other String
Hello
SQL> connect rmaggio@test
Enter password: *********
Connected.
SQL> SELECT db_user,os_user, userhost, policy_name
2 FROM dba_fga_audit_trail;
DB_USER OS_USER USERHOST POLICY_NAME
RMAGGIO rmaggio WORKGROUP\RENRTP_RMAGGIO1 T_ACCESS
BOBTEST rmaggio WORKGROUP\RENRTP_RMAGGIO1 T_ACCESS
BOBTEST rmaggio WORKGROUP\RENRTP_RMAGGIO1 T_ACCESS
Where can I see that bobtest is proxied as rmaggio? How is that audited?
September 30, 2005 - 7:50 am UTC
I'm not sure what you are trying to show here?
the connect through is for proxy authentication - so that bobtest can log in as HIMSELF (needs create session for that) and then "become rmaggio" without having to know rmaggio's password.
bobtest doesn't inherit anything from rmaggio - bobtest rather has the ability to say "become rmaggio".
You just have two users here -- the connect through grant isn't relevant to this example?
bobtest was never proxied - you need to use OCI or Java to do that.
NT domain auth
Mark, October 13, 2005 - 4:53 pm UTC
We are evaluating using the PL/SQL module for a somewhat simple internal web app. Most of our internal apps are IIS. They pass the network login info to the database to manage the security. The user does not have to login as they are already authenticated in the domain.
Is there a way using the Apache server and PL/SQL module to read the users Network login? I can give them a Username/password prompt and use LDAP to verify, but I would like to bypass that part.
Thanks.
October 13, 2005 - 9:03 pm UTC
sorry - i don't know off the top of my head (don't really "do" windows...)
Mark, October 13, 2005 - 4:54 pm UTC
Sorry.. DB is 9.2.0.7
Thanks..
fgac, single sign-on
A reader, January 16, 2006 - 9:22 am UTC
Hi
We have a Forms application which uses FGAC, the users are database accounts.
We are consolidating all our applications using Oracle Single Sign-on Server, I have doubts regarding how to migrate the Forms application. It seems that with SSO we cannot have database accounts, instead the users need to be migrated to the LDAP (Oracle Internet Directory), if that is the case we will be loosing all pur FGAC functionalities. I am not sure if my understanding about Single Sign-On Server is correct or it is still possible to use database accounts?
January 16, 2006 - 9:49 am UTC
you can and still will have "database users" with single sign on.
but even if you didn't have database users for whatever reason, you don't lose the "identity", SSO still gives you an "identity" and FGAC is based on "who is there", you'd still be able to return the proper predicate because you would still know "who is there"
who will be the user
A reader, January 16, 2006 - 10:02 am UTC
Hi
Right now my FGAC is using USER as the predicate, with SSO what will be the predicate. The problems arises for me with SSO. I have another application which uses Resource Manager and it´s based again on USER! I willhave to reconfigure all :-(
authentication
sam, June 20, 2006 - 10:33 pm UTC
Tom:
1. Why you do not like to have the app do the authentication as you state above? What is the advantage of creating hundreds of oracle user accounts?
2. If you show user a login page on his browser and then create an account in the database on the fly (exec immediate create user), how are you going to connect to log this user into the database using mod_plsql. For each account you need a DAD. Are you going to create hundreds of DADS? It does not make sense!
June 21, 2006 - 4:32 pm UTC
1) it is called "wheel reinvention" and I don't like "reinventing wheels". we have many good wheels out there.
what is the DISADVANTAGE of hundreds of database accounts?
Some advantages (obvious ones)
o i have grant
o i have revoke
o i have drop user
o i have create user
o i have the ability to audit you
o i have the ability to do different things because the database knows you
very short list, but can get longer.
2) DAD's can either
a) have a username password associated with them
b) OR NOT
you would go with the OR NOT after you created their account. So, use the dad that has a user/password to create their account (and I of course assume you must be emailing them their password using a valid email account right - else, what is the point of accounts in the first place....).
And have your application that requires authentication use the dad that does not.
So, "two", not hundreds.
authentication
mo, June 21, 2006 - 6:07 pm UTC
Tom:
That is pretty good idea. I did not think about it.
So basically you have 4 ways of authentication:
1. via application table
2. via apache http server
3. via oracle database accounts
4. via active directory or OID
It seems your best choice is 3 and then 2. Correct?
2. If you do it via active directory or OID, do you bascially let the application database query the active server database to check if the name/password are there, or you do it at the http server and directory server first.
June 22, 2006 - 11:51 am UTC
there are more than 4, you are limited simply by your imagination.
It depends on your NEEDS which is best.
authentication
sam, July 20, 2006 - 9:18 pm UTC
Tom:
I undesrtand that 9IAS includes:
1. apache 2.0 http server
2. mod_plsql
Would I still be able to have a (mod_plsql application) system use apache basic authentication and access control (.htaccess) before a user can get to the application? Would I be able to edit the apache files as usual under 9IAS?
2. Currently I use application user/passwd table for user authentication. Do you see any benefit of moving the user authentication to the apache web server instead of form-based? My impression is that there is no benefit for access control for the data in the database, however it might work better to control access to directories/subdirectories on the unix file system as building this functionality in the database is cumbersome.
July 22, 2006 - 5:33 pm UTC
Actually - 9i the "database" includes that, the application have them and a "bit more" (like forms, reports, webcache, discoverer, etc etc...)
you can edit the apache config files, as it is just apache - yes.
why don't you see any benefit from having users log into the database as themselves? You don't see the benefit in granting, auditing, creating accounts and so on?
authentication
sam, July 22, 2006 - 10:47 pm UTC
Tom:
We can certainly create hundreds of accounts in oracle instead of using an application user table but:
1. How can you store other info for that user as last_name, first name, phone, email, etc. Do you have to create another application user_Table for that?
2. Don't you have to manage the account privileges using SQL*plus? I dont thing there is an easy web interface like when you do it using the app?
3. How does web authentication using oracle accounts work? you present an HTML form and let him enter userid/password and then submit that using a predefined oracle account with the DAD, verify that his oracle account is valid and then log him in to the app main menu using how the userid/password he provided.
4. How can creating oacle accounts help with securing files on the apache web server. Can you do that? My understanding is that you have to use apache security for that.
July 23, 2006 - 8:55 am UTC
1) sure (or use a directory - centralize all of your users for your entire "enterprise")
2) there are thousands of tools - from the free Enterprise Manager that comes with the database on up and out. Thousands of them.
3) typically via basic authentication, the brower pops up a window to get the credentials.
4) it cannot, not unless you write something to return the files instead of apache. If I have something that needs securing, the last place I put it is the file system. I put it into the database.
authentication
sam, July 23, 2006 - 10:29 pm UTC
Tom:
1. I am interested in the directory solution you proposed. Did you mean Oracle Internet Directory and how/where you set this up (browser->http server->mo_plsql->database)? any docs that explain how to do this.
2. I guess using a directory means you do not need to create hundreds of oracle accounts. correct?
As developers, we do not have DBA access over database and if we want to create oracle accounts this might mean moving account management to client which is something we want to avoid.
October 02, 2008 - 11:11 am UTC
you have to create the accounts regardless. using a centralized directory service makes it so you have to create them once.
suggest you jump on the APEX (htmldb) forum on otn.oracle.com - there you can ask about using apex (mod_plsql based development environment) with single sign on (SSO).
</code>
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b16373/sec.htm <code>
OS authentication
Tony, August 18, 2006 - 4:37 pm UTC
Hi Tom,
I got a bunch of clients each having several crons. The password of the oracle user has to be changed every quarter.
I don't want to include hard-code pass in the cron scripts, is there anyway I can access database without providing password? For example, as OS user oracle, I would like
somthing silimiar as sqlplus " / as sysdba" instead of sqlplus -s user/pass <<EOC in my script.
Thanks for your reply
August 18, 2006 - 4:41 pm UTC
I'm confused.
That is exactly what we answered above?? In the original question. Use OS authenticated accounts.
Database but all users are locked
Dawar, December 15, 2006 - 5:44 pm UTC
Tom,
1)
We have a database in our network but all users are locked.
No user can access.
Do we need security for the DB?
If yes, why and what?
2)
We have a database in our network but all users are locked except sys.
Do we need security for the DB?
If yes, why and what?
cheers,
Dawar
December 16, 2006 - 5:51 pm UTC
1) eh?
my car won't start - why not?
2) see #1
user authentications
A reader, September 21, 2008 - 2:06 pm UTC
Tom:
Which most do you prefer and when do you think each is suitable:
a) database authentication. one db account per user.
b) middle tier authentications. One central repository for all users and which product you would use here ie kerberos, OID, etc.
c) application accounts. i know you do not like this one and it is not useful
September 21, 2008 - 2:27 pm UTC
A and B do not have to be separate and distinct. So, I'd go for A and B
Being that I am here at Oracle - it would be a ldap based repository and you can use enterprise users in the database to authenticate to the database or to a middle tier application.
authentication
sam, September 21, 2008 - 4:18 pm UTC
Tom:
Not sure what you mean. Let us say you are a user for an application:
would your useird be in LDAP in the middle tier and that would be used to authenticate the client.
AND your userid will be in the backend database and that would be used to authenticate the middle tier to connect to DB.
If this is right is not this a lot of work to develop/maintain? You have two sets of authentications accounts.
2. Is the n tier authentication a middle tier authentication that uses LDAP or oracle internet directory or kerberos, etc. and it that what supports a SIngle Sign on.
September 22, 2008 - 4:57 pm UTC
Do you want to do that, that of course depends, do you have the need for centralized users. Are you that large. Do you have the infrastructure to support it. Is it part of your companies strategic direction.
I don't know what you mean by "two sets of authentication records". You use LDAP to have *one* place to authenticate, *one* place to authorize. LDAP removes the "two" from the equation.
N-Tier can use database users or enterprise (LDAP users) - it isn't either or.
@ sam (Enterprise User Security)
A reader, September 23, 2008 - 8:44 am UTC
First, let's switch off this bold
That's better...
Now, Sam - you should read up on enterprise security; basically what happens is that the database inquires the LDAP server to find out if you're entitled to connect to the instance, whether you are already autheicated, and what roles you have within this instance.
Roles and access to instances can be maintained centrally, for many instances at the time - so there's an advantage in that.
Setting it all up may be tedious and challanging, but don't we all need challanges? Besides - it's a one time job.
authentication
sam, September 23, 2008 - 10:57 pm UTC
When you use LDAP for authentication would the application still log into the database using one DB account for everyone or each user will login in with a separate DB account.
Trying to find good article or book on different setups for user authentication.
September 24, 2008 - 7:02 pm UTC
what would you like it to do.
You could do either OR you could use n-tier proxy authentication...
authentication
A reader, September 29, 2008 - 11:07 am UTC
Tom:
reading the oracle application security docs, it seems that if you have a limited number of users (client/server) then you can setup a db user account for each user and each will have a unique session/connection.
For web apps though where there might be thousands of users, it is much easier to do middle tier authentication (LDAP) or application accounts (one big user model) so that sessions can be shared.
Is this correct and is this how you would design it.
September 29, 2008 - 11:58 am UTC
is this correct?
yes
no
maybe
it depends
ldap is not middle tier authentication. ldap is a repository, we can authenticate and authorize against it - either in the middle tier or in the database itself. Middle tier authorization would simply be "we do the authorization in the middle tier" - it means nothing beyond that. It is not tied to a particular technology or approach. and LDAP is not exclusive to being used in the middle tier.
is this how I would design it?
yes
no
maybe
it depends
(I've been really consistent that way - "it depends" - only you can decide what you need based on your requirements - of which only you really know in total)
even if you have thousands of users, you could use database accounts (simple create user) if you wanted. Or you could use enterprise users (global user accounts centrally managed in an ldap repository) or you could do it yourself (highly not recommended) in the middle tier - whatever.
authentication
A reader, October 01, 2008 - 11:09 pm UTC
Tom:
Can you do middle tier authentication even using mod_plsql application. There is no program in the middle tier unless we write some JAVA stuff I guess and store it in the application server.
How would that work. The login info goes to web server who sends it to LDAP server and then if it was valid sends request to MOD_PLSSQL or reject it and sends it back to browser.
In your opinion what is the most flexible and easy to manage authentication mechanism for mod_plsql web application.
October 02, 2008 - 7:09 am UTC
mod_plsql is the 'middle tier', but it basically runs in the database.
You can use basic authentication (web servers do that)
You can use APEX and have it authenticate in many ways - but in the database.
... In your opinion what is the most flexible and easy to manage authentication
mechanism for mod_plsql web application. ....
use APEX.
http://apex.oracle.com/
user authenticate
A reader, October 02, 2008 - 10:13 am UTC
Tom:
We do not want to convert the pl/sql Web toolkit application to APEX. Can i still do only the authentication using APEX?
Also, I beleive APEX runs in database and not middle tier.
Is not it using database accounts too. So basically it is providing the GUI for managing the internal database passwords.
October 02, 2008 - 11:13 am UTC
I sort of clearly said it ran in the database:
...
mod_plsql is the 'middle tier', but it basically runs in the database.
You can use basic authentication (web servers do that)
You can use APEX and have it authenticate in many ways - but in the database.
....
APEX can use single sign on, database accounts or your own "do it yourself" authentication.
If you are writing your own mod_plsql stuff by hand, so can you - APEX did, and therefore you can do it yourself as well.
authentication
A reader, October 09, 2008 - 12:22 am UTC
Tom:
Can you explain how you can implement n-tier authentication for mod_plsql application.
You have a DAD and your middle tier is a gateway.
How does the middle tier authenticate "Scott" who is an application user and how does user "midtier" connect to database to do stuff on behalf of scott.
Would "Scott" still be in my application table "app_users".
October 09, 2008 - 10:42 am UTC
you do not have that ability, mod_plsql would have to do it and it currently does not.
N-Tier is for when you write in the middle tier yourself, when the connection management is done by you.
authentication
A reader, October 09, 2008 - 1:21 pm UTC
Tom:
Does this mean I have two choices only with mod_plsql:
1. One database account per client user. I think this will open a dedicated connection from browser to DB since you are using a DAD that logs in witt scott/tiger. Auditing can be done by database.
2. One application account per user. Every user will be in custom application table. Custom Code will need to be written for password management and auditing.
3. LDAP authentication. Define all users in Oracle internet directory and authenticate user against that before connecting to database. This seems to be simlar to n-tier.
I know you mentioned apex before but that requires conversion of all application from pl/sql web toolkit to Apex.
Is this correct?
October 09, 2008 - 2:18 pm UTC
you listed three...
you can use single sign on, you can do custom authentication, you can use database accounts (we've had this discussion smk - over and over... all over the place)
authentication
sam, October 09, 2008 - 5:23 pm UTC
Tom:
According to David Knox there are 3 security models that map end user with database connection
a)1:1 (ok for client server with limted number of users)
b)N:M (many users connect to several schemas)
c)N:1 (many users connect using one db account)
When you have a database account per user for web apps, are not you creating a dedicated connection for each user instead of connection pool. The middle tier is sort of client machine connecting to DB with separate account each time.
With thousands of users would not you really kill the performance of the system?
Even David Knox dose not recommend to do that for web apps in his book.
October 09, 2008 - 5:33 pm UTC
.. When you have a database account per user for web apps, are not you creating a
dedicated connection for each user instead of connection pool. ...
it DEPENDS.
not necessarily. We have discussed ad nauseum things like n-tier proxy authentication. Then, that is NOT true. You can have one database account per end user and still have this connection pool (one connection shared by MANY users)
smk - what I suggest to you is to gather together all of the hundreds of places you've asked about this, put them all together, read them all.
We've talked about this sixteen billion different ways.
If you ask me generically what to do, I'm going to say "use database users". If you ask me "I'm building specifically an APEX application", I might have a different answer. If you ask me "x", I might say "y".
I can only, and have only, try to educate you on the available "things" - you seem to want me to tell you specifically what to do - in a very generic case. No one can do that - if they do - run away from them.
user
sam, October 09, 2008 - 6:36 pm UTC
Tom:
I will put all the info together. It is not that many places. maybe two or three threads. I will also complete the David knox book as it does give good ideas.
Concerning the n-tier authentication, You just told me above I can't use that since my application is pl/sql web toolkit stored procedures. It is not in the middle tier.
Now i have to find out how you can do connection pooling with one database account per user.
authentication
A reader, June 24, 2009 - 6:17 pm UTC
Tom:
What is your opinion on having a PB client application uses windows O/S account for authentication. basically not having an application password at all.
Do you like it? hate it? is it good or bad.
I know you hate application security but let us go with it for this case.
June 26, 2009 - 10:33 am UTC
this is not application security at all, this is good - this means the users actually have their own accounts in the database with their own privileges.
the OS authenticates them (just like SYSDBA is done - *we* do this for our own internal most special accounts), the database then restricts what they can do based on WHO they are.
The application - it does *nothing* to perform either authentication or authorization.
the OS and database do it.
This is a good thing.
authenitcation
A reader, June 27, 2009 - 11:59 pm UTC
Tom:
I think you misunderstood me.
We do not hae one database account per user. WE have one db account for all that application uses.
I am asking you about what you think if the password in the custom table weas removed and rely on windows for application authentication. Basically, anyone who logs to windows is assumed to be valid user and log in to application.
to me this is very weak since windows is insecure.
your ideas?
July 06, 2009 - 5:17 pm UTC
sam/smk
Ummm, you wrote:
... What is your opinion on having a PB client application uses windows O/S account for authentication. .....
No come on, that is 100% unambiguous - I didn't misunderstand you, you clearly stated "2+2 = 55" and then say I misunderstood you - it is really 4. I give up
... to me this is very weak since windows is insecure. ...
sam, you must immediately stop making things up, you do not know what you are talking about - seriously, I am very serious here.
What you describe is not "using windows OS authentication", what you describe is "Sam is making the most insecure application possible". Keyword = Sam. Nothing to do with windows, nothing to do with windows authentication (which can actually be a very secure implementation - if you actually read about what it is and use it as it is defined - not as you make it up to be defined)
My idea Sam is that your manager hire a very good, experienced mentor and you work under their tutelage
authentication
A reader, July 06, 2009 - 11:17 pm UTC
Tom:
Welcome back - i hope you enjoyed your vacation.
I do not like this idea at all. I was trying to see what you think as someone from client side wants to implement it.
Their point is that if windows authenticate user before he gets to application, why authenticate him again when he logs to application. I already explained windows is insecure O/S.
July 07, 2009 - 12:43 am UTC
Sam
what I had issue with is your terminology and blanket statements.
OS authentication against Oracle on Windows is very secure, very strong, I fully approve. It would require an account per user. You said "OS authentication", that has a very precise meaning.
What you describe is neither
a) OS authentication by a client (powerbuilder) to the database
b) secure
My question to you - how can you tell that windows authenticated anyone. That is not a prereq to run a powerbuilder application. Not at all.
Windows IS NOT AN INSECURE OS, stop saying made up stuff. You know not of what you speak.
Given these statements:
<OS>, in the hands of a professional who understands the <OS>, can be implemented in a very secure fashion.
<OS>, in the hands of someone that doesn't know <OS>, that doesn't undertand <OS>, that knows not what they are doing - will likely be implemented in a very insecure fashion.
You can replace <OS> with Unix, Linux, Windows NT, Windows XP, Windows Vista, OS/390, virtually any commercially successful OS and it will be true. So, please stop it.
authenticate
sam, July 07, 2009 - 8:52 am UTC
<<My question to you - how can you tell that windows authenticated anyone. That is not a prereq to run a powerbuilder application. Not at all. >>
You can't tell. If you think of WINDOWS = YOUR HOUSE and PB APPLICATION = YOUR ROOM
you assume that you do not a door to lock the room because the main house door is protected and has a lock on it.
Believe it or not, this idea was proposed by some senior technologist.
<<How do i know windows is insecure>>
I saw a few techies (with no account) demonstrate many ways to log in as admins, create windows accounts, reset passwords, etc very easily.
http://www.linux-watch.com/news/NS4575571479.html http://sec-soapbox.blogspot.com/2007/03/why-is-windows-insecure.html
July 07, 2009 - 6:35 pm UTC
...I saw a few techies (with no account) demonstrate many ways to log in as
admins, create windows accounts, reset passwords, etc very easily.
...
then the people running your systems are not competent.
and funny enough, the pages you point to prove that out:
... It doesn't work that way. Security is a process, not a product. ...
exactly correct, if you have competent people that know what they are doing and institute correct policies - you can have something secure. Else, you likely will not (oh wait, I'm simply repeating myself from above, already been said)
authentication
A reader, July 07, 2009 - 7:33 pm UTC
Tom:
For some reason i thought you never liked windoze and you thought Linux was more stable and secure.
Anyway, what you say is that if windows is secured correctly then it will be secure and cannot be hacked.
This CTO is saying if windows does my authenication on the PC why do i need to have a second level authentication by the application installed on that PC. It sounds a logical question? But i could not understand you when you said all you need is userid "SAM".
Did you mean is all i need is to setup a windows station with a valid userid and the user will be able to get into the application? Where is the big vulnerability issue here.
July 07, 2009 - 7:59 pm UTC
... For some reason i thought you never liked windoze and you thought Linux was
more stable and secure.
....
I've written many many many many times
http://asktom.oracle.com/Misc/collaborate09-thoughts.html#c9089971388380107828 which reads:
Go with the OS you know
Go with the OS you've mastered
Go with the OS you have skilled people in
Because - any other OS would have a steep learning curve for you - you would be less secure (because you don't know how to secure it), less available (because....), less everything - unless and until you've mastered it.
The choice of an OS is one part technically motivated and nine parts "personal".
You will never see me running windows as a server platform - for a reason....
I don't do windows.
It is really that plain and simple.
I do *nix.
As long as viable unix (like) platforms exist, I'll be doing unix.... Anyway, what you say is that if windows is secured correctly then it will be
secure and cannot be hacked. ....
probably, everything has a vunerability somewhere. So, to say "cannot be hacked" would be naive and "not responsible". You can secure it as best as you can - and monitor it, have policies in place to make it less likely to be hacked/hackable. But probably, someone with enough knowledge, with enough time, with enough motivation - probably they would be able to subvert at least part of your system, somehow, someway.
The goal is to reduce that to the smallest possible "probably"
... This CTO is saying if windows does my authenication on the PC why do i need to
have a second level authentication by the application installed on that PC. ...
so what, it authenticated you as "SAM", so what? Who is sam? is sam allowed to run this application? And what if I come into your place of business with my machine and plop it down into your network and I authenticate to my machine - am I allowed in? Is being on your network sufficient to allow access? (if the answer is "no", well, think about it)
Sam - you can answer your own question. Could you bring in a machine (your own laptop) and plug it into your network and install the pb application on it and access the data? If so, you have just gotten around "all of the security".
I bring my machine into Oracle.
I put it on the Oracle network.
I cannot run a single application until I authenticate to the "system" as a whole (we use Oracle single sign on).
Once I'm signed on - authenticated to this trusted source - so that the application can go to this trusted source and ask "who the heck is running me" - then I'm good to go.
Sounds like in your place of work, with your concept of "windows authentication", I would be able to access your database - no problem, no questions - because you trust that anything on your network is a valid, trusted client.
If the PB application doesn't go to the operating system and say "who is the user and what domain are they in" - and then verify that they know this user and they know that domain - then using "windows authention" is not what you are doing.
If the PB application just "assumes" that the user must be logged in and trusted, it assumes something that might not be true.
Sort of like if some guy walks up to you tomorrow and says "Hey, ask this guy who I am - and the other guy (whom you do not know) says 'That is Tom Kyte'" - would you believe them?
That seems to be what you are proposing, we'll just trust that no one would ever try to do anything mischievous...
I'm done with this thread - I've said what I have to say and I'll say no more.
authorization
A reader, November 24, 2009 - 12:04 am UTC
Tom:
<<<for the other two paragraphs after that -- it is a matter of design. You will be using SQL to restrict what they can see with each DML statement (and hence what they can therefore modify). So, you would design this "privs" table for maximum access performance -- at the cost of insertability into it (eg: you give and take away privs lots lots less often then you look them up - opimize data
retrieval at the expense of modification for this).....>>>
What would be the best way to implement this for application accounts.?
Can it be done using one INSERT and UPDATE statement or i need 3 statements each.
I have an EMP table with 10 columns.
Users access is classified to 3 levels: A, B, C
A = update cols 1..10
B = update cols 1..5
C = update cols 6..10
User will send an XML file for update/insert.
If a user has "B" access and sends update for any column 6..10 it will be rejected (no access).
If a user has "C" and send any updates for any column 1..5 it will be rejected.
procedure save_emp ()
begin
for x in (select extractalue(colum_value,col1) col1...from xml_file)
loop
...do the access validation here
if (user = group_b) and (col6 is not null or col7 is not null....)
raise insufificent_privileges;
elsif (user = group_c) abd (col1 is not null or col3 is not null....)
raise insufficient_privileges;
end;
--if authorized to update and user is group B
if group = 'B' then
update EMP
set col1 = x.col1,
col2 = x.col2,
......
where empno = x.empno
if (sql%rowcount) = 0 then
insert into emp(empno, col1.col2....) values (x.empno, x.col1,x.col2)
end if;
elsif group = 'C' then
upadte EMP
set col6 = x.col6....
if (sql%rowcount) = 0 then
insert into emp(empno, col6,col7....) values (x.empno, x.col6,x.col7)
elsif group = 'A' then
update EMP (All)
end;
Is this OK to implement or there is better way of using one update/insert and some privs table for all groups.
can you advise?
November 24, 2009 - 10:52 am UTC
I have an EMP table with 10 columns.
Users access is classified to 3 levels: A, B, C
A = update cols 1..10
B = update cols 1..5
C = update cols 6..10
User will send an XML file for update/insert.
I would NOT do that in code, you have roles that do that for you.
if group = 'B' then
update EMP
set col1 = x.col1,
col2 = x.col2,
......
where empno = x.empno
if (sql%rowcount) = 0 then
insert into emp(empno, col1.col2....) values (x.empno, x.col1,x.col2)
end if;
really - you would have an API that would create a record if the update updated zero records? That seems very very strange indeed. Wouldn't the application that invokes know "I want to UPDATE an existing record" versus "I want to create a new record"
If you say "no", then I say "that application will be the cause of lost updates forever" (what happens when two different sessions pull up your record at about the same time and update back in the database one after the other -the second guy overwrites the first guys update without ever seeing it)
User will send an XML file for update/insert.ugh. Maybe we should both learn Esperanto to communicate as well...
Your concept of NULL must be "null is a value we never ever use, the reason is, if someone tries to update col1 to NULL but they are in group C (not allowed to update col1) - we have no way to tell them that their update failed due to insufficient privileges - we'll just ignore that column"
Have you considered that conundrum???
I don't think applications should be updating specific columns like this, you should have an API in the database that performs certain business transactions and the user is either
a) allowed to invoke that business transaction
or
b) NOT allowed to
Meaning, you wouldn't have any of this logic at all, there would not be these blind updates (which will cause lost updates), there would be well formed business transactions that are invoked and you can either DO the transaction or NOT.
authentication
A reader, November 24, 2009 - 12:01 pm UTC
Thanks Tom for the excellent points. But let me clarify I understand you:
<<I would NOT do that in code, you have roles that do that for you. >>
You mean database roles?? We only have one database account. I cannot use oracle roles.
<<Wouldn't the application that invokes know "I want to UPDATE an existing record" versus "I want to create a new record" >>
No, the visual basic application does not really know. It just calls this oracle web API via http which should be smart enough to tell whether to insert the record or update it. VB does not talk to database at all.
<<<If you say "no", then I say "that application will be the cause of lost updates forever" (what happens when two different sessions pull up your record at about the same time and update back in the database one after the other -the second guy overwrites the first guys update without ever seeing it) >>>
Excellent point. I need to add a TCN (dbms_utility.get_time) to the record and update only when client sent TCN matches TCN in the table.
<<<I don't think applications should be updating specific columns like this, you should have an API in the database that performs certain business transactions and the user is either >>>
You mean like this:
If user_group = 'B'
save_emp_group_B; (this will do all transaction work for user B)
elsif user_group = 'C'
save_emp_group_c; (this will do all the transaction work for user C)
end if;
<<<Your concept of NULL must be "null is a value we never ever use, the reason is, if someone tries to update col1 to NULL but they are in group C (not allowed to update col1) - we have no way to tell them that their update failed due to insufficient privileges - we'll just ignore that column" >>>
We can do it both ways. the oracle API can tell user you send data for something you are not allowed to update or we can update only allowed columns. I do not see any issues either way - whatever the reqt is.
November 24, 2009 - 12:24 pm UTC
... You mean database roles?? We only have one database account. I cannot use
oracle roles.
...
you could, via the SET ROLE command. but anyway.
No, the visual basic application does not really know. It just calls this
oracle web API via http which should be smart enough to tell whether to insert
the record or update it. VB does not talk to database at all.
Sam/SMK - you probably know what I'm going to say next then, don't you.
Excellent point. I need to add a TCN (dbms_utility.get_time) to the record and
update only when client sent TCN matches TCN in the table.
then, ummm, you will know whether to update an existing record or insert new, won't you.
Don't use dbms_utility, use a sequence number or systimestamp with full precision. dbms_utility rolls around and resets itself. If you use dbms_utility get time, undoubtedly someone will try to map it to a wall clock time and that'll screw things up royally when it wraps back to zero
.. You mean like this: ..
No, I mean like "the application will ask to do transaction X, if the user is allowed to do X they will"
period, no if statements.
... I do not see any issues either way - whatever the reqt is.
...
then, frankly speaking, you haven't thought this through yet. Think about it - long and hard. And then you'll see.
You wrote:
B = update cols 1..5
C = update cols 6..10
Ok, user B will set cols 1..5 to 'X' and col 6 to NULL
You cannot tell the difference between user B trying to set cols 1..5 and user B trying to set cols 1..6 in this case. You will SILENTLY succeed and user B will think "I just updated column 6"
but they won't have, you will have silently failed.
think about this please, it is an obvious issue.
locking
A reader, November 24, 2009 - 1:04 pm UTC
<<<You cannot tell the difference between user B trying to set cols 1..5 and user B trying to set cols 1..6 in this case. You will SILENTLY succeed and user B will think "I just updated column 6" >>
I do not understand.
User B is not allowed to update column 6. client should prevent him first.
When i extract data from file, I will check column 6 values.
If it is NULL then it is OK and proceeed with transaction for user B which updates col1..col5.
If there is data in column 6 he will be told that this is not valid.
I do not see any oher way to implement this reqt.
November 24, 2009 - 2:18 pm UTC
....
User B is not allowed to update column 6. client should prevent him first.
....
then why the heck are you worried, client will magically do it all
I can only look at your code snippets - YOUR CODE SNIPPETS (in plsql, not client code) are 100% counter to what you just wrote here.
... If it is NULL then it is OK and proceeed with transaction for user B which
updates col1..col5.
...
see, you are entirely missing my point.
I am user B.
I would like to set col1 = 'X', col2 = 'A', col3 = '4', col4 = NULL, col5 = 'Z', col6 = NULL
so, I send you a packet that says "do that, update those"
And you do, silently FAILING to update col6, but not telling me. All because you think I'm not telling you to update col6.
And what do I end up with? I end up setting col4 to null, but not setting col6 as I thought to null.
Nice... think about this please.
... I do not see any oher way to implement this reqt....
now, really start thinking about this - they exist, be creative, you are a software developer, this is what you get paid to do!
authentication
A reader, November 24, 2009 - 2:40 pm UTC
Ok, i thought about it .
if you are USER B and you tell me to update col6 to NULL or NOT NULL, I will reject it. You are only allowed to send/update data for col1 .. col5. I guess i need to have 3 different cursors too for extracting XML.
But how does oracle do it using ROLES. If you have ROLE_B and you set update for certain columns how do they enforce that. Is is basically the same idea. Oracle will only allows me to UPDATE col1..col5. If i inculde col6 it will tell me your update failed.
November 24, 2009 - 3:07 pm UTC
.. I will
reject it. ..
No, that is my point, you WILL NOT reject it, you will just not do it.
You think user B is saying "I don't want to touch col6". User B thinks they just asked you to update col6. You do not update it, but they are never told that.
That is the third and hopefully last time I have to write basically the same text. slow down, read critically.
think about this - user b thinks you updated col6. You did not. You did not fail, you did not reject, you silently tricked them into thinking you did something
and you did not.
authentication
A reader, November 24, 2009 - 3:06 pm UTC
Tom:
It seems i have a brain lock today. It happens sometimes.
Can you show me (very high level) in plain english or pseudo code how you structure your code in terms of validation and the transactions to implement this.
There are so many ways of doing things but i like to implement your ways usually.
Thanks,
November 24, 2009 - 3:29 pm UTC
create or replace procedure tranaction1( inputs )
as
begin
do whatever transaction 1 does
end;
/
create or replace procedure tranaction2( inputs )
as
begin
do whatever transaction 2 does
end;
/
create or replace procedure tranaction3( inputs )
as
begin
do whatever transaction 3 does
end;
/
grant execute on transaction1 to roleA
grant execute on transaction2 to roleA, roleB
grant execute on transaction3 to roleA, roleB, roleC
Now, you receive a request. You look at the user and figure out "user X has roleB as their allowed role"
So, you "set role roleB"
And then you try to execute the transaction they asked you to execute.
if they are allowed to, it runs
if they are not allowed to, it fails - the role you have set does not give that ability.
authenitcation
A reader, November 24, 2009 - 3:20 pm UTC
<<think about this - user b thinks you updated col6. You did not. You did not fail, you did not reject, you silently tricked them into thinking you did something
and you did not. >>
No ,he does not. I am going to raise an exception and tell him "UPDATE NOT ALLOWED".
You made it sound like
when others then
null;
November 24, 2009 - 3:37 pm UTC
ok, tell me how your logic does that sam
You said "if it is null, they are not trying to set it, so it'll be OK"
Look sam, all I can do is read your code and your supplied input:
...do the access validation here
if (user = group_b) and (col6 is not null or col7 is not null....)
raise insufificent_privileges;
elsif (user = group_c) abd (col1 is not null or col3 is not null....)
raise insufficient_privileges;
end;
--if authorized to update and user is group B
if group = 'B' then
update EMP
set col1 = x.col1,
col2 = x.col2,
......
where empno = x.empno
if (sql%rowcount) = 0 then
insert into emp(empno, col1.col2....) values (x.empno, x.col1,x.col2)
end if;
elsif group = 'C' then
upadte EMP
set col6 = x.col6....
if (sql%rowcount) = 0 then
insert into emp(empno, col6,col7....) values (x.empno, x.col6,x.col7)
elsif group = 'A' then
update EMP (All)
end;
Now, if I am user B and I sent you the inputs I described above
I would like to set col1 = 'X', col2 = 'A', col3 = '4', col4 = NULL, col5 = 'Z', col6 = NULL
- what would you do?
You would update col1..col5 and that is all.
No failure.
The logic equivalent of when others then null;
Like I said, slow down, read critically, work it through.