Skip to Main Content
  • Questions

  • By DBA user through After Logon Triggers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Zubair.

Asked: August 01, 2017 - 5:35 pm UTC

Last updated: August 03, 2017 - 1:07 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi,

we have schema owner user named ABC, that execute our dayend jobs and as per application requirement it required a DBA role as the application is in development phase, what i needed to do is to fix that only a machine with specific IP address can connect through that user,

As the owner user had a dba role, i have created my own dba role it has same privs as dba - import full database, import and export. But the user is able to bypass the after logon trigger.. is there any other specific privs that is required to be revoked from the user ??


and Connor said...

If you're a DBA, you get to bypass that, because obviously, if I'm a DBA I could disable/drop the trigger anyway.

"as per application requirement it required a DBA role as the application is in development phase"

is not a valid statement in my opinion. Sure you might need a lot of privileges, but there is a big difference between "a lot" and "DBA".

Just give out the privs you need.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

You are right!

Zubair Hamid, August 02, 2017 - 10:10 am UTC

Yes! you are right i have asked the same question to my Application Vendors but as they are development phase they requested to have dba privs right now but i ll revoke all others which are not required.

Btw problem has been solved by revoking the administer any trigger privilege the problem has been solved!!

Just for info: is there any way to get information about the users like?
out of all assigned privileges how many privileges are used by him, so that un used privs can be revoked.

You are right!

Zubair Hamid, August 02, 2017 - 10:10 am UTC

Yes! you are right i have asked the same question to my Application Vendors but as they are development phase they requested to have dba privs right now but i ll revoke all others which are not required.

Btw problem has been solved by revoking the administer any trigger privilege the problem has been solved!!

Just for info: is there any way to get information about the users like?
out of all assigned privileges how many privileges are used by him, so that un used privs can be revoked.

Connor McDonald
August 03, 2017 - 1:07 am UTC

Yes, look at Privilege Capture. It's pretty cool

http://docs.oracle.com/database/121/DVADM/priv_analysis.htm#DVADM591

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library