| user created with default automatic privilege [message #419428] |
Sat, 22 August 2009 03:18  |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
i have created a user in oracle 9.2.0.1.0 on window xp
create user xxx identified by xxx
by default the user shouldn't have any privileges ,but the user is able to connect to database ,and also can create user ,table,and tablespace
when i run the view select * from session_privs ,it shows 140 privileges in that
SQL> 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 RULE
PRIVILEGE
----------------------------------------
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
EXECUTE ANY RULE
CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
PRIVILEGE
----------------------------------------
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
PRIVILEGE
----------------------------------------
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
PRIVILEGE
----------------------------------------
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
PRIVILEGE
----------------------------------------
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE SNAPSHOT
CREATE ANY SNAPSHOT
ALTER ANY SNAPSHOT
DROP ANY SNAPSHOT
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
PRIVILEGE
----------------------------------------
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
DROP ANY OPERATOR
PRIVILEGE
----------------------------------------
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
PRIVILEGE
----------------------------------------
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
PRIVILEGE
----------------------------------------
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
ON COMMIT REFRESH
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
PRIVILEGE
----------------------------------------
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
140 rows selected.
i try to revoke that privileges but it throws a error
SQL> revoke create user from xxx;
revoke create user from xxx
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'xxx'
how can a user can get 140 default privileges at the time of creation ,please help me out of this mess 
[Updated on: Sat, 22 August 2009 03:31] Report message to a moderator |
|
|
|
|
|
| Re: user created with default automatic privilege [message #419433 is a reply to message #419430] |
Sat, 22 August 2009 04:00  |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
i have run the query
select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
ROLE
------------------------------
XDBADMIN
OLAP_DBA
13 rows selected.
but how this role is been granted at the time of user creation
and i,m unable to revoke this role from the user
SQL> CONN / AS SYSDBA
SQL> CONNECTED.
SQL> REVOKE DBA FROM XXX;
REVOKE DBA FROM XXX
*
ERROR at line 1:
ORA-01951: ROLE 'DBA' not granted to 'XXX'
|
|
|
|
|
|
|
|
|
|
| Re: user created with default automatic privilege [message #419494 is a reply to message #419460] |
Sun, 23 August 2009 05:16 |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> set time on
15:41:15 SQL> show user
USER is "SYS"
15:41:18 SQL> create user xxx identified by xxx_1;
User created.
15:41:34 SQL> conn xxx/xxx_1
Connected.
15:41:52 SQL> create table xxx(name char(12));
Table created.
15:42:14 SQL> desc xxx;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME CHAR(12)
15:42:19 SQL> select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
ROLE
------------------------------
XDBADMIN
OLAP_DBA
13 rows selected.
15:42:28 SQL> conn / as sysdba
Connected.
15:42:36 SQL> revoke dba from xxx;
revoke dba from xxx
*
ERROR at line 1:
ORA-01951: ROLE 'DBA' not granted to 'XXX'
15:42:52 SQL> revoke SELECT_CATALOG_ROLE from xxx;
revoke SELECT_CATALOG_ROLE from xxx
*
ERROR at line 1:
ORA-01951: ROLE 'SELECT_CATALOG_ROLE' not granted to 'XXX'
15:43:04 SQL>
as per the guideline i have posted the problem again
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: user created with default automatic privilege [message #419512 is a reply to message #419428] |
Sun, 23 August 2009 13:34 |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
after querying your query
select trigger_name from dba_triggers where owner = 'SYS' and triggering_event like 'CREATE%';
i have found this result
TRIGGER_NAME DESCRIPTION
------------------------------ -----------------------------------
CDC_CREATE_CTABLE_AFTER sys.cdc_create_ctable_after
CDC_CREATE_CTABLE_BEFORE sys.cdc_create_ctable_before
NO_VM_CREATE no_vm_create
out of which first two triggers are enabled and last one is disabled
|
|
|
|
|
|
| Re: user created with default automatic privilege [message #419514 is a reply to message #419513] |
Sun, 23 August 2009 14:09 |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
i apologize for above output ,the actual output was
TRIGGER_NAME
------------------------
CDC_CREATE_CTABLE_AFTER
CDC_CREATE_CTABLE_BEFORE
NO_VM_CREATE
DESCRIPTION
-------------------------------------------------
sys.cdc_create_ctable_after
sys.cdc_create_ctable_before
no_vm_create
TRIGGERING_EVENT
------------------------------------------------------------------------------
CREATE
CREATE
CREATE
out of which first two where enabled and last one is disabled
[Updated on: Sun, 23 August 2009 14:13] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: user created with default automatic privilege [message #419559 is a reply to message #419558] |
Mon, 24 August 2009 04:37 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There is no chance that your database has been infected with a virus.
If there is not error message in the log files and no trace files are being created, then the chance that your database is corrupt is very small.
It looks very much like there is a trigger granting a role to newly created users.
Can you run SELECT * FROM USER_ROLE_PRIVS |
|
|
|
| Re: user created with default automatic privilege [message #419573 is a reply to message #419559] |
Mon, 24 August 2009 05:18 |
param786
Messages: 15
Registered: August 2009
Location: chandigarh
|
Junior Member |
|
|
yes i run that query after creating and connecting through the new user
SELECT * FROM USER_ROLE_PRIVS
USERNAME
------------------------------
PUBLIC
GRANTED_ROLE
------------------------------
DBA
OS_
---
NO
when i run the query select * from session_roles;
it shows 13 roles granted to that user at the time of user creation
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
ROLE
------------------------------
XDBADMIN
OLAP_DBA
13 rows selected.
somehow i able to remove that roles by using the command
altet user dolt default role none;
then i run the query
select * from session_privs;
it show three three privilges
PRIVILEGE
-----------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
out of which i only granted create session privilege to user ,the other privileges unlimited tablespace and create table where assigned to user by default from nowhere
i unable to revoke that two privilege ,when i try to revoke that two privileges ,it troughs an error
revoke create table from dolt
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'dolt'
|
|
|
|
| Re: user created with default automatic privilege [message #419577 is a reply to message #419573] |
Mon, 24 August 2009 05:59 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I reckon that most of those Roles come from having the DBA role granted.
Connect to your Db as System, or a DBA account, run this and paste the results:select distinct owner,name
from dba_source
where type = 'TRIGGER'
and upper(text) like '%DBA%';
|
|
|
|
| Re: user created with default automatic privilege [message #419580 is a reply to message #419573] |
Mon, 24 August 2009 06:21 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SELECT * FROM USER_ROLE_PRIVS
USERNAME
------------------------------
PUBLIC
GRANTED_ROLE
------------------------------
DBA
You granted DBA to PUBLIC and so everybody is DBA.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
[Updated on: Mon, 24 August 2009 06:23] Report message to a moderator |
|
|
|
|
|
| Re: user created with default automatic privilege [message #419595 is a reply to message #419573] |
Mon, 24 August 2009 07:30 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| param786 wrote on Mon, 24 August 2009 12:18 | yes i run that query after creating and connecting through the new user
SELECT * FROM USER_ROLE_PRIVS
USERNAME
------------------------------
PUBLIC
GRANTED_ROLE
------------------------------
DBA
OS_
---
NO
|
| JRowbottom said | Well spotted Michel - I missed that completely.
|
See, param786, this is what you get if you fake output. What you showed is NOT the output SQLPlus showed you.
|
|
|
|
|
|
|
|
|
|
| Re: user created with default automatic privilege [message #419627 is a reply to message #419620] |
Mon, 24 August 2009 09:26 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Michel Cadot wrote on Mon, 24 August 2009 15:56 | | Quote: | What you showed is NOT the output SQLPlus showed you.
|
It may be:
|
I was talking about the way it was "formatted"
I bet that if it had been displayed the way you did it, JRowbottom would have spotted it.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
