?
Solved

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

Posted on 2009-03-29
10
Medium Priority
?
1,828 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

752 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