Home » RDBMS Server » Security » New User Getting privileges without granting
icon9.gif  New User Getting privileges without granting [message #144229] Tue, 25 October 2005 10:40 Go to next message
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..
Re: New User Getting privileges without granting [message #144233 is a reply to message #144229] Tue, 25 October 2005 10:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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"



Re: New User Getting privileges without granting [message #144234 is a reply to message #144233] Tue, 25 October 2005 11:03 Go to previous messageGo to next message
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..



Re: New User Getting privileges without granting [message #144235 is a reply to message #144234] Tue, 25 October 2005 11:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
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

Re: New User Getting privileges without granting [message #144238 is a reply to message #144235] Tue, 25 October 2005 12:01 Go to previous message
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
Previous Topic: Restrict user from updating stored procedure
Next Topic: file.plb to file.sql ????
Goto Forum:
  


Current Time: Fri Sep 24 04:59:48 CDT 2021