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
Solved! Go to Solution.
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
I use the following query to fetch privileges from 10.1 geodatabase on Oracle 11.2.04:
select * from USER_TAB_PRIVS
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?
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.
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 | NO | NO |
>> expected YES
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???
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
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??
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.
OK
It seems the problem was the line OWNER = 'GISADMIN'
THIS WORKS...
sql_statement = ' '.join((
"SELECT *",
"FROM USER_TAB_PRIVS",
"WHERE OWNER LIKE 'GISADMN'"
))