Home » SQL & PL/SQL » SQL & PL/SQL » creating User & assign Role
creating User & assign Role [message #383337] Wed, 28 January 2009 06:10 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

I am creating a user in the DB as follows.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> show user
USER is "SYS"


SQL> ed
Wrote file afiedt.buf

  1* create user ravi identified by secure
SQL> /

User created.


Following are the privilages i am granting to the user.


SQL> GRANT CONNECT,RESOURCE,CREATE SESSION,CREATE DATABASE LINK,CREATE PUBLIC SYNONYM,CREATE SYNONYM
,CREATE TYPE,CREATE MATERIALIZED  VIEW,CREATE ROLE,CREATE TABLE,CREATE VIEW,CREATE PROCEDURE,CREATE 
SEQUENCE,CREATE TRIGGER, QUERY REWRITE TO RAVI;

Grant succeeded.
;

when i am trying to loggin as a user "ravi" . the db is hanging and i am not able loggin.

any help would be appreciated.

Re: creating User & assign Role [message #383342 is a reply to message #383337] Wed, 28 January 2009 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SQL> show user
USER is "SYS"

Very bad.
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Quote:
GRANT CONNECT,RESOURCE

Bad.
Don't use predefined roles, create your own.

Regards
Michel
Re: creating User & assign Role [message #383345 is a reply to message #383342] Wed, 28 January 2009 06:35 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Michel thnks for the reply.

I always struck in understanding the Roles & Users.

I created the user loggoing in as "system"

SQL> show user
USER is "SYSTEM"


I followed the below link step wise and done as mentioned by Barbara and granted the permission

http://www.orafaq.com/forum/m/377135/124572/?srch=create+user#msg_377135

, but still the same problem.

[Updated on: Wed, 28 January 2009 06:59]

Report message to a moderator

Re: creating User & assign Role [message #383355 is a reply to message #383345] Wed, 28 January 2009 07:38 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For "hanging" problem:
- check alert.log
- check trace files
- query v$session_wait and v$lock to see if the session is waiting on something
- activate trace on this session

Regards
Michel
Previous Topic: How to Create a Password?
Next Topic: BUlk insert limit with data blocks
Goto Forum:
  


Current Time: Mon Dec 05 13:02:40 CST 2016

Total time taken to generate the page: 0.08221 seconds