OraFAQ Forum: Security » Public DBA role

Home » RDBMS Server » Security » Public DBA role

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Public DBA role [message #152197]

Wed, 21 December 2005 11:01

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

Report message to a moderator

Re: Public DBA role [message #152280 is a reply to message #152197]

Thu, 22 December 2005 03:11

Frank Naude
Messages: 4596
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:

Code: [Select all] [Show/ hide]

SELECT * FROM session_roles;
SELECT * FROM session_privs;

Best regards.

Frank

Report message to a moderator

Re: Public DBA role [message #152327 is a reply to message #152280]

Thu, 22 December 2005 09:31

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

Report message to a moderator

Re: Public DBA role [message #152454 is a reply to message #152327]

Fri, 23 December 2005 04:58

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

Report message to a moderator

Re: Public DBA role [message #152472 is a reply to message #152454]

Fri, 23 December 2005 07:18

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 22:03:27 CDT 2025