Oracle: a litte confused about privileges granted to my user

by

The question:

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'

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


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 Comment