OraFAQ Forum: SQL & PL/SQL » creating User & assign Role

Home » SQL & PL/SQL » SQL & PL/SQL » creating User & assign Role

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

creating User & assign Role [message #383337]

Wed, 28 January 2009 06:10

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.

Report message to a moderator

Re: creating User & assign Role [message #383342 is a reply to message #383337]

Wed, 28 January 2009 06:27

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: creating User & assign Role [message #383345 is a reply to message #383342]

Wed, 28 January 2009 06:35

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 09:47:57 CST 2025