Link to home
Start Free TrialLog in
Avatar of dhite99
dhite99

asked on

Oracle: How to Prevent a User with Revoke Privs from Revoking "Create Session" on a Specific Userid

Hi -

We have a situation in which the application revokes the "create session" privilege as a method for preventing user access while the batch update job runs. We already know this is a bad practice, so please no comment is needed on that part.

What we have is that the application sometimes malfunctions and revokes "create session" from ALL of the userids in the database, including one that Veritas uses to assure the function of the database. This causes veritas to think that something is wrong with the database and it subsequently does a shutdown abort.

We know we can grant DBA privs to Veritas - that would effectively bypass the need for create session, however because of security reasons (pci data, etc), we are not able to grant that privilege to Veritas.

The question is this - is there any way to protect the "create session" privilege on a specific userid (veritas) such that no other user can revoke create session on it? Or is there some privilege short of DBA that we can grant to Veritas user id so that it does not have to have create session to connect?

Thanks for any help -
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

the CONNECT role already contains create session

or, if you know you don't like what the app is doing, why not modify the app?

you could put a ddl trigger in that would capture the revoke and abort with an error
Avatar of dhite99

ASKER

slightwv - a nice idea - I have to find out if that d__ app has the ability to revoke create session from roles. I presume there is no way to prevent it from revoking create session from anything, including roles?

sdstuber - thanks for the info on the connect role - as for changing the app, there are 3 companies involved in this and a lot of politics. Regrettably we are not the company that has any control, input, affect, or influence on how the app was designed and written, or even the privileges that it has been granted. We only have the responsibility to protect the database as best we can. Thanks anyway on that one.
I would also modify the revoke script to exclude VERITAS user.
>>We only have the responsibility to protect the database as best we can

An app that is compromising an HA cluster should cut right through politics.
>>I presume there is no way to prevent it from revoking create session from anything, including roles?

If it has the privileges then not really.  My guess is that the app looks for only users only 'create session' but this might be an incorrect assumption.

It is possible it might look for anything granted create session and revoke it but that would be a pretty bad thing to do since it would also revoke create session from system roles like CONNECT mentioned above.
Avatar of dhite99

ASKER


mrjoltcola: The revoke procedure is part of the application which is owned by another company.

>> An app that is compromising an HA cluster should cut right through politics.

Yes, it should, but it doesn't - the acronym FUBAR accurately describes the situation. Nonetheless, thank you for your comments.