Solved

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

Posted on 2009-03-29
10
1,819 Views
Last Modified: 2013-12-18
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...
0
Comment
Question by:tparvaiz
  • 5
  • 3
10 Comments
 
LVL 8

Expert Comment

by:LindaC
ID: 24012552
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
 
LVL 8

Expert Comment

by:LindaC
ID: 24012555
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
 

Expert Comment

by:djbaliotti
ID: 24063002
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tparvaiz
ID: 24196801
LindaC I am getting following error message

no rows selected.
Error at line 3
ORA-00900:
0
 
LVL 8

Expert Comment

by:LindaC
ID: 24197479
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
 

Author Comment

by:tparvaiz
ID: 24199768
LindaC

Can you please repost your solution

Thanks
0
 
LVL 8

Expert Comment

by:LindaC
ID: 24204007

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
 

Author Comment

by:tparvaiz
ID: 24210430
still the same error...

no rows selected.
Error at line 3
ORA-00900: invalid SQL statement
0
 
LVL 8

Accepted Solution

by:
LindaC earned 50 total points
ID: 24214137
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question