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: Wed, 13 Aug 2003 14:41:45 -0400
Message-ID: <3f3a866f$1@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 Received on Wed Aug 13 2003 - 13:41:45 CDT

Original text of this message

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