Solved

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

Posted on 2009-03-29
10
1,812 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:tparvaiz
Comment Utility
LindaC I am getting following error message

no rows selected.
Error at line 3
ORA-00900:
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.

 
LVL 8

Expert Comment

by:LindaC
Comment Utility
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
Comment Utility
LindaC

Can you please repost your solution

Thanks
0
 
LVL 8

Expert Comment

by:LindaC
Comment Utility

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
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now