Oracle Table access - finding out user rights and replicate the same on another table

Hi,

How to find out access rights to one of my Oracle table (need to find out who can see my tables)...?

Plus, how to grant similar rights to another table...  For example, lets say I have a Table_2008 and 20 people can access that table ... I want to grant access rights to same 20 users to my new table table_2009 ...

any advice...
tparvaizAsked:
Who is Participating?
 
LindaCConnect With a Mentor Commented:
Maybe, what you are facing what this note of Oracle Metalink is mentioning:
Subject:       ORA-00900 in SQL*PLUS after Creating a New Database Instance
        Doc ID:       1033804.6       Type:       PROBLEM
        Modified Date :       09-NOV-2007       Status:       PUBLISHED


"Checked for relevance on 09-Nov-2007"


PROBLEM DESCRIPTION:
====================

After installing a new database instance, when you enter SQL*Plus, the
following error message occurs:

   ORA-00900: "Invalid SQL statement"
   // *Cause:  The statement is not recognized as a valid SQL statement.

       This error can occur if the Procedural Option is not installed and
       and a SQL statement is issued that required this option; for
       example, a CREATE PROCEDURE statement.  You can determine if the
       Procedural Option is installed by starting SQL*Plus.  If the PL/SQL
       banner is not displayed, then the option is not installed.

   // *Action: Correct the syntax or install the Procedural Option.



SOLUTION DESCRIPTION:
=====================

Run catalog.sql and catproc.sql as sys or internal.

0
 
LindaCCommented:
Run this dynamic sql:

set trimspool on;
set linesize 160
spool grant_newtable.sql
select 'grant '||privilege||' on table_2009 to ' ||grantee|| ';'  from dba_tab_privs where table_name='D_ORGANIZATIONS' ;
spool off;
@grant_newtable

0
 
LindaCCommented:
set trimspool on;
set linesize 160
spool grant_newtable.sql
select 'grant '||privilege||' on table_2009 to ' ||grantee|| ';'  from dba_tab_privs where table_name='table_2008' ;
spool off;
@grant_newtable


Corrected because d_organizations is one of my tables.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
djbaliottiCommented:
FINDING WHO HAS PERMISSIONS:
You can do this in tools like Toad.  You can also run queries on SYS.ALL_TAB_PRIVS, if you have the right permissions.

MANAGING PERMISSIONS GOING FORWARD:
I would also suggest using Oracle Roles to manage the permissions.  
You assign your 20 people to MYAPP_ROLE.
MYAPP_ROLE has permissions to TABLE_2008
When you add another table (TABLE_2009), you grant the MYAPP_ROLE permission to the new ROLE and all your people get access too because they have that role.  

This makes it easier for you to check the permissions (just check the ROLE), to add permissions for all your users, and to revoke privileges quickly.   You won't have to worry what individual permissions USER_A has - just remove the ROLE and all the permissions it had are gone from USER_A

Here is the oracle info for 10g (you didnt mention your version):
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#sthref2775
0
 
tparvaizAuthor Commented:
LindaC I am getting following error message

no rows selected.
Error at line 3
ORA-00900:
0
 
LindaCCommented:
Change plsql_optimize_level=0 at the instance or session level.

alter session set plsql_optimize_level=0;

then do the dynamic sql.
Let us know.
0
 
tparvaizAuthor Commented:
LindaC

Can you please repost your solution

Thanks
0
 
LindaCCommented:

Run this:

alter session set plsql_optimize_level=0;
set trimspool on;
set linesize 160
spool grant_newtable.sql
select 'grant '||privilege||' on table_2009 to ' ||grantee|| ';'  from dba_tab_privs where table_name='table_2008' ;
spool off;
@grant_newtable
0
 
tparvaizAuthor Commented:
still the same error...

no rows selected.
Error at line 3
ORA-00900: invalid SQL statement
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.