Oracle: a litte confused about privileges granted to my user

All we need is an easy explanation of the problem, so here it is.

I want to give few permission to my non-dba user on my database.
I give create session, create table, view, etc..
This query seems to confirm

SELECT privilege from USER_SYS_PRIVS;

    PRIVILEGE
    ------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE
CREATE VIEW
CREATE TABLE
CREATE SESSION

but I see I can create roles and user as not privileged user

create role mich identified by blablabla;

Role created


SQL> create user mich1 identified by blablabla;

User created

Is not too much for a simple user the permission to create user and roles?

As a DBA user I try to revoke…

SQL> revoke create user from myuser;
revoke create user from myuser
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'MYUSER'

I check if my user has dba role(I do those command as dba user)

SELECT granted_role FROM dba_role_privs where grantee='MYUSER';

GRANTED_ROLE
--------------------------------------------------------------------------------
IMP_FULL_DATABASE
RESOURCE
MICH
EXP_FULL_DATABASE
CONNECT

SQL> revoke DBA from myuser;
revoke DBA from myuser
*
ERROR at line 1:
ORA-01951: ROLE 'DBA' not granted to 'MYUSER'

I remove the roles MICH and RESOURCE, from my "unprivileged" user
but he still can create other users and roles.

Another strange thing:

as a non privileged user i did..

SQL> select * from SESSION_PRIVS;

PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE
CREATE USER
BECOME USER
ALTER USER
DROP USER....

— other 69 lines..

as a sysdba I did..

revoke CREATE USER from myuser
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'MYUSER'

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your user obviously has the DBA role. Look into this

SELECT granted_role FROM dba_role_privs where grantee='xxx';

where xxx is the name of your user (in capital letters usually)

Method 2

Solution found.
I remove the CONNECT from user

revoke CONNECT from myuser

and I will assign the privileges as I need

grant create session, create table to myuser

now create user give permission insufficient (error ORA-01031) when I try it as a non dba user

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply