Re: Temporary tables visible to all sessions

see notes below... your best bet is a redesign of your proposed process. "Konstantin Kudin" <konstantin_kudin_at_yahoo.com> wrote in message news:ff88eb34.0307091218.373b70a3_at_posting.google.com...
> Hi,
>
> I am curious if it is possible to create a temporary table (in Oracle
> 8.1.7) that would be visible to all sessions. Then its data would go
> away when the database is shutdown. Hopefully, due to its temporary
> nature such a table would also be writable in read-only mode.
>

how often are you going to be shutting down the database? How hard is it to just drop the tables when the database shutsdown? just create a script that drops the tables and then shuts the database down???? do you mean the data gets deleted the USER logs off, but is visible to everyone? if so just use a table and use a logoff trigger to delete the data.

> A possible usage would be to keep track of web users when each user
> is issued an ID on web logon. The ID is passed back and forth as a
> cookie or a hidden form field to authenticate the user. Stored
> procedures would validate legitimacy of requests by the ID. The IDs
> would be stored in an all session visible temporary table.
> Hypothetically if the database is opened in read-only mode, the users
> can still login from the web, get IDs, and do at least some things
> since the table with IDs is hopefully writable. Is this possible or is
> there a better way?
>

well this is a session level authentication right? why does it need to be visible to all sessions? If its authentication dont you want it to be secure.

now by 'validate legitimacy' if its for queries you can use the Virtual Private database. It just appends a where clause to your sql statements... very simple to use. no overhead. no bother.

if you mean only certain users can perform DML, then use roles. Alot of this 'validation' sounds like it should be application layer logic. are you using html? not sure how hard it is to implement business logic using that, but if its a more robust front end, it should be easy.

> I know that Oracle has "global temporary" tables, which are either
> session-specific or transaction-specific. Are these writable in
> read-only mode? There is a way to make sure that Oracle can sort in
> read-only mode and thus perform writes to a temporary tablespace.
> Would the same mechanism work with "global temporary" tables thus
> making them writable, or not?

put the tablespace in read only mode. put the tables you want to be writable in another tablespace that is not in read only mode.

>
> Thanks!
>
> Konstantin

you really should drop this and redesign your process. sounds like you did something similiar to this with another tool and want to twist oracle to do it that way. bad idea. each tool has its own pros and cons.

Sounds like a simple validation model right? ie, certain users can do certain things right? use VPD and roles. its pretty easy. now are you saying that users can 'enter passwords' to gain access to certain things... lots of ways to do that, and it sounds like you only want a limited number of accounts. Received on Thu Jul 10 2003 - 18:28:42 CDT