0

I want to give certain authorizations to users who connect to the database. The scenario is as follows; users will be able to assign select queries to the tables and access the tables, but only users who are admin will be able to see the create scripts. How should I follow a path for this. I want to hide the query in the picture when connected from Oracle db or when entering the DDL content of the table from the terminal. How do you suggest a way to do this?enter image description here

I gave a role to hide sql queries and assigned this role to users who I don't want to see the queries, but I couldn't hide the sql query directly from this role.

Improve this question
4
  • role privileges are ADDITIVE - so you would need to make sure the user is not granted a role that contains the privilege. You can't take away a privilege by adding a role
    – Randy
    Commented Sep 4 at 14:01
  • is there a way to hide SQL queries from users or roles instead of hide tables? I want tables to be visible but sql queries to be hidden
    – Murat Simsek
    Commented Sep 4 at 14:06
  • 1
    If you give select privs on a view, that will automatically give them the ability to see the view code in all_views. The code behind all_views is literally "show all view definitions that the user has any privs on." You can't prevent that. If that's an issue, materialize the view with a CTAS to a standalone table and give them select privs on the table, not the view.
    – Paul W
    Commented Sep 4 at 15:55
  • 1
    More fundamentally, it's unusual to try to hide view code from folks. This suggests that you may be embedding things into view code that don't belong there. There really shouldn't be anything sensitive in them. Data in tables is far more important that a SQL query that queries it.
    – Paul W
    Commented Sep 4 at 15:56

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.