OTN Discussion Forums : Grant privileges to new user + few ...

America

Posts: 184
Registered: 01/31/07

Grant privileges to new user + few tables
Posted: Dec 24, 2007 8:14 AM

Hi all. How can I give my new user all privileges for inserting deleting records in some tables...
CREATE USER user1 IDENTIFIED BY user1

Hans Forbrich

Posts: 10,458
Registered: 03/13/99

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 8:16 AM

in response to: America

Reply

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#i2155015

America

Posts: 184
Registered: 01/31/07

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 8:37 AM

in response to: Hans Forbrich

Reply

CREATE USER nobody IDENTIFIED BY nopassword

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

// connecting as nobody
can't see table:(

sgalaxy

Posts: 4,944
Registered: 01/26/06

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 8:54 AM

in response to: America

Reply

// 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

tekicora

Posts: 858
Registered: 04/05/02

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 8:58 AM

in response to: America

Reply

// 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'

Hans Forbrich

Posts: 10,458
Registered: 03/13/99

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 9:39 AM

in response to: America

Reply

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.

America

Posts: 184
Registered: 01/31/07

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 9:40 AM

in response to: tekicora

Reply

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;

tekicora

Posts: 858
Registered: 04/05/02

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 10:09 AM

in response to: America

Reply

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'

America

Posts: 184
Registered: 01/31/07

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 10:40 AM

in response to: tekicora

Reply

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?

tekicora

Posts: 858
Registered: 04/05/02

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 10:50 AM

in response to: America

Reply

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

Hans Forbrich

Posts: 10,458
Registered: 03/13/99

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 10:55 AM

in response to: America

Reply

alter session set current_schema='SYSTEM';

America

Posts: 184
Registered: 01/31/07

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 11:01 AM

in response to: tekicora

Reply

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:(

Joseph Crofts

Posts: 235
Registered: 08/02/00

Re: Grant privileges to new user + few tables
Posted: Dec 24, 2007 1:30 PM

in response to: America

Reply

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.