Home » RDBMS Server » Security » Public DBA role
icon8.gif  Public DBA role [message #152197] Wed, 21 December 2005 11:01 Go to next message
sunil_mahajan26
Messages: 7
Registered: December 2005
Location: Gurgoan
Junior Member
Hi everyone,

I got a problem which is: whenever I create a new user in oracle 8i it always take DBA role by default.
I will explain it to u with example:
suppose i create a user sunil
After creation of user I will not grant any privilege to it not even create session . But when I try to login using this user I
easily login and able to do everything e.g
create table, see another users tables and so on
Do anyone have any suggestions regarding this i will be very thankful to him/her

Sunil Mahajan
Re: Public DBA role [message #152280 is a reply to message #152197] Thu, 22 December 2005 03:11 Go to previous messageGo to next message
Frank Naude
Messages: 4570
Registered: April 1998
Senior Member
Please post your CREATE/ALTER USER and GRANT statements so we can look at them.

Also, after creating a new user, login to it and do the following:

SELECT * FROM session_roles;
SELECT * FROM session_privs;


Best regards.

Frank
Re: Public DBA role [message #152327 is a reply to message #152280] Thu, 22 December 2005 09:31 Go to previous messageGo to next message
sunil_mahajan26
Messages: 7
Registered: December 2005
Location: Gurgoan
Junior Member
These r the results what u want:

I have first created a user by using a simple command

create user hello identified by hello123;

After that connec hello/hello123;


select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE

6 rows selected.



select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER

PRIVILEGE
----------------------------------------
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE

PRIVILEGE
----------------------------------------
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX

PRIVILEGE
----------------------------------------
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE

PRIVILEGE
----------------------------------------
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE

PRIVILEGE
----------------------------------------
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER

PRIVILEGE
----------------------------------------
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE SNAPSHOT
CREATE ANY SNAPSHOT
ALTER ANY SNAPSHOT

PRIVILEGE
----------------------------------------
DROP ANY SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY

PRIVILEGE
----------------------------------------
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
QUERY REWRITE

PRIVILEGE
----------------------------------------
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT

PRIVILEGE
----------------------------------------
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER

115 rows selected.



select * from dba_role_privs where grantee='hello';

no rows selected

select * from dba_tab_privs where grantee='hello';
no rows selected

select * from dba_sys_privs where grantee='hello';
no rows selected

select trigger_name,trigger_type,action_type from dba_triggers where owner
no rows selected


Regards
Sunil
Re: Public DBA role [message #152454 is a reply to message #152327] Fri, 23 December 2005 04:58 Go to previous messageGo to next message
ramsat
Messages: 49
Registered: November 2005
Member
Ur query to check the privilege for the user ----hello--- is wrong.
The username should be in capital letters but u have given in small case letters

I believe ur profile was changed.Ur profile points to a role whih contains this dba privilege.
So, check ur ways to change ur profile to a new one which plays all vital limits for a user.
see that whether u can do something with the pfile.(parameters specifications.... i believe this will work)
in that case after successful attempt then create spfile from pfile.
restart the database.
check it out and please revert any change that u come across.....
The scenario is quite interesting .......................



Ramesh
Re: Public DBA role [message #152472 is a reply to message #152454] Fri, 23 December 2005 07:18 Go to previous messageGo to next message
sunil_mahajan26
Messages: 7
Registered: December 2005
Location: Gurgoan
Junior Member
Dear sir
I have given it in capital the same result has come
I don't know the cause behind it . Whenever I create a new user
It will automatically get the above privileges .
Pls be specific which parameter u r taking abt in pfile

Do u have any suggestions - what should I do rectify it

[Updated on: Fri, 23 December 2005 07:19]

Report message to a moderator

Re: Public DBA role [message #155469 is a reply to message #152472] Mon, 16 January 2006 07:30 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
From the looks of things, DBA may have been granted to public Very Happy
Not a good idea.

Give
REVOKE DBA FROM PUBLIC;

a try.

Jim
Previous Topic: ORA-01919: role 'XDBADMIN' does not exist
Next Topic: oracle Run-time error '-2147467259 (80004005)'
Goto Forum:
  


Current Time: Thu Dec 02 16:20:11 CST 2021