|
|
|
|
|
|
Re: How to create user in Oracle ? [message #322014 is a reply to message #296831] |
Thu, 22 May 2008 02:49   |
gaganzk
Messages: 36 Registered: May 2008
|
Member |
|
|
HI i think the proper and technical answer should be like this please correct me if i made any mistake
connect username/password ---any username whose have privs to create user
create user test identified by test ----user created
but still this account can't be use as no privs is assigned till now.
so give privs as;
grant connect,resource to test
above commands create user who can connect only further if we want to use account for other purpose we need to give more privs.
Regards:
Gagan Deep Kaushal
|
|
|
Re: How to create user in Oracle ? [message #322016 is a reply to message #322014] |
Thu, 22 May 2008 03:02   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | grant connect,resource to test
|
No, never use these predefined roles, above all NEVER use RESOURCE. You can use CONNECT in 10g and up, it is equivalent to CREATE SESSION privilege.
Always define yours with the appropriate privileges.
Regards
Michel
[Updated on: Thu, 22 May 2008 03:03] Report message to a moderator
|
|
|
|
|
Re: How to create user in Oracle ? [message #322274 is a reply to message #322254] |
Thu, 22 May 2008 23:51   |
gaganzk
Messages: 36 Registered: May 2008
|
Member |
|
|
You are very right to this piont.
We are totally bookish or follow some trend that does not have base..
thanks Michel Cadot
but it give me more pleasure if you give me full detail about privs.
Regards:
Gagan deep Kaushal
|
|
|
|
Re: How to create user in Oracle ? [message #322500 is a reply to message #322254] |
Fri, 23 May 2008 15:57   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Fri, 23 May 2008 06:12 | Quote: | But Michel Cadot could you please clear me more that when i am granting RESOURCE to user what privs exactly he got ..
|
This is the reason why you MUST NOT give this role, you don't know what is inside.
Create your own role then you will know what privileges you give.
|
Back then, not all but some DBAs created a user and granted CONNECT and RESOURCE roles to this newly created user by default. For most purposes, everyone was quite happy as these predefined roles were enough for an ordinary user to start working, while DBA didn't have to think about all possible privileges one could need.
Then it was suggested not to grant CONNECT and RESOURCE any more, but create your own roles (for example, MY_CONNECT and MY_RESOURCE), make sure which privileges I've granted to them and deal with these roles from now on.
However, what's wrong with the original CONNECT and RESOURCE? Why wouldn't I know which privileges they own? Something like this:SQL> SELECT grantee, privilege, admin_option
2 FROM DBA_SYS_PRIVS
3 WHERE grantee IN ('CONNECT', 'RESOURCE')
4 ORDER BY grantee, privilege;
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TYPE NO
9 rows selected.
SQL>
Now that I know what RESOURCE is, is it acceptable to grant it to my newly created user? Of course, if everyone's happy with these privileges? I guess it is.
However, not for long. Oracle announced that both CONNECT and RESOURCE roles will be deprecated in future Oracle versions.
Therefore, all these lovely privileges which once existed in Oracle 8i are about to be gone. For your information, these were- CONNECT:
ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
- RESOURCE:
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE A conclusion? Do as Michel said - create your own role(s) and use them in the future.
|
|
|
|
|