1

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
0

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.

-------------------------
-- GET_TABLE_PRIVS.SQL --
-------------------------
select table_name from sde.layers where owner = USER
union
select table_name from sde.table_registry where owner = USER
order by 1;

PROMPT
PROMPT
PROMPT

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

PROMPT
PROMPT

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


-------------
-- RESULTS --
-------------
SQL> @C:\TEMP\get_table_privs.sql

TABLE_NAME
------------------------------
CANADA
COUNTIES

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.