Oracle: a litte confused about privileges granted to my user



  • 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'
    


  • 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)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2