Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-03-29
10
Medium Priority
?
1,831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 200 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

618 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