Home » RDBMS Server » Security » how to revoke privileges for certain objects
how to revoke privileges for certain objects [message #6785] Tue, 06 May 2003 05:14 Go to next message
shawlin
Messages: 25
Registered: April 2003
Junior Member
suppose I dropped a object (in my case)
can i not retrieve back the table back.
Then how to revoke drop object permission
for the user or table.
how to do this.

Thanks and regards
shawlin
Re: how to revoke privileges for certain objects [message #6787 is a reply to message #6785] Tue, 06 May 2003 07:05 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Object permissions are dropped automatically if an object ceases to exist (to my knowledge).

MHE
Re: how to revoke privileges for certain objects [message #6789 is a reply to message #6787] Tue, 06 May 2003 07:59 Go to previous messageGo to next message
shawlin
Messages: 25
Registered: April 2003
Junior Member
No, My question is how to revoke the drop permission
for the user.Because i do not want this user
to drop the tables.

thanks and regards
shawlin
Re: how to revoke privileges for certain objects [message #6793 is a reply to message #6785] Tue, 06 May 2003 10:18 Go to previous messageGo to next message
jigar
Messages: 74
Registered: July 2002
Member
Connect as System
Revoke Drop Any Table from UserName

Hope this will work out,

Jigar
Re: how to revoke privileges for certain objects [message #6808 is a reply to message #6793] Tue, 06 May 2003 23:19 Go to previous messageGo to next message
shawlin
Messages: 25
Registered: April 2003
Junior Member
Hello Jigar,

I have a user shawlin.I tried what u suggested.
It did not worked out.
I logged as system/manager and try to
execute the command revoke drop any table from shaw
I was told this error.

system privileges not granted to shaw.

how to revoke the drop table privilege for the user shaw.

Thanks and regards
shawlin
Re: how to revoke privileges for certain objects [message #6813 is a reply to message #6789] Wed, 07 May 2003 00:44 Go to previous messageGo to next message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
Sorry, I misread your question. If I understand your problem, the situation now is as follows:
you have a user SHAW, and you don't want him to be able to drop tables in his own schema. You cannot revoke implicit database rights from a user so you must create another user with enough rights on the SHAW schema. In most cases there are few people that have access to the schema owner, since he has (implicitly) all rights on his own objects. I have found a document that might resolve your issue though:
Purpose: 
~~~~~~~~ 
The purpose of the document is to help the DBA's understand how to avoid users 
from dropping their own objects. 
This is sometimes important from users accidental errors 
Note that Oracle does not have any automated feature to prevent a user from 
dropping his own objects. 

Scope & Application: 
~~~~~~~~~~~~~~~~~~~~ 
A user automatically has all object privileges for schema objects contained in 
his or her schema. 
As such you cannot revoke any object privilege from a user for his own schema 
objects. 
Below mentioned are few steps/workarounds to prevent a user from dropping his 
own objects. 

Workarounds: 
~~~~~~~~~~~~ 
Workaround 1: 
------------- 
First you create a user for e.g. scott and have user scott login and create all 
necessary objects, and then revoke create session from user scott so that user 
can no longer login 
This will prevent user scott from logging a session, which in turn will prevent 
from dropping his own objects. 
Everyone else that will be able to drop that table will have to have the 
"drop any table " Privilege given to them. 
This can help in application design wherein your production user scott has all 
the application required objects created. 
You can then create another application user which has only selective 
application privileges been granted on production scott schema. 
This will prevent the application from directly accessing the production user 
and avoid accidental errors. 
Below mentioned are the Steps for Workaround 1. 
SQL> connect sys/****** 
SQL> create user scott identified by tiger 
default tablespace users temporary tablespace temp quota 100m on users; 
SQL> grant connect, resource to scott; 
SQL> connect scott/tiger 
SQL> create table xyz ( a number); 
SQL> create table abc ( d number); 
SQL> connect sys/****** 
SQL> revoke create session from connect; 
SQL> connect scott 
Enter password: ***** 
ERROR: 
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied 
SQL> connect sys/****** 
SQL> drop table scott.xyz; 
Later on when you create another schema you will have to grant create session 
privilege again to connect role or else the new schema will lack it and fail to 
login. 
*** This way you can prevent user scott from dropping his own objects. 

Workaround 2: 
------------- 
To make use of Product_user_profile table. 
A table that resides in the SYSTEM account. 
It provides product level security that supplements the user level security 
provided by SQL commands GRANT and REVOKE, and can be used with one's own 
applications as well as with other Oracle products. 
The table is created by running the command file pupbld.sql under the schema 
SYSTEM. 

Note: 
----- 
This will prevent you to drop any Schema Object using SQL*Plus only. 
This will not work via Svrmgrl or any other Tool. 
Note: The userid, attribute and char_value must be in uppercase. 
For Example: 
SQL> connect system/manager 
SQL> insert into product_user_profile (product,userid,attribute,char_value) 
values('SQL*Plus','SCOTT,'DROP','DISABLED'); 

PRODUCT   USERID  ATTRIBUTE  CHAR_VALUE 
--------- ------- ---------- ---------------- 
SQL*PLUS  SCOTT   DROP       <B>DISABLED</B>

If for example user SCOTT attempts to Drop a object, he would receive this error: 
SQL> drop table xyz; 
SP2-0544: invalid command: drop. 
To re-enable commands, delete the row containing the restriction. 
*** This will prevent user scott from dropping his own objects. 

Suggestion: 
----------- 
The above workarounds/steps can sometimes beneficially help from accidental users 
errors and can save drastic time and energy against recovering from such 
accidental errors. 


HTH,
MHE
Re: how to revoke privileges for certain objects [message #6819 is a reply to message #6813] Wed, 07 May 2003 04:03 Go to previous messageGo to next message
shawlin
Messages: 25
Registered: April 2003
Junior Member
BTW, the product_user_profile can also be accessed
from the my user shaw.
This table is meant for this purpose or has it got
any other purpose.

Thanks for your response.

Regards
shawlin
Re: how to revoke privileges for certain objects [message #6845 is a reply to message #6819] Thu, 08 May 2003 00:18 Go to previous message
Maaher
Messages: 7064
Registered: December 2001
Senior Member
PRODUCT_USER_PROFILE is a table in the database that the DBA can modify 
to provide product-level security. 
This table provides security for products of SQL*Plus, SQL*ReportWriter, and 
Oracle Browser.

Various Oracle products use PRODUCT_USER_PROFILE, a table in the SYSTEM 
account, to provide product-level security that supplements user roles 
and the user-level security provided by the SQL GRANT and REVOKE commands. 
DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands 
in the SQL*Plus environment on a per-user basis. SQL*Plus --not Oracle-- 
enforces this security. DBAs can even restrict access to the GRANT, REVOKE, 
and SET ROLE commands to control users' ability to change their database 
privileges. 
SQL*Plus reads restrictions from PRODUCT_USER_PROFILE when a user logs into 
SQL*Plus and maintains those restrictions for the duration of the session. 
Changes to PRODUCT_USER_PROFILE only take effect the next time the affected 
users log into SQL*Plus. 


The fact that your user has access to the table is most probably because there exists a PUBLIC SYNONYM for the table. You should, however, verify that the user doesn't have any insert/update/delete rights on it. Select privileges do no harm.

MHE
Previous Topic: Re.How to revoke privileges for certain objects
Next Topic: Grant/ Revoke permission
Goto Forum:
  


Current Time: Sun Sep 19 15:30:46 CDT 2021