Re: How to trap the user's login program and disallow if Windows based?

Andy, we did it in a much less elegant way than Daniel Morgan's suggestion (which sounds cool); we had the same worries and fears about people running amuck with Access and SQL*PLUS and other tools, untrammeled by picky things like program logic.

But we just built a bunch of roles like:

ST_TAX_AUDITOR
FED_TAX_AUDITOR
ANNOYING_TAX_LAWYER
PROP_TAX
PAY_TAX
CFO_SEE_NOT_TOUCH and so forth, and passworded the roles and ensured that our create user scripts never gave anybody anything but CONNECT and all roles were assigned as NOT default.

The individual apps would do the role activation and supply the password. It's ugly, it's bugged me, but it worked. So anyone not logged in through one of our client apps (that would check out the ROLES assigned to the user prior to granting access, and only after they CONNECTed, and then activate the needed role(s)) would get a hearty howdy-do from SQL*PLUS, presuming they knew how to get that far in either UNIX or Windows:

SQL> select sysdate from dual;
ORA-14492: The DBA Staff will be quite annoyed with you

Access, SQL Windows, and Powerbuilder were less gracious in their reception of unexpected guests.

I had sometimes been tempted to twiddle a few things so it would look more like:

Oracle 7.2.3 blather blather blather
more blather

Welcome to SQL*PLUS!

 You aren't supposed to be using this tool, and won't be allowed to do anything while here, but a prompt follows. Please type 'exit' (without the quotes) followed by depressing the ENTER key when you are thoroughly bored. The DBA Team on X8-1234 will be more than happy to assist with any special requirements that you might have. That's what they pay us for. Systems Development can be reached on X8-2212; they do the long, tedious things; however, they are much more friendly.

SQL> I said, I was tempted. Apologies all around to developers, DBA's, end users, and anyone else I've left out.

But haven't you all just wanted to do that, just once?

RSH. "Andy" <enzoweb_at_hotmail.com> wrote in message news:8d4033cd.0206041503.4c37138e_at_posting.google.com...
> Oracle RDBMS V8.0.6 on Solaris 2.7
>
> I can remember that there is a way to trap the program name a user is
> logging into the database with, and disallow it if it is something
> like TOAD or ACCESS.
>
> Here is the problem:
>
> We have an application which connects into the database with the
> password hard-coded and which everyone knows. This application userid
> is also the schema owner. The developers also use TOAD. We can change
> the application password but it is hard coded in plain text and
> everyone knows how to see it. So the Developers have a habit of
> signing on to TOAD as the application owner and therefore have total
> access to the tables.
>
> I know that I can create a trigger at logon to run some SQL, but
>
> 1. Not sure if it works on the V806 we are running.
> 2. Don't know how to code it.
>
> So, I want it to go -
>
> login - check program name - if user = schema.owner and program !=
> program.name then kill session (and maybe issue a nasty message).
>
> Does anyone have an example of this I can use?
>
> Thanks.
Received on Tue Jun 04 2002 - 21:57:36 CDT