12 Replies Latest reply: Dec 24, 2007 3:30 PM by 92686 RSS

    Grant privileges  to new user + few tables

    559287
      Hi all. How can I give my new user all privileges for inserting deleting records in some tables...
      CREATE USER user1 IDENTIFIED BY user1
        • 1. Re: Grant privileges  to new user + few tables
          Hans Forbrich
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015
          • 2. Re: Grant privileges  to new user + few tables
            559287
            CREATE USER nobody IDENTIFIED BY nopassword

            GRANT ALL ON invoices TO nobody
            GRANT CREATE SESSION TO nobody;

            // connecting as nobody
            can't see table:(
            • 3. Re: Grant privileges  to new user + few tables
              sgalaxy
              // connecting as nobody
              can't see table:(

              Try...
              select * from <user_who_owns_the_table>.invoices;
              For example:
              select * from sys.invoices

              or....
              connected as user who_owns_the_table....
              create public synonym p_sym for invoices...

              and then connected as nobody:
              select * from invoices....

              But , you must have the create public synonym in order to create public synonyms....

              Greetings....
              Sim
              • 4. Re: Grant privileges  to new user + few tables
                400137
                // connecting as nobody
                can't see table:(
                How do you query the data from the table? Do you use synonym?
                Can you send an example?

                Can you see INVOICE table in ALL_TABLES when logged in as user NOBODY?
                select owner, table_name
                from all_tables
                where table_name='INVOICES'
                • 5. Re: Grant privileges  to new user + few tables
                  Hans Forbrich
                  CREATE USER nobody IDENTIFIED BY nopassword

                  GRANT ALL ON invoices TO nobody
                  GRANT CREATE SESSION TO nobody;

                  // connecting as nobody
                  can't see table:(
                  Are you sure you can not see the table? Have you told the system WHICH table you want to see.

                  SELECT * FROM my.invoices;

                  If you do not tell the system WHICH table, it has no idea which one.

                  You could also do something like

                  ALTER SESSION SET CURRENT_SCHEMA='MY';
                  SELECT * FROM INVOICES;

                  to have the system assume that unqualified table names are pointed to schema 'MY'. This is generally a MUCH better idea in the long run than using synonyms.
                  • 6. Re: Grant privileges  to new user + few tables
                    559287
                    select owner, table_name
                    from all_tables
                    where table_name='INVOICES'


                    1-SYSTEM-INVOICES

                    I thought I gave invoice to nobody by this command:
                    GRANT ALL ON Invoices TO nobody;
                    • 7. Re: Grant privileges  to new user + few tables
                      400137
                      select owner, table_name
                      from all_tables
                      where table_name='INVOICES'


                      1-SYSTEM-INVOICES

                      I thought I gave invoice to nobody by this command:
                      GRANT ALL ON Invoices TO nobody;
                      What happens when you do execute the following statement logged in as NOBODY?
                      select count(1) from system.invoices
                      With the following query (execute it when logged in as SYSTEM) you can check what privileges NOBODY has on INVOICE.
                      select grantor, grantee, table_name, privilege
                      from dba_tab_privs
                      where table_name='INVOICES'
                      and grantee='NOBODY'
                      • 8. Re: Grant privileges  to new user + few tables
                        559287
                        select count(1) from system.invoices
                        it works if I change invoices to system.invoices it works.

                        How can I call table invoices as invoices not as system.invoices?
                        • 9. Re: Grant privileges  to new user + few tables
                          400137
                          You have to create a synonym (public or private) for table INVOICES

                          http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#CNCPT711

                          Message was edited by:
                          tekicora
                          • 10. Re: Grant privileges  to new user + few tables
                            Hans Forbrich
                            alter session set current_schema='SYSTEM';
                            • 11. Re: Grant privileges  to new user + few tables
                              559287
                              thanks for advice. May be it will be better to make nobody the owner of the tables? Because when i am nobody i can't see in Oracle developer any tables
                              I tried
                              grant select, insert, update, delete on * to Alex but this does not work:(
                              • 12. Re: Grant privileges  to new user + few tables
                                92686
                                May be it will be better to make nobody the owner of the tables?
                                It's definitely a good idea to make somebody else the owner of the table rather than SYSTEM... you shouldn't really be putting your own objects in the SYSTEM schema anyway if you can help it.

                                Whether NOBODY is the best schema to use depends on what you want the table for. If this is just a test system you are playing about with then it doesn't really matter... but if you are trying to put together a proper development/test system with a plan to move into a production environment then think through what you want (and whether NOBODY is the best name for your application schema).

                                Saying that, if this is just a database you are 'playing' with to learn some Oracle then you might want to look at granting system privileges rather than object privileges.

                                GRANT SELECT ANY TABLE TO ALEX;

                                Will let ALEX select form any table (other than the data dictionary).

                                Again, though, a word of caution - if you intend to put this into a 'real' system (rather than just one you are learning on) then think it through and only grant what you need. If you go for the 'easy' system privilege now you'll find you'll always be stuck with it because you'll never be 100% certain that you can revoke it without affecting your application.