6

How do I revoke all privileges for a user in SQLPlus? I'm logged in as sysdba and I would like to revoke all privileges for a regular user.

I googled this query

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

but I don't understand what should I put for user, etc.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
R_sol
  • 67
  • 1
  • 1
  • 7
  • possible duplicate of [MySQL grant all privileges to database except one table](http://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table) – Tim C May 18 '15 at 22:15
  • whether you want to `revoke` `system privilege` or `Object privilege` ? – Nagendra Nigade May 20 '15 at 04:58

2 Answers2

4

Here answer depends on whether you want to revoke system privileges or object privileges. There is syntax change for the both.

But as you are logging through sysdba i am guessing that you want to revoke system privileges.

revoke sysdba from user_name;

Here replace user_name with your actual user. like 

revoke sysdba from nagendra;

Updated : And to revoke all system privileges we can use :

revoke all privileges from user_name

object privilege means previleges on tables procedures functions and to revoke this use :

revoke all on object_name from public

Example

revoke all on nagendra_table from public

This will remove all existing privilegs on table nagendra_table from all users

Mohit Shetty
  • 1,551
  • 8
  • 26
Nagendra Nigade
  • 866
  • 2
  • 12
  • 28
2

user is the name of the use you want to revoke the privileges from. The grant option clause is MySQL syntax, and does not exist in Oracle Enterprise Databases. So, if I'd like to revoke all of r_mat's privileges, I'd use:

REVOKE ALL PRIVILEGES FROM r_mat;
Mureinik
  • 297,002
  • 52
  • 306
  • 350