Oracle DB table prefix missing

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle DB table prefix missing

Christian González
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

pEpkey.asc (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DB table prefix missing

Jani Tiainen
Hi. 

Unfortunately there currently isn't such a feature.

This would be interesting feature to have. Surely there are quite few edge cases where prefixing would fail.

ti 17. syysk. 2019 klo 18.27 Christian González <[hidden email]> kirjoitti:
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAHn91oe6Tv7ZfFQvWXWJKv-8QQCdQKk8iuxOKKFFN8Rh0nWutQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DB table prefix missing

Jani Tiainen
In reply to this post by Christian González
...

Also in Oracle you can create synonyms (private are enough) to get around prefix.

And IIRC there is a way to set default schema for user but I that would require executing piece of SQL after connection is made.

ti 17. syysk. 2019 klo 18.27 Christian González <[hidden email]> kirjoitti:
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAHn91oepYWx31VUALj8CWbjr-hfYh-Az%3DOp1sPw3VF3Yzmri0Q%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DB table prefix missing

Stephen J. Butler
In reply to this post by Christian González
Maybe I'm misunderstanding, but tablespace has to do with physical storage of the schema, not how tables are named. What you really want is a db_schema_name or something. I think this long, old ticket is related https://code.djangoproject.com/ticket/6148

On Tue, Sep 17, 2019 at 10:27 AM Christian González <[hidden email]> wrote:
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAD4ANxVRRxfuS90H13gvBZ8JfZF9TM_vXCbU6FhMFGJ5AjpdKw%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DB table prefix missing

Jani Tiainen
You're right Oracle has quite few levels of storage definitions tablespaces just being one.


ti 17. syysk. 2019 klo 19.20 Stephen J. Butler <[hidden email]> kirjoitti:
Maybe I'm misunderstanding, but tablespace has to do with physical storage of the schema, not how tables are named. What you really want is a db_schema_name or something. I think this long, old ticket is related https://code.djangoproject.com/ticket/6148

On Tue, Sep 17, 2019 at 10:27 AM Christian González <[hidden email]> wrote:
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAD4ANxVRRxfuS90H13gvBZ8JfZF9TM_vXCbU6FhMFGJ5AjpdKw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAHn91ocSwP5dGgqRYDRfu-1ui68N0bcuqPdXZTDCndjJY_rqVA%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: Oracle DB table prefix missing

Christian González
In reply to this post by Christian González

This is exactly the ticket my issue is about, des. I'll follow the Konversation there.
Thanks Stephen.

Greets,
Christian

Am 17.09.2019 18:19 schrieb "Stephen J. Butler" <[hidden email]>:
Maybe I'm misunderstanding, but tablespace has to do with physical storage of the schema, not how tables are named. What you really want is a db_schema_name or something. I think this long, old ticket is related https://code.djangoproject.com/ticket/6148

On Tue, Sep 17, 2019 at 10:27 AM Christian González <[hidden email]> wrote:
Hi,

I don't know if this is a missing feature. But after reading the code of
django.db.backends.oracle.* I think that there is a feature missing - at
least in the Oracle driver - you can't add a generic table prefix.

I have a production Oracle database where a proprietary software does
read/write operations. I created a Django ORM on top of it using
'inspectdb', but want to make sure that Django never-ever writes
anything into that DB, not even on purpose (user accidentally clicks on
"save" in admin UI). So I created a readonly user in Oracle who has
readonly (SELECT) access to that database:

CREATE USER testreadonly IDENTIFIED BY testreadonly DEFAULT TABLESPACE
PROD_DB TEMPORARY TABLESPACE TEMPTBS2 QUOTA UNLIMITED ON PROD_DB;

and gave him rights:

GRANT connect, resource to testreadonly;   ## those are the two roles
for access here
GRANT SELECT ANY TABLE TO testreadonly;

So far, so good.

The problem is that, in SQL, as the "testreadonly" user's primary schema
is not PROD_DB, I have to prefix every table name like:

SELECT max(id) FROM PROD_DB.people

There is a more or less unanswered stackexchange question too:
https://stackoverflow.com/questions/49056244/prefix-all-table-names-django-1-11

What would be a workaround is to add the prefix to the
metaclass.table_name attribute of each model. Which sounds extremely
un-pythonic to mee (DRY?)

Ideally, I wished to have a "TABLESPACE" config field within the
DATABASE entry which reflects the TBLSPACE setting which is provided by
Django , but only within the DATABASES["TEST"] dict, or the
"db_tablespace" option as attribute within a Model. Which is also DRY.

Is there a chance that this could be worth an implementation?

Or did I get something completely wrong and it's just my missing
configuration?

thanks,

Christian



--
Dr. Christian González
https://nerdocs.at

--
You received this message because you are subscribed to the Google Groups "Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/860c153a-2efc-86fb-4649-6ce3752f6e5b%40nerdocs.at.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/django-developers/CAD4ANxVRRxfuS90H13gvBZ8JfZF9TM_vXCbU6FhMFGJ5AjpdKw%40mail.gmail.com?utm_medium&#61;email&amp;utm_source&#61;footer">https://groups.google.com/d/msgid/django-developers/CAD4ANxVRRxfuS90H13gvBZ8JfZF9TM_vXCbU6FhMFGJ5AjpdKw%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/5d8144a5.1c69fb81.638a2.040aSMTPIN_ADDED_MISSING%40gmr-mx.google.com.