0

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'

2 Answers 2

0

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)

5
  • No, I add now the lines in my question, thanks for answer, anyway
    – elbarna
    Apr 10, 2022 at 16:09
  • I have removed the roles MICH and RESOURCE from my user, but it still can create other rules and users.
    – elbarna
    Apr 10, 2022 at 17:41
  • Roles can be granted roles and it’s not just the DBA role that can create users. You need to do a connect by or recursive with query to get to all the possible grants. Think about what a user needs to be able to do in order to import a database, the IMP_FULL_DATABASE role gives them this (you can check what system privileges and roles that has grants to) Apr 10, 2022 at 17:56
  • 1
    It is role IMP_FULL_DATABASE
    – Tuxinose
    Apr 10, 2022 at 18:00
  • No, it was the role CONNECT, but also IMP_FULL_DATABASE permit the creation of role and user
    – elbarna
    Apr 10, 2022 at 18:12
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.