Home » RDBMS Server » Security » How to create a user which would be able to create other users ? (Oracle 11,12)
How to create a user which would be able to create other users ? [message #675410] Thu, 28 March 2019 01:15 Go to next message
a100
Messages: 24
Registered: March 2019
Junior Member
Hello all,
I need to create a user (in both Oracle 11 and 12) which has enough privileges to run the following commands:
CREATE USER {} IDENTIFIED BY "{sth}" DEFAULT TABLESPACE users QUOTA unlimited ON users
GRANT CREATE SESSION TO {}
GRANT CREATE TABLE TO {}
GRANT CREATE VIEW TO {}
GRANT CREATE SEQUENCE TO {}
and delete it
DROP USER {} CASCADE
How do I do that ?

[Updated on: Thu, 28 March 2019 01:16]

Report message to a moderator

Re: How to create a user which would be able to create other users ? [message #675411 is a reply to message #675410] Thu, 28 March 2019 01:35 Go to previous messageGo to next message
John Watson
Messages: 8076
Registered: January 2010
Location: Global Village
Senior Member
The privilege to create a user is CREATE USER. For the other privileges, you need to read up on WITH ADMIN OPTION.
Re: How to create a user which would be able to create other users ? [message #675607 is a reply to message #675410] Tue, 09 April 2019 08:40 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
So you want to know what privs you need to grant to a user to allow that user to issue certain other statements? So just look up that other statement (ie: CREATE USER) in the SQL Reference and look at the "prerquisites" section for that statement. For instance, under CREATE USER we see this:

Prerequisites

You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. Refer to GRANT for more information.

https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#i2065278
Re: How to create a user which would be able to create other users ? [message #675629 is a reply to message #675607] Wed, 10 April 2019 10:58 Go to previous messageGo to next message
Littlefoot
Messages: 21517
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I understood the question.

Connected as a privileged user (SYS in my 11gXE database), I'll create a user which will be able to create other users and grant certain privileges to them:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> create user creator identified by creator
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create user     to creator;

Grant succeeded.

SQL> grant create session  to creator with admin option;

Grant succeeded.

SQL> grant create table    to creator with admin option;

Grant succeeded.

SQL> grant create view     to creator with admin option;

Grant succeeded.

SQL> grant drop user       to creator;

Grant succeeded.

SQL>

Now, connect as newly created user and - create yet another user, grant it some privileges (CREATOR is allowed to grant) and, finally, drop that user:
SQL> connect creator/creator
Connected.
SQL> create user dropme identified by dropme;

User created.

SQL> grant create session to dropme;

Grant succeeded.

SQL> grant create table   to dropme;

Grant succeeded.

SQL> drop user dropme;

User dropped.

SQL>
Re: How to create a user which would be able to create other users ? [message #675631 is a reply to message #675629] Wed, 10 April 2019 17:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do NOT use SYS to create an account, use one with DBA role for instance.
Use SYSTEM to create the first DBA account (or better the account with a role containing the only privileges it needs to do its job).

Re: How to create a user which would be able to create other users ? [message #675673 is a reply to message #675631] Thu, 11 April 2019 08:17 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Well, it has now been a week since the OP started this thread, and we've not heard from him since his opening post. Looks like he's WORN (Write Once, Read Never).
Re: How to create a user which would be able to create other users ? [message #675677 is a reply to message #675673] Thu, 11 April 2019 08:55 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I never understand that, Ed. You'd think that feeding-back would increase your chances of getting prompt help again in the future, wouldn't you? *shrugs*
Previous Topic: How to Unwrap Wrapped PL SQL (Functions, Procedures, Packages etc)
Next Topic: TDE multiple master encryption keys within single schema.
Goto Forum:
  


Current Time: Mon Dec 09 07:09:07 CST 2019