Home » RDBMS Server » Security » user grants via role work only sometimes
user grants via role work only sometimes [message #264696] Tue, 04 September 2007 04:08 Go to next message
braini
Messages: 7
Registered: August 2007
Junior Member
Hi,
I have a big problem with my Oracle 10g XE database:
I have one database user which has several tables other users or schemas can access via a user role. Every thing works fine.

Now I do the following:

1. I delete one table
2. I recreate it
3. I redeclare the user role ( grant access to the new table)
4. I reassign the user role to the other users
5. The user can access the new table

Everything works fine... sometimes...
The last time I recreated a table was 4 days ago. Today one of the other users has accessed this table for about 1 hour. Then suddenly Oracle denies access to this table with the comment "Table or view is not available".

Has anyone any idea why this may happen? I never experienced something like that before... This is also a serious security breach in my opinion isn't it?

(A database restart solves the problem but is not an acceptable option)

Re: user grants via role work only sometimes [message #264699 is a reply to message #264696] Tue, 04 September 2007 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, step 4 is useless.

What you say is not possible, no one can lose the access if the table is there and access granted to the role.
You have to post exactly what you did that is copy and paste all the statements you executed.

Regards
Michel

Re: user grants via role work only sometimes [message #264724 is a reply to message #264699] Tue, 04 September 2007 06:05 Go to previous messageGo to next message
braini
Messages: 7
Registered: August 2007
Junior Member
Hi,
thanks for your quick answer. This is what I do:

DROP TABLE TESTTABLE CASCADE CONSTRAINTS;
DROP SEQUENCE TESTTABLE_SEQ;
DROP ROLE TESTUSERROLE;

CREATE TABLE TESTTABLE (
  "ID" NUMBER(5,0) NOT NULL ENABLE,
  "Name" VARCHAR2(50 BYTE),
  CONSTRAINT "TESTID_PK" PRIMARY KEY ("ID") ENABLE );
CREATE SEQUENCE TESTTABLE_SEQ;
CREATE ROLE TESTUSERROLE;

GRANT SELECT, UPDATE, INSERT ON TESTTABLE TO TESTUSERROLE;
GRANT SELECT ON TESTTABLE_SEQ TO TESTUSERROLE;
GRANT TESTUSERROLE TO TESTUSER2;



DROP TABLE TESTTABLE succeeded.
DROP SEQUENCE TESTTABLE_SEQ succeeded.
DROP ROLE TESTUSERROLE succeeded.
CREATE TABLE succeeded.
CREATE SEQUENCE succeeded.
CREATE ROLE succeeded.
GRANT SELECT, succeeded.
GRANT SELECT succeeded.
GRANT TESTUSERROLE succeeded.


Hope this helps.
My colleagues also mentioned that this is not possible.
This answer did not help me so far.

Is there maybe something that could be cached? The connection to the database for instance?

The DDL commands should work instantly - no commit or something necessary, right?

Re: user grants via role work only sometimes [message #264742 is a reply to message #264724] Tue, 04 September 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you drop the role? (Step which was not in your post, by the way)
If a user connect between you dropped the role and you regrant it, then he doesn't have the table access.

Regards
Michel

Re: user grants via role work only sometimes [message #265083 is a reply to message #264742] Wed, 05 September 2007 06:37 Go to previous messageGo to next message
braini
Messages: 7
Registered: August 2007
Junior Member
I drop the role because I use a migration script that works automaticly. It deletes and creates tables. Afterwards it grants access for all new tables to the userrole. Since the tables differ from migration step to migration step I use USER_ALL_TABLES for automatic reading and granting privileges to all tables.

If I would leave the role I would be afraid that in the role a lot of unused stuff will be accumulated in the next months... And the users would maybe not be able to use the new role privileges.

Is it possible to disconnect all users before the migration starts? A SQL command would be great.

Is it uncritical to leave the role in the database and just add different priviledges again and again...



Re: user grants via role work only sometimes [message #265115 is a reply to message #265083] Wed, 05 September 2007 08:03 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to disconnect all users before the migration starts?

shutdown immediate
startup restrict

Quote:
Is it uncritical to leave the role in the database and just add different priviledges again and again...

It is safe.

Regards
Michel
Previous Topic: OLS query problem
Next Topic: audit vault password
Goto Forum:
  


Current Time: Thu Mar 28 04:41:29 CDT 2024