Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PUBLIC gives access to all tables- revoking that for one user?
"Thomas T" <T_at_T> wrote in message news:3f3a866f$1_at_rutgers.edu...
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:bd0ljvcp4v2qep69k1pgg6p6h9snnk6pu6_at_4ax.com...
> > 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
>
>
Thanks again, Sybrand & Daniel! One more question, though- it seems easier for me to log in with the sys account, and query dba_tab_privs, and grant each access to the role as it comes along (from the privilege column), using something like
spool tooMuchToType.sql
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to
mynewrole'
from dba_tab_privs
where grantee='PUBLIC'
spool off
The only potential problem I see with the generated script is that I'd be running it as the SYS account. That's not the account that the main schema is under. I imagine that Oracle will tell me that I don't have permission to grant anything on those tables. I remember asking on the newsgroup a long time ago "isn't SYS the all-powerful see-everything grant-everything account?" and was told "no"...
But I could run down the "owner" list... do something like
set heading off/feedback 0/termout off/pagesize 0/etc
spool tooMuchToType.sql
select 'conn mainschema @ myservice'
from dual;
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to
mynewrole'
from dba_tab_privs
where grantee='PUBLIC'
and owner = 'MAINSCHEMA'
/
select 'conn testschema @ myservice'
from dual;
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to
mynewrole'
from dba_tab_privs
where grantee='PUBLIC'
and owner = 'TESTSCHEMA'
/
select 'conn mainschema @ myservice'
from dual;
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to
mynewrole2'
from dba_tab_privs
where grantee='PUBLIC'
and owner = 'DEVELOPMENTSCHEMA'
/
spool off
@tooMuchToType (I should be prompted for a password for each schema)
And then each schema owner would be able to grant privileges to other people for it's own objects. Is that why, Sybrand, you suggested using user_tables? That way I'd just log into each schema, run the query, and not have to worry about the owner field? Although I've noticed that there's only select access on 3 tables and 1 view... if I use dba_tab_privs I can narrow down the exact "original" rights to each object.
Does all this sound okay?
Also, why exactly do some of my views come up in dba_tab_privs? I thought that was just for tables...?
I wish I had this system on my test server so I could play without risk; but my test server doesn't have enough disk space! :) Maybe it's time to petition for an upgrade...
Thanks,
-Thomas Received on Mon Aug 18 2003 - 12:20:47 CDT