Home » RDBMS Server » Security » Revoking all permissions from user
Revoking all permissions from user [message #28846] Tue, 27 January 2004 21:37 Go to next message
jayanta Nandi
Messages: 2
Registered: December 2003
Junior Member
Dear All,

One of my user has created a table. Now after creating the table all relevant right like altering,dropping etc needs to be removed from that user. Objective is that one a table structure has been finalized the DBA wants to revoke all the rights from him, so that he cant do any alteration.

Can you let me know how to achieve this?

Thanks and regards,
jayanta
Re: Revoking all permissions from user [message #28860 is a reply to message #28846] Wed, 28 January 2004 09:00 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Note that a schema owner will be able to drop his objects anytime unless restricted by a trigger or password changed!

SQL> connect thiru/****
Connected.
SQL> create or replace trigger restrict_scott
  2  before drop or truncate or alter on scott.schema
  3  begin
  4  Raise_Application_Error(num=>-20000,msg=>'No Scott,you cant do that.See your DBA!');
  5  end;
  6  /

Trigger created.

SQL> connect scott/tiger
Connected.
SQL> create table t(x int);

Table created.

SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: No Scott,you cant do that.See your DBA!
ORA-06512: at line 2

SQL> alter table t drop column x;
alter table t drop column x
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: No Scott,you cant do that.See your DBA!
ORA-06512: at line 2



For others privileges,
Find out what privileges/roles has been assigned to that user , like this

select OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='SCOTT';

select GRANTED_ROLE from dba_role_privs where GRANTEE='SCOTT';

select PRIVILEGE from dba_sys_privs where GRANTEE='SCOTT';

and then REVOKE the priviliges that you think he/she shouldnt have..

-Thiru
Previous Topic: access control
Next Topic: How to add custome level security
Goto Forum:
  


Current Time: Thu Mar 28 04:20:23 CDT 2024