Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PUBLIC gives access to all tables- revoking that for one user?

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

From: Thomas T <T_at_T>
Date: Mon, 18 Aug 2003 13:20:47 -0400
Message-ID: <3f410af0$1@rutgers.edu>


"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

>

> Sybrand, thanks for the information- and the fast reply! I was still
> searching the 'net for ways to restrict public from one user; I think I'll
> stop looking now. And thanks for the tip on using a query to create a
> query- it's been a long time since I've had to use that. :) Thanks
again!
> I'll let you know how it goes.

>
> Luckily the only views and procedures in the system are the ones I
created;
> and there's not many of them. (They're not used by the front-end
program.)
> Looks like the designers of this system (using Oracle 7.3, by the way, to
> indicate this system's age!) never took the time to learn about Oracle.
> What I find funny is that if you delete a user (from the front-end
program),
> the user remains in Oracle. They can't log in to the front-end, but they
> can still log in through SQL*Plus. You know, little, insignificant, minor
> security issues...! I was glad I found that before a "deleted" user did.
>

> There's nothing like seeing over 900 rows after doing select * from
> dba_tab_privs where grantee='PUBLIC'... and only 200 of them were owned by
> SYS. Scripting will make that -much- easier. Thanks again!
>
> -Thomas

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US