Get User Privileges

5823
11
Jump to solution
05-29-2015 08:41 AM
CliveSwan
Occasional Contributor II

I am trying to:

1) Determine which tables to select to get the User Privileges

2) Run an arcpy script to get all FeatureClass, Table priviliges

I get an ORA-00936 error (933 ORA-00933: SQL command not properly ended)

The SQL statement is:

sql_statement =

"SELECT OWNER,OBJECT_TYPE,OBJECT_NAME  \

    FROM DBA*  \

    WHERE OWNER = 'GISADMIN' and OBJECT_TYPE = 'TABLE'"

The SQL appears to be correct???

Don't understand why it is returning this error??

Thanks,

Clive

0 Kudos
1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

USER_TAB_PRIVS is a view into the Oracle catalog. In fact, USER_TAB_PRIVS doesn't have an OBJECT_TYPE column at all:

SQL> describe user_tab_privs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                  NOT NULL VARCHAR2(30)
OWNER                                    NOT NULL VARCHAR2(30)
TABLE_NAME                               NOT NULL VARCHAR2(30)
GRANTOR                                  NOT NULL VARCHAR2(30)
PRIVILEGE                                NOT NULL VARCHAR2(40)
GRANTABLE                                          VARCHAR2(3)
HIERARCHY                                          VARCHAR2(3)

Even if there were an OBJECT_TYPE, the contents would likely be all UPPERCASE ('cause that's how Oracle rolls).  The TAB view allows you to distinguish between tables and views:

SQL> describe tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(30)
 TABTYPE                                            VARCHAR2(7)
 CLUSTERID                                          NUMBER

But the TABTYPE will contain 'TABLE' or 'VIEW' (or 'CLUSTER' or 'SYNONYM'), and never a variant of "FeatureClass".

- V

View solution in original post

11 Replies
BillDaigle
Occasional Contributor III

I use the following query to fetch privileges from 10.1 geodatabase on Oracle 11.2.04:

select * from USER_TAB_PRIVS

BlakeTerhune
MVP Regular Contributor

For multiline strings, you should just use triple single quotes so you don't have to use the backslash, that might be what's messing it up. I say to use the triple single because triple double quotes are used for docstrings.

python - Pythonic way to create a long multi-line string - Stack Overflow

EDIT:

Just noticed the asterisk (*) after the table name. I'm not a SQL expert, but I've never seen syntax like that. What is that supposed to do?

JoshuaBixby
MVP Esteemed Contributor

I agree with Blake T​, the use of an asterisk in the table name seems problematic.

I am not sure if it is a bug, but I have always run into issues using triple quotes for line continuation within the interactive Python Window in ArcGIS Desktop.  Even in other interpreters, the newline character will be preserved in the string, which may or may not be desirable depending on your specific situation.  Beyond triple quotes, strings wrapped in parentheses don't need backslashes for continuation either.  If you want to use backslashes or parentheses, each line needs to be a complete string literal.  For example,

>>> 'This is an example of ' \
... 'line continuation using backslashes'
...
'This is an example of line continuation using backslashes'
>>> ('This is an example of '
... 'line continuation using parentheses')
...
'This is an example of line continuation using parentheses'

Within the interactive Python Window in ArcGIS Desktop, but not all interactive interpreters, you will need to use Shift+Enter instead of Enter when using backslashes, likely do to the same oddity that causes issues with triple quote line continuations.  Line continutation using parentheses doesn't have this issue, using just Enter works fine.

CliveSwan
Occasional Contributor II

Hi,

Thanks to Bill, Blake and Joshua for the inputs...

This query works:

sql_statement =  ' ' .join((

    "SELECT *",

    "FROM USER_TAB_PRIVS",

    "WHERE OWNER = 'GISADMIN'",

))

The query works, but now I need to get back the table names and understand the data.

The data returned is not what I expected to get back eg:

Data name                Owner                             Permission                 ??         ??

LIBRARYGISADMINSELECTNONO

                                                                                                          >> expected YES

0 Kudos
CliveSwan
Occasional Contributor II

I get an error when I include OBJECT_TYPE = 'FeatureClass'  or OBJECT_TYPE = 'Table'    (?????)

Execute SQL Statement: SELECT * FROM USER_TAB_PRIVS WHERE OBJECT_TYPE = 'Table'

'ascii' codec can't encode character u'\uf35c' in position 52: ordinal not in range(128)

This is weird as the data is encoded as

[u'data', u'more-data', u'more-data', u'more-data', u'more-data', ]

for each row, to encode as csv???

0 Kudos
VinceAngelo
Esri Esteemed Contributor

USER_TAB_PRIVS is a view into the Oracle catalog. In fact, USER_TAB_PRIVS doesn't have an OBJECT_TYPE column at all:

SQL> describe user_tab_privs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                  NOT NULL VARCHAR2(30)
OWNER                                    NOT NULL VARCHAR2(30)
TABLE_NAME                               NOT NULL VARCHAR2(30)
GRANTOR                                  NOT NULL VARCHAR2(30)
PRIVILEGE                                NOT NULL VARCHAR2(40)
GRANTABLE                                          VARCHAR2(3)
HIERARCHY                                          VARCHAR2(3)

Even if there were an OBJECT_TYPE, the contents would likely be all UPPERCASE ('cause that's how Oracle rolls).  The TAB view allows you to distinguish between tables and views:

SQL> describe tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(30)
 TABTYPE                                            VARCHAR2(7)
 CLUSTERID                                          NUMBER

But the TABTYPE will contain 'TABLE' or 'VIEW' (or 'CLUSTER' or 'SYNONYM'), and never a variant of "FeatureClass".

- V

CliveSwan
Occasional Contributor II

HI Vince,

I was trying to use Python DESCRIBE..

I do not have access to the Oracle PL/SQL to run queries..

Seems have to use Oracle Client not Python??

0 Kudos
BlakeTerhune
MVP Regular Contributor

You can use the arcpy ArcSDESQLExecute class to execute SQL in SDE using Python. Another alternative is cx_Oracle​; you'll have to download and import a separate module, but it works on any Oracle database, not just one with SDE. In either case, you will need the Oracle client adapter installed on your computer that allows you to connect to an Oracle database.

CliveSwan
Occasional Contributor II

OK

It seems the problem was the line OWNER = 'GISADMIN'

THIS WORKS...

sql_statement =  ' '.join((

    "SELECT *",

    "FROM USER_TAB_PRIVS",

    "WHERE OWNER LIKE 'GISADMN'"

))

0 Kudos