| ....... Issue clear the concepts plz [message #139735] |
Thu, 29 September 2005 06:50  |
orcl_dba
Messages: 84
Registered: March 2005
|
Member |
|
|
Hi. i am using forms 6i with oracle 9i rel 2 DB
I think it may be avery simple question but since i m new to development so i have to ask this and i hope that senior members will clear concept of many of us here .
The issue is that in my application i want that whenever user update any record ,
the table stores the information of the user name as well. so that later it can be identified that who was the user who updated it.
One way is to use Logging in oracle. workspace manager . thats fine..
Is there any way .. i mean can we use sys_context...( )..
i tried but it gives me error.
plz tell me the exact command if syscontext is possible.
Another thing is that should i create Database Users to acces the tables.
Like the application has some fixed tables that many users will access. for. eg, school, teacher, presence, etc, all users will acces these tables. so i have made all the table under one user .
Now should i make different DB users to use the application or should i control the login through Forms like
make another table , define fix users and passwords , put a check that if users entries for login matched the login table, then allow these menu items and disable other menu items..
I do not know how to explain my second questin but actually i just want to know which way is better , and how does the application entries for user name passworkd is check by Databae itself..
which way is better and if i make database users, then i think i should given them select, insert, or update prev on certain tables of Schools user and only select on certain tables of Schools users.. isnt it ..
Your kind comments allow me to understand the things very easily.
Thanx alot for your kindness and your precious time you ll spend here for new bied like me ..
thanx again...
|
|
|
|
| Re: ....... Issue clear the concepts plz [message #139879 is a reply to message #139735] |
Thu, 29 September 2005 23:23  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
For me, sys_context is used for 'creating' virtual databases. I think you need to read a couple of security white papers. The following are my general ideas concerning building and using Oracle databases. You create an userid for each user that is going to access the database. You give them a 'role' (use this as a main search key for future querying) and the 'role' is given various accesses to the relevant database objects. For example, a 'query user' will be assigned the 'query' role which has 'select' access, but a 'boss user' will be assigned the 'boss' role which has 'select/update/insert' access, there may be an 'adminstrator role' that has 'select/delete' access for doing archiving, etcetra.
Let the default processing that exists in the database to handle the access to the various database objects. You can still have your 'users' table in which you can keep things like a history of when someone signed on and off, etc.
David
[Updated on: Thu, 29 September 2005 23:52] Report message to a moderator |
|
|
|
| Re: ....... Issue clear the concepts plz [message #139889 is a reply to message #139735] |
Fri, 30 September 2005 00:19 |
orcl_dba
Messages: 84
Registered: March 2005
|
Member |
|
|
David, thanx for you reply.
If suppose i have 18 users altogether for the application , i can create them as DB users and assigned the Roles to them.
Then kindly tell me
if the user try to insert the record on a Form,
how can i capture the error oracle DB gives, i mean internally oracle DB also generates an erro that this user has not the previliges etc, how can i like capture it on Forms..
is it someting like On-error, if error number = 3333 etc .
is it like that .
and secondly If a DB user is logged in and has the previlige to update certain record, how can i keep a log that which record is updated by which user. i mean if i cant use sys_context, then how can i know that this record is updated by the y users.
becaseu my update triger behind the button works like
Update table
set fieldvalue = value
where condition = condition ..
then at the end i also update the username column which i have kept for chekcin the logs ..
update table
set username = ( now how can i capture the username).can i get
this using global variable which held user id.
each time user log in. and if yes then plz tell
tell me that is global variable works for all the applicaton.
where condition = condition ..
Hope i m able to clarify my question
thnx alot for your reply
|
|
|
|
| Re: ....... Issue clear the concepts plz [message #139943 is a reply to message #139889] |
Fri, 30 September 2005 02:35 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I don't know why you want to capture the error message. I don't know the number but there are threads concerning 'error' handling so have a look at them.
Oracle Designer adds 4 fields to each table for which auditing is to be done. They are called something like insert_username, update_username, insert_datetime, and update_datetime. Then in your DATABASE trigger you have code like:
When Insert
:new.insert_username := user;
:new.insert_datetime := sysdate;
When Update
:new.update_username := user;
:new.update_datetime := sysdate;
I strongly recommend that you DO NOT populate these fields in your form. Leave it to the database trigger. The 'insert' fields can be made mandatory. You do not include them in your form block.
Have a look at the get_application_property properties in the reference manual - there is CONNECT_STRING, PASSWORD, and USERNAME.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
