I want to be able to see what permissions have been granted on a specific object in my Oracle geodatabase. What would my script look like?

closed as off-topic by PolyGeo Sep 12 '16 at 8:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general Information Technology, with no clear GIS component, are off-topic here, but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software) and Database Administrators (relational databases)" – PolyGeo
If this question can be reworded to fit the rules in the help center, please edit the question.

  • In what language and/or environment do you wish to accomplish this? Scripting is a different solution than a one-off property check. – Vince Oct 3 '14 at 2:01
  • Just in sql plus – geogeek Oct 3 '14 at 2:26
  • 1
    Then it's really more of a DBA question, right? dba.stackexchange.com – Vince Oct 3 '14 at 3:00
  • Yes I suppose. My apologies. – geogeek Oct 3 '14 at 4:03

Save the syntax below to a file with a .sql extension (ie: C:\TEMP\get_table_privs.sql). Then log into SQL*Plus as the owner of the table you want to see the privileges for and execute the script.

select table_name from sde.layers where owner = USER
select table_name from sde.table_registry where owner = USER
order by 1;


ACCEPT tabPrivs char PROMPT ' Enter in a table_name from the list above : '; 


col tablename for a30
col grantee for a14
col grantor for a14
col privilege for a12

set verify off
set lines 130
set pages 100

break on tablename skip 1 on grantor skip 1 on grantee skip 1

select owner||'.'||table_name tablename, grantor, grantee, privilege, grantable from 
user_tab_privs where table_name = UPPER('&tabPrivs');

set verify on

SQL> @C:\TEMP\get_table_privs.sql


2 rows selected.

 Enter in a table_name from the list above : COUNTIES

TABLENAME                      GRANTOR        GRANTEE        PRIVILEGE    GRA
------------------------------ -------------- -------------- ------------ ---
CR.COUNTIES                    CR             SDE            SELECT       NO

                                              TEST           SELECT       NO

                                              GIS            DELETE       NO
                                                             INSERT       NO
                                                             SELECT       NO
                                                             UPDATE       NO
6 rows selected.

Not the answer you're looking for? Browse other questions tagged or ask your own question.