OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » create user (10g,32-bit)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

create user [message #560632]

Mon, 16 July 2012 02:52

sam524
Messages: 69
Registered: July 2012
Location: sydney

Member

hi guys,

I can create user and grant,revoke,But can anyone help me out in creating a user for only one specific table for ex: samtable.......

He can only access to a specific table...he can read and write and insert,and all permissions for only that specific table....

please help me out iam new to oracle....

Thanking you

Report message to a moderator

Re: create user [message #560635 is a reply to message #560632]

Mon, 16 July 2012 02:59

Littlefoot
Messages: 16975
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SAMTABLE belongs to someone. He has ALL privileges on that table (as he is the owner). He can, then, grant some privileges to other users.

So, what is your question, really? You said that you can create users, grant and revoke privileges. What is the problem, then?

Report message to a moderator

Re: create user [message #560643 is a reply to message #560635]

Mon, 16 July 2012 03:16

sam524
Messages: 69
Registered: July 2012
Location: sydney

Member

how can i grant that some privileges to other users help me out by command please ....

Report message to a moderator

Re: create user [message #560644 is a reply to message #560643]

Mon, 16 July 2012 03:20

Littlefoot
Messages: 16975
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

GRANT.

For example: Scott creates a table called TEST. Then he grants some privileges to another user, Mike:

SQL> show user
USER is "SCOTT"
SQL>
SQL> create table test (col number);

Table created.

SQL> grant select, insert, update on test to mike;

Grant succeeded.

SQL>

Report message to a moderator

Re: create user [message #560648 is a reply to message #560644]

Mon, 16 July 2012 03:29

sam524
Messages: 69
Registered: July 2012
Location: sydney

Member

thank you very much really appreciate .....

Report message to a moderator

Re: create user [message #560655 is a reply to message #560648]

Mon, 16 July 2012 03:56

Michel Cadot
Messages: 54194
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is simple
1/ Create an account: CREATE USER ... IDENTIFIED BY ...
2/ Grant it the privilege to connect: GRANT CREATE SESSION TO ...
3/ Grant it the privilege to DML on the target table: GRANT SELECT,INSERT,DELETE,UPDATE ON ... TO ...

That's all, the user can only connect and access this table.

Regards
Michel

Report message to a moderator

Re: create user [message #560659 is a reply to message #560632]

Mon, 16 July 2012 04:30

sam524
Messages: 69
Registered: July 2012
Location: sydney

Member

create user sam default tablespace sam temporary tablespace space temp identified by sam;

grant connect,resource,create view to sam;

revoke unlimited tablespace from sam;

alter user sam quota unlimited on sam;

thats how i create users and give permissions iam i right....But for on specific table and for DML PERMISSIONS i should only give the one which u said

grant select,delete,update on xxxtable to sam;

thats how its is....

Report message to a moderator

Re: create user [message #560661 is a reply to message #560659]

Mon, 16 July 2012 04:39

Littlefoot
Messages: 16975
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

(I wouldn't create a user just like that, but OK)

Who owns the "xxxtable"? You don't have to grant any privileges on your own tables to yourself.

Report message to a moderator

Re: create user [message #560662 is a reply to message #560661]

Mon, 16 July 2012 04:47

Michel Cadot
Messages: 54194
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

thats how i create users and give permissions iam i right....

No you are wrong:
- NEVER use Oracle predefined roles, create your own.
- grant ONLY the necessary privileges no more no less.

Regards
Michel

Report message to a moderator

Re: create user [message #560669 is a reply to message #560662]

Mon, 16 July 2012 05:19

sam524
Messages: 69
Registered: July 2012
Location: sydney

Member

yes i got it now....and thank you very much ....michel,littlefoot ....really appriciate your help

regards,
sam

Report message to a moderator

Previous Topic:	Secuence Help
Next Topic:	Connect SQL and Oracle, possible?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 12:00:19 CDT 2013

Total time taken to generate the page: 0.19625 seconds