DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Is possible to Create User with Grant in a single SQL statment

  1. #1
    Join Date
    Nov 2001
    Location
    Delhi
    Posts
    31

    Is possible to Create User with Grant in a single SQL statment

    Dear All,

    I want to know is it possible to create a user with grant option in a single SQL statemnt in Oracle 9i.

    If so pls give me SQL statment also.

    Thnaks in Advance
    Imti
    Junior DBA

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    nope
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you are asking if you can grant some privilege and at the same time with the GRANT statement you can create user, then the answer is yes.
    Code:
    SQL> select username from dba_users
      2  where username = 'TEST';
    
    no rows selected
    
    SQL> grant connect to TEST identified by test;
    
    Grant succeeded.
    
    SQL> select username from dba_users
      2  where username = 'TEST';
    
    USERNAME
    ------------------------------
    TEST
    
    SQL> 
    
    SQL> connect test/test
    Connected.
    
    SQL>
    Note that the CONNECT role used in the example is totaly arbitrary, it can be any other role or system privilege. As long as you use "... identified by ..." in the GRANT statament and the user does not yet exist, oracle will create a new user. If user does allready exist, Oracle will change his/her password with the one supplied in the ".. identified by ..." clause.

    This strange way of creating users with the GRANT statement is of no practical use realy, it is there only for backward compatibility. And this "backward compatibility" means being backward compatible with the way users were created in Oracle6 and before (sic)!
    Last edited by jmodic; 01-26-2004 at 02:46 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2000
    Posts
    295
    well, depends:

    the following command works:

    SQL> drop user test cascade;

    User dropped.

    SQL> grant dba to test identified by test;

    Grant succeeded.

    SQL> select * from V$version;

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
    PL/SQL Release 8.1.7.2.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Solaris: Version 8.1.7.2.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    SQL> drop user test;

    User dropped.

    SQL> grant select on t to test identified by test;

    ERROR at line 1:
    ORA-00933: SQL command not properly ended

  5. #5
    Join Date
    Nov 2001
    Location
    Delhi
    Posts
    31
    Dear All,

    Thanks a lot for helping me to clear my doubt.

    Regards,
    Imti
    Junior DBA

  6. #6
    Join Date
    Jul 2003
    Posts
    323

    Angry

    So a user can be created with a role and not with an object privilege ?

  7. #7
    Join Date
    Jun 2000
    Posts
    295
    RTFM:

    grant_system_privileges has "identified by clause"
    while
    grant_object_privileges does not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width