Logon trigger to boot a user from SQLPLUS 1

[stephenwales]

I have a user account that I want to only connect from a certain application and I want to reject logins from SQL*Plus.

Is there a way to create a logon trigger to check the name of the account and the method of connection (sqlplus from Unix or SQLPLUS.EXE from Windows) and if that's true, boot them?

Thanks
Steve

 

[LKBrwnDBA]

You could look into fine grained access control

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hoinz]

... or perhaps PRODUCT_USER_PROFILE table

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842/ch10.htm#1005648

 

[LKBrwnDBA]

Good catch hoinz, maybe that would be easier to implement.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[stephenwales]

Hmmm, that's going to cover 95% of my access issues, I think.

However:

The PUP table applies only to the local database. If accessing objects on a remote database via a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.

This won't protect me from a user connecting to the Unix database via SQL*Plus for Windows.

Any other thoughts?

However, I will play with the PUP table and have a star.

Steve

 

[hoinz]

Glad it helped, thanks.

And you are right, this won't protect you from a user connecting to the database via SQL*Plus.
The link says that you may disable connect command in SQL*Plus, but unfortunately this does not apply to the initial connect.

regards