1 2 Previous Next 17 Replies Latest reply on Feb 15, 2013 3:04 PM by JustinCave Go to original post
      • 15. Re: Grant Read only privilege
        Richard Harrison .
        OK - got you now.

        Unfortunately there is no 'grant select on schema' right in oracle ( though there is in sqlserver - thats a first!). Maybe it will be in 12c?

        You'll have to do what Justin suggests and possibly have addl trigger to keep the grants up to date as the schema changes.

        • 16. Re: Grant Read only privilege
          Meenakshy singh
          Hi Gurus,

          My oracle version is Oracle9i Enterprise Edition Release and Oracle9i Enterprise Edition Release - 64bit on AIX machine.

          I have create user and grant read privileges to database schemas in 2 different databases.

          ON AIX - when I am running this query select from table_name;* Users can view the records.
          Means I can acess select * from ci_acct; It works fine without schema name.

          On windows - But for other databases I am running same quey select from ebt_transaction;* its not working.It's asking me to enter select from ebt.ebt_transaction;*.
          But why do i need to enter the ebt?
          normally in our application, we won't use schemaname.tablename

          If same is quey is running in one database then why not in another database.can anyone tell me ?

          • 17. Re: Grant Read only privilege
            First, since this is a different question, you're probably better off creating a new thread rather than reopening a thread from a month ago. Lots of folks that could help you will probably see a thread with 15 replies and pass simply because they don't want to read through all the previous posts to see whether they can help you.

            Second, most likely, on one database, you (or someone else) has created a synonym (either public or private) for the object in question. If you
            CREATE PUBLIC SYNONYM table_name
              FOR schema_name.table_name
            then every user can refer to TABLE_NAME without using the schema prefix. If you
            CREATE SYNONYM table_name
              FOR schema_name.table_name
            then the current user (the owner of the synonym) can refer to TABLE_NAME without using the schema prefix. Private synonyms like this are generally preferrable to public synonyms since that allows your application to exist in a database that supports other applications without worrying about conflicting synonym names.

            1 2 Previous Next