 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
|
|
|
|
| Re: Public DBA role [message #152280 is a reply to message #152197] |
Thu, 22 December 2005 03:11  |
Frank Naude
Messages: 4587
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 |
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 |
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 |
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 
Not a good idea.
Give
REVOKE DBA FROM PUBLIC;
a try.
Jim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
