Hello, in working with an old system (that wasn't designed too well), I
created a user for an external program. The user was to have lookup only
access on a single view.

When I logged into the user, I found out I could query any table in the main
schema! Apparently, the old system granted access to everything via PUBLIC.

Is there a way to remove all those grants from the one user without
affecting the other users?

For example:

ACTMGR is the schema under which all the main tables and views are created.

BILL and JOE and JIM and BOB use all of the ACTMGR tables and views.

LOOKONLY was created, with just the create session privelege. It was
granted select on a single view, ACTMGR.ACCTNUMS_V. A synonym
LOOKONLY.ACCTNUMS_V was created to represent ACTMGR.ACCTNUMS. However,
LOOKONLY can see and do everything that bill/joe/jim/bob can do.

I'd like LOOKONLY to -just- be able to get at the one view, not the entire
database.

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

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? 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.

Thanks!!

-T

On Wed, 13 Aug 2003 13:49:40 -0400, "Thomas T" <T@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

Thomas T wrote:

> Hello, in working with an old system (that wasn't designed too well), I
> created a user for an external program. The user was to have lookup only
> access on a single view.
>
> When I logged into the user, I found out I could query any table in the main
> schema! Apparently, the old system granted access to everything via PUBLIC.
>
> Is there a way to remove all those grants from the one user without
> affecting the other users?
>
> For example:
>
> ACTMGR is the schema under which all the main tables and views are created.
>
> BILL and JOE and JIM and BOB use all of the ACTMGR tables and views.
>
> LOOKONLY was created, with just the create session privelege. It was
> granted select on a single view, ACTMGR.ACCTNUMS_V. A synonym
> LOOKONLY.ACCTNUMS_V was created to represent ACTMGR.ACCTNUMS. However,
> LOOKONLY can see and do everything that bill/joe/jim/bob can do.
>
> I'd like LOOKONLY to -just- be able to get at the one view, not the entire
> database.
>
> Is there a way to do something, like, revoke PUBLIC from LOOKONLY ?
>
> 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? 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.
>
> Thanks!!
>
> -T

Sybrand is correct but there is a solution.

CREATE a role such as APP_USER and assign to APP_USER the necessary privileges.

Then assign this role to each of the users that should have access.

Then remove all assignments to PUBLIC.

Be sure to test this thoroughly before implementation as there are cases where
it won't work.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

"Thomas T" <T@T> wrote in message news:3f3a866f$1@rutgers.edu...
> "Sybrand Bakker" <gooiditweg@sybrandb.demon.nl> wrote in message
> news:bd0ljvcp4v2qep69k1pgg6p6h9snnk6pu6@4ax.com...
> > On Wed, 13 Aug 2003 13:49:40 -0400, "Thomas T" <T@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