OraFAQ Forum: Security » roles and users

Home » RDBMS Server » Security » roles and users

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

roles and users [message #8988]

Thu, 09 October 2003 03:56

Deepa
Messages: 269
Registered: November 2000

Senior Member

Hi,
I have created a public synonym for a role and granted prvileges for that synonym to some users.When the user access the data suddenly the role gets revoked from the user.can u explain me how this happens?

Thanks and regards,
Deepa

Report message to a moderator

Re: roles and users [message #8996 is a reply to message #8988]

Thu, 09 October 2003 12:21

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Deepa,
why do you create public synonyms for a role ?
How do you say the role gets revoked ? Is it reproducible ? Did you check user_role_privs ? Are there any jobs scheduled that may be causing issues ?

thiru@9.2.0:SQL>select user from dual;

USER
------------------------------
THIRU

thiru@9.2.0:SQL>drop user test;

User dropped.

thiru@9.2.0:SQL>create user test identified by test;

User created.

thiru@9.2.0:SQL>grant connect to test;

Grant succeeded.

thiru@9.2.0:SQL>create role t_role ;

Role created.

thiru@9.2.0:SQL>grant select on t to t_role;

Grant succeeded.

thiru@9.2.0:SQL>create public synonym t_role for t_role;

Synonym created.

thiru@9.2.0:SQL>grant t_role to test;

Grant succeeded.

-- Roles are not owned by the user who creates it. user_roles doesnt exist.

thiru@9.2.0:SQL>select * from user_roles;
select * from user_roles
*
ERROR at line 1:
ORA-00942: table or view does not exist

thiru@9.2.0:SQL>select * from dba_roles where role='T_ROLE';

ROLE PASSWORD
------------------------------ --------
T_ROLE NO

thiru@9.2.0:SQL>connect test/test
Connected.

-- shows the assigned roles for that user :

thiru@9.2.0:SQL>select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
--------------- ------------------------------ --- --- ---
TEST CONNECT NO YES NO
TEST T_ROLE NO YES NO

thiru@9.2.0:SQL>select count(*) from thiru.t;

COUNT(*)
----------
9

-- Lets drop the public synonym for the role.

thiru@9.2.0:SQL>connect thiru/thiru
Connected.
thiru@9.2.0:SQL>drop public synonym t_role;

Synonym dropped.

thiru@9.2.0:SQL>connect test/test
Connected.
thiru@9.2.0:SQL>select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
--------------- ------------------------------ --- --- ---
TEST CONNECT NO YES NO
TEST T_ROLE NO YES NO

--- It has no effect becos its the role that is granted not the public synonym. You cant grant public synonym(of a role) to a user

thiru@9.2.0:SQL>select count(*) from thiru.t;

COUNT(*)
----------
9

thiru@9.2.0:SQL>connect thiru/thiru
Connected.

-- Lets drop and recreate the table

thiru@9.2.0:SQL>drop table t;

Table dropped.

thiru@9.2.0:SQL>create table t(x int);

Table created.

thiru@9.2.0:SQL>connect test/test
Connected.
thiru@9.2.0:SQL>select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
--------------- ------------------------------ --- --- ---
TEST CONNECT NO YES NO
TEST T_ROLE NO YES NO

-- The role is still granted but the Grant to the role has disappeared becos we recreated the table

thiru@9.2.0:SQL>select count(*) from thiru.t;
select count(*) from thiru.t
*
ERROR at line 1:
ORA-00942: table or view does not exist

So maybe you were recreating tables somewhere without regranting ? or were you recreating roles without regranting the roles to the concerned user?

Also privileges through roles are not visible inside Pl/sql.

Hope this helps
Thiru

Report message to a moderator

Re: roles and users [message #9002 is a reply to message #8996]

Fri, 10 October 2003 01:30

Deepa
Messages: 269
Registered: November 2000

Senior Member

Hi Thiru,
I am sorry i didn't create a synonym for the role.Actually I created a role which has been granted to all the users who is accessing my application.Among the users ,when one of the user connected to the application (internally it executes one of the package for which i gave execute privilege to that user)it fires an error stating that package not declared .This is because the role has been revoked from that user.But i didn't give any revoke statement.can u tell me how this happened?

Is it possible to know who revoked the role from that particular user?

Thanks and Regards,
Deepa

Report message to a moderator

Re: roles and users [message #9003 is a reply to message #9002]

Fri, 10 October 2003 04:24

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Deepa,

Does the application dynamically set the role using DBMS_SESSION.SET_ROLE procedure or SET ROLE sql command based on the user credentials ? It may be that you have granted the role to the user,but the application disables it on the fly for that session.

Did you create a synonym for the package ? Can you reproduce this in sqlPlus ?

Also note that roles are disabled within packages.

Again,how are you confirming that the role has been revoked? did you check it with user_role_privs and session_roles?

Lets see how this can happen :

thiru@9.2.0:SQL>show user
USER is "THIRU"

thiru@9.2.0:SQL>create or replace package my_pkg as
2 procedure test_proc;
3 end;
4 /

Package created.

thiru@9.2.0:SQL>create or replace package body my_pkg as
2 procedure test_proc is
3 begin
4 null;
5 end test_proc;
6 end my_pkg;
7 /

Package body created.

thiru@9.2.0:SQL>create role test_role;

Role created.

thiru@9.2.0:SQL>grant execute on my_pkg to test_role;

Grant succeeded.

thiru@9.2.0:SQL>drop public synonym my_pkg;

Synonym dropped.

thiru@9.2.0:SQL>create public synonym my_pkg for my_pkg;

Synonym created.

thiru@9.2.0:SQL>grant test_role to test;

Grant succeeded.

thiru@9.2.0:SQL>connect test/test
Connected.
thiru@9.2.0:SQL>select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
--------------- ------------------------------ --- --- ---
TEST CONNECT NO YES NO
TEST MY_ROLE NO YES NO
TEST TEST_ROLE NO YES NO

thiru@9.2.0:SQL>execute my_pkg.test_proc;

PL/SQL procedure successfully completed.

-- Lets dynamically change the session role to CONNECT only.

thiru@9.2.0:SQL>set role connect;

Role set.

thiru@9.2.0:SQL>execute my_pkg.test_proc;
BEGIN my_pkg.test_proc; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'MY_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- Since the test_role was disabled within the app,you couldnt see the package

thiru@9.2.0:SQL>select * from session_roles;

ROLE
------------------------------
CONNECT

-- Lets enable TEST_ROLE

thiru@9.2.0:SQL>set role test_role;

Role set.

thiru@9.2.0:SQL>execute my_pkg.test_proc;

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	Row level security
Next Topic:	grant select privelege on some rows of table.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 09:12:32 CDT 2025