topic Re: Get User Privileges in Python Questions

Get User Privileges

CliveSwan — Fri, 29 May 2015 15:41:03 GMT

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

Re: Get User Privileges

BillDaigle — Fri, 29 May 2015 16:34:04 GMT

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

select * from USER_TAB_PRIVS

Re: Get User Privileges

BlakeTerhune — Fri, 29 May 2015 20:13:35 GMT

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?

Re: Get User Privileges

JoshuaBixby — Sun, 12 Dec 2021 02:10:17 GMT

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.

Re: Get User Privileges

CliveSwan — Tue, 02 Jun 2015 10:42:38 GMT

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 ?? ??

LIBRARY

GISADMIN

SELECT

>> expected YES

Re: Get User Privileges

CliveSwan — Tue, 02 Jun 2015 10:43:27 GMT

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???

Re: Get User Privileges

CliveSwan — Tue, 02 Jun 2015 13:04:38 GMT

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

THIS WORKS...

sql_statement = ' '.join((

"SELECT *",

"FROM USER_TAB_PRIVS",

"WHERE OWNER LIKE 'GISADMN'"

))

Re: Get User Privileges

BillDaigle — Sun, 12 Dec 2021 02:10:20 GMT

I agree with Blake about the triple quoted strings. They are much cleaner and will allow you to copy and paste from a SQL editor like PLSQL or SQL Developer.

sql_statement =  """
                   SELECT *
                   FROM USER_TAB_PRIVS
                   WHERE OWNER LIKE 'GISADMN'
                 """

Re: Get User Privileges

VinceAngelo — Sun, 12 Dec 2021 02:10:23 GMT

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

Re: Get User Privileges

BlakeTerhune — Tue, 02 Jun 2015 15:25:43 GMT

When you have WHERE OWNER LIKE 'GISADMN' without a % wildcard, you're essentially just saying equals. For the sake of clarity, you should change it to WHERE OWNER = 'GISADMN' unless you really do want a wildcard at the beginning or end.

Re: Get User Privileges

CliveSwan — Tue, 09 Jun 2015 08:01:49 GMT

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??

Re: Get User Privileges

BlakeTerhune — Tue, 09 Jun 2015 14:01:49 GMT

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.