OraFAQ Forum: Security » New User Getting privileges without granting

Home » RDBMS Server » Security » New User Getting privileges without granting

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

New User Getting privileges without granting [message #144229]

Tue, 25 October 2005 10:40

anilhyd
Messages: 10
Registered: October 2005

Junior Member

Hi

We have a strange problem in oracle 9i database.

We create a user - Create user test identified by test;

We grant him only create session - grant create session to test;

When we connect as Test and query - select * from session_privs;

We get the following
DROP ANY TABLE
UNLIMITED TABLESPACE
SELECT ANY TABLE
EXECUTE ANY PROCEDURE.

We tried with 3 test users and the same problem is getting repeated. Not sure how the user "TEST" is getting the above privs without granting. Can anyone help ASAP.

Thanks in Advance
Anil..

Report message to a moderator

Re: New User Getting privileges without granting [message #144233 is a reply to message #144229]

Tue, 25 October 2005 10:54

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Can't reproduce the case.
You might executing the query against another schema or the same schema from which you created the test user.
Post what you did.

scott@9i > create user test identified by test;
User created.
scott@9i > grant create session to test;
Grant succeeded.
scott@9i > !sqlplus -s test/test
Enter value for gname:
show user
USER is "TEST"
select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

exit

scott@9i > show user
USER is "SCOTT"

Report message to a moderator

Re: New User Getting privileges without granting [message #144234 is a reply to message #144233]

Tue, 25 October 2005 11:03

anilhyd
Messages: 10
Registered: October 2005

Junior Member

Thanks for the quick reply.

In fact in another database we tried and could not reproduce.

But it is happenning in one database where whatever users we create are getting these privs (once we grant them create session).

Please see the test case below:-
SQL> conn a/a@test
Connected.
SQL>
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
DROP ANY TABLE
SELECT ANY TABLE
EXECUTE ANY PROCEDURE

SQL> conn system@test
Enter password:
Connected.
SQL> select * from dba_role_privs where grantee='A'
2 ;

no rows selected

SQL> select * from dba_tab_privs where grantee='A'
2 ;

no rows selected

SQL> select * from dba_sys_privs where grantee='A'
2 ;

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
A CREATE SESSION NO

SQL> select trigger_name, trigger_type,action_type from all_triggers where owner='A'
2 ;

no rows selected

SQL>

Pl Suggest .

Thanks..
Anil..

Report message to a moderator

Re: New User Getting privileges without granting [message #144235 is a reply to message #144234]

Tue, 25 October 2005 11:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

THe session is not showing how the user A was created.
Login as dba and issue
drop user A cascade;
create the user , grant the priv and post the results as shown

Report message to a moderator

Re: New User Getting privileges without granting [message #144238 is a reply to message #144235]

Tue, 25 October 2005 12:01

anilhyd
Messages: 10
Registered: October 2005

Junior Member

Thank you.
Problem identified and the problem was someone had granted these privs to public like "grant select any table to public".

We revoked them and all the objects become invalid. We have run utlrp.sql to recompile invalid objects.

Regards
Anil

Report message to a moderator

Previous Topic:	Restrict user from updating stored procedure
Next Topic:	file.plb to file.sql ????

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 23 08:51:07 CDT 2026