Re: PUBLIC gives access to all tables- revoking that for one user?

On Wed, 13 Aug 2003 13:49:40 -0400, "Thomas T" <T_at_T> wrote:

>Is there a way to do something, like, revoke PUBLIC from LOOKONLY ?
>

No. Public is public

>Or am I going to have to grant all the users specific access... maybe grant
>the access to a role, and then revoke everything from public?
You are going to have to do that
 We're talking
>about almost 100 users here, and I'd rather make 1 user a special case then
>affect everyone else.
>
>Should I explicity revoke all selects on all tables/views from the LOOKONLY
>user?
>
>I haven't found anything other then "revoke all from public" on the 'net,
>which isn't what I want to do. I'd like to keep my job. :)

There is no reason to be afraid.
Set up the role
grant access on the individual tables to the role (you can do this by scripting)
grant the role to all users except one
revoke access from public

total impact :0

if you just
set feedback off pagesize 0 newpage 0
spool t.sql
select 'grant select, insert, update, delete on '||table_name||' to <your role>''
from user_tables;
spool off
@t.sql
spool t.sql
select 'grant <your role> to '||username from user_users
where username <> '<the one user>';
spool off
@t.sql
spool t.sql
select 'revoke all on '||table_name||' from public;' from user_tables;
spool off
@t.sql
exit

You couldn't go wrong and it shouldn't be hard work: NB: the above adhoc script doesn't include views, functions and procedures, and packages. I leave that as an exercise for you.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Aug 13 2003 - 13:28:24 CDT