Home » RDBMS Server » Security » Grant select any table, revoke specific table
Grant select any table, revoke specific table [message #170678] Thu, 04 May 2006 15:16 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
In an effort to enhance our security, I'm creating a role 'NEW_ACCESS' with limited privileges, and then assigning that role to a db user 'HU'. Here are the privileges I've assigned the role:
(07_DICTIONARY_ACCESSIBILITY has been set to FALSE)

grant select any table to NEW_ACCESS;
grant update any table to NEW_ACCESS;
grant insert any table to NEW_ACCESS;
grant delete any table to NEW_ACCESS;
grant execute any procedure to NEW_ACCESS;
grant execute any operator to NEW_ACCESS;
grant select any sequence to NEW_ACCESS;
grant create session, connect, resource to NEW_ACCESS;

I need to revoke privileges on two tables from this role:

revoke select, update, delete on DB1.AUDIT_DAY from NEW_ACCESS;
revoke select, update, delete on DB1.AUDIT_ARCHIVE from NEW_ACCESS;

I get the below error message:

*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

If I granted the ANY TABLE privileges, how come I can't revoke privileges on certain tables? Users are not allowed to query these two archive tables and I can't revoke select privileges on an object from the objects owner, 'TOM'.

Also, our application currently accesses the database via the schema's owner 'TOM'. Will I have to update our code when users connect to the database to query TOM's objects as 'HU'?
(ex. select id from tom.id_table;)

Any suggestions on how I may improve this model are certainly welcome.

Thanks!
Re: Grant select any table, revoke specific table [message #173309 is a reply to message #170678] Sun, 21 May 2006 14:09 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
I think you may be getting system privleges mixed with object privelges. Select any table is a system priv, grant select on <your table> to <your user> is an object priv.

If you grant select any table then they can select any table. I usually recommend granting select on a public synonym that is created for your table.

i.e.
create public synonym person for oradba.person;
grant select, update, insert, delete (or whatever you want them to have) on person to HU;

In order to find all your tables start a sqlplus session. Enter:
spool tbl.txt
set linesize 100
set pagesize 0
select 'grant select on ' || table_name || ' to HU;' from all_tables where owner = '<schema that created the table>' order by table_name;
Then exit sqlplus and edit the tbl.txt file and run it (or copy an paste) in another sqlplus session.
Re: Grant select any table, revoke specific table [message #182022 is a reply to message #170678] Wed, 12 July 2006 11:09 Go to previous message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
'If I granted the ANY TABLE privileges, how come I can't revoke privileges on certain tables?'
It looks like those privs where never granted to NEW_ACCESS? System grants such as 'grant select any table' are different than object grants.


'Also, our application currently accesses the database via the schema's owner 'TOM'. Will I have to update our code when users connect to the database to query TOM's objects as 'HU'?'

As HU your will need to setup select privs to see TOM's tables. As TOM, 'grant select on TOM.<tablename> to HU;'

HU will need the select table privs (grant select TOM.<table name> to HU); for each indivual table object.

[Updated on: Wed, 12 July 2006 11:28]

Report message to a moderator

Previous Topic: EXTPROC
Next Topic: obtaining security reports on users on different hosts
Goto Forum:
  


Current Time: Fri Mar 29 01:15:19 CDT 2024