Re: Revoking DBA privileges

Rama Mohan (mohan_at_mnsinc.com) wrote:
: Is it true that DBA privileges can not be revoked from a user?

No

: I was told that the ALTER command would not report any errors but DBA
: privilegs will not be revoked.

I use the grant and revoke commands with no problems.

: Currently I gave temporary DBA privileges to a user. How can I get out
: of it without destroying the database

revoke dba from <user>;

There is something of a gotcha in that the effects will only be immediately effective if the user is not logged onto Oracle at the time - consider the below:

connect system/<pass>
create user scott identified by tiger;
grant create session to scott;
grant dba to scott;
connect scott/tiger
select role from session_roles; /* You will see dba */ revoke dba from scott; /* ie user revoking role from self whilst logged on */ select role from session_roles; /* You will still see dba */ connect scott/tiger /* Reconnect - reason why granted create session */ select role from session_roles; /* dba role gone */ connect system/<pass>
drop user scott;

I tested the dba privilege by creating and dropping users and it was in step with the data returned from session_roles in that scott could still create & delete users even after he had revoked dba from himself. The same effect is seen if the privilege is revoked from another session by another user with the relevant privilege.

IAP Received on Sat Mar 22 1997 - 00:00:00 CST