| Inserting into schema of currently logged in user [message #377435] |
Tue, 23 December 2008 01:01  |
coolguy01
Messages: 64
Registered: August 2006
|
Member |
|
|
Hi ,
I am trying to insert into table based on user currently logged into application. So i get the username currently logged in into a global variable called :GLOBAL.user_name. SO where ever there is a insert done into any table it should be done as
insert into :GLOBAL.user_name.table ..... to insert into table in his schema.
For example if temp user is logged in the value of :GLOBAL.username will be temp. so when he does a insert it should get inserted in temp.table. So how do i dynamically substitute the schema name depending on whichever user is logged in. Would just a insert into :GLOBAL.user_name.table substitute the value of the variable or has the value be executed and substituted doing some manipulation.
Thanks and regards
|
|
|
|
|
|
|
|
|
|
| Re: Inserting into schema of currently logged in user [message #377533 is a reply to message #377467] |
Tue, 23 December 2008 06:35  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Maybe I got it wrong, but - when user logs on, he uses credentials: username, password, database. Schema which is "valid" during this logon time is his (this user's) schema. It means that database block is based on a table which belongs to this user. Consequentially, every insert into a table will be done into this user's own table. All by default, you don't have to do anything about it.
When someone else logs on, he'll insert into his own table, and so on.
However, does this mean that EVERY user which uses this application has its own tables? Is that a good idea/design? Wouldn't you rather have a schema owner (which contains all the tables), while every other user is granted to use these tables in a certain manner (selecting, inserting, updating, ...). Data will be stored into the same table, no matter which user does that; you might, however, store username (or some other user ID) so that you'd know who has done that (or even enable database auditing).
Furthermore, you might investigate use of the Virtual Private Database.
Whatever you do, I hope it is/will be different from I understood in the first place. Because, I believe that such a design sucks (pardon my French).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
