Home » SQL & PL/SQL » SQL & PL/SQL » create user (10g,32-bit)
create user [message #560632] Mon, 16 July 2012 02:52 Go to next message
sam524
Messages: 71
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
Re: create user [message #560635 is a reply to message #560632] Mon, 16 July 2012 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 19345
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?
Re: create user [message #560643 is a reply to message #560635] Mon, 16 July 2012 03:16 Go to previous messageGo to next message
sam524
Messages: 71
Registered: July 2012
Location: sydney
Member
how can i grant that some privileges to other users help me out by command please ....
Re: create user [message #560644 is a reply to message #560643] Mon, 16 July 2012 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 19345
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>
Re: create user [message #560648 is a reply to message #560644] Mon, 16 July 2012 03:29 Go to previous messageGo to next message
sam524
Messages: 71
Registered: July 2012
Location: sydney
Member
thank you very much really appreciate .....
Re: create user [message #560655 is a reply to message #560648] Mon, 16 July 2012 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
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
Re: create user [message #560659 is a reply to message #560632] Mon, 16 July 2012 04:30 Go to previous messageGo to next message
sam524
Messages: 71
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....
Re: create user [message #560661 is a reply to message #560659] Mon, 16 July 2012 04:39 Go to previous messageGo to next message
Littlefoot
Messages: 19345
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.
Re: create user [message #560662 is a reply to message #560661] Mon, 16 July 2012 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
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
Re: create user [message #560669 is a reply to message #560662] Mon, 16 July 2012 05:19 Go to previous message
sam524
Messages: 71
Registered: July 2012
Location: sydney
Member
yes i got it now....and thank you very much ....michel,littlefoot ....really appriciate your help

regards,
sam
Previous Topic: Secuence Help
Next Topic: Connect SQL and Oracle, possible?
Goto Forum:
  


Current Time: Thu Jul 31 23:21:29 CDT 2014

Total time taken to generate the page: 0.06941 seconds