Revoking All Access Rights For Users

Database
Enthusiast

Revoking All Access Rights For Users

Hi,

We're in the process of moving from our current access setup of granting access rights at user level to using Roles to control access. What we're struggling with is removing the existing rights users have (sometimes to dozens of DBs) in an easy way.

Is there a way of revoking all the accesses they currently have in one go ?

e.g.

Revoke All on "EVERYTHING" from UserA

Thanks

Alistair
5 REPLIES
Enthusiast

Re: Revoking All Access Rights For Users

There's no such stmt. An alternative: drop user; then recreate the user.
rgs
Enthusiast

Re: Revoking All Access Rights For Users

Hmm, is it not just this statement?:

REVOKE ALL PRIVILEGES ON object FROM user_name;

for exampe if user name is "sam":

REVOKE ALL PRIVILEGES ON sam FROM sam;

The only privileges it does not remove are INDEX and REFERENCES. See docmentation on REVOKE command for more specific information.
Enthusiast

Re: Revoking All Access Rights For Users



Thanks for that, we'd come to the conclusion that the only way to do it was to drop the users and recreate them.
Enthusiast

Re: Revoking All Access Rights For Users

is it possible to revoke access to everyone on a database?
Enthusiast

Re: Revoking All Access Rights For Users

REVOKE ALL PRIVILAGES ON database_name FROM ALL.

Removes only the explicit privilages