validating if any schema deserves GRANT ALL for all objects
Joyan, March 16, 2012 - 1:42 pm UTC
Yes, I thought about the dynamic sql thing.
But considering only static sql in the procedure, I was expecting maybe you could enlighten me with some trick (even though it might not be universal) to beat this, since you always seem to find out a solution for anything related to Oracle...
But in case it cannot be done...it cannot be.
Thanks Tom. I always learn from you and will continue doing so.
March 16, 2012 - 4:17 pm UTC
I cannot enlighten you to something that doesn't exist.
As McCoy used to say on Star Trek "I'm a doctor, not a magician"
:)
And no, I don't always find a solution for anything related to Oracle - I many times say "nope, doesn't do that"
Maybe ...
G, March 18, 2012 - 9:22 am UTC
Any other way of doing this?
Have the users connect through (Schema 3) which only has execute privileges on the packages from (Schema 2).
March 19, 2012 - 10:04 am UTC
that will not tell them if schema 2 has "least privileges" - the least amount of privileges it needs to access schema 1
user_dependencies
Will, March 19, 2012 - 12:09 am UTC
You could use all_dependencies for a definitive list of what is referenced from each package. You would still need to understand about INSERT, UPDATE, DELETE, etc.
March 19, 2012 - 10:13 am UTC
It would not be definitive (dynamic sql)
and it would not answer the question at all really. That is why I did not even mention it. You'd have to go package by package to do the one time analysis to get an understanding of what the minimal set of privileges are and hopefully document at that time why they are that way.
Move Code to Schema1
Carsten, March 19, 2012 - 5:18 am UTC
Actually your seperation is just slightly off from what you desire:
Your code will most likely require full access to your data, so move it into the same schema for easy maintance. But then you can restrict access from schema2 to execute only for schema1 (and maybe select on some tables or views).
This way now unnecessary user priviledge is granted in schema2.
You will hardly benefit from adding a third schema, but increase complexity. Ok except certain business rules require even more restricted access to part of the data, but then again you will most likely bundle data and API in the same schema.
March 19, 2012 - 10:28 am UTC
NO NO NO NO.
Absolutely NOT.
That is a really bad idea - I don't care if "lots of people do it", it is a horrible practice.
You want many schemas - not just one mega schema.
You want a schema 1 holding tables.
You want another schema 2 holding code and views that access said tables.
You want a third schema 3 that has execute on the code in schema 2 and minimal access to the views in schema 2 and hardly any access to tables in schema 1.
That way, when someone finds that inevitable bug in your application they are connected to a schema that doesn't OWN A SINGLE THING. And the schema that accesses the schema 1 objects (schema 2) has MINIMAL PRIVILEGES to do so - so even if that got compromised, you would limit your exposure.
You will hardly benefit from adding a third schema, but increase complexity.
it is precisely this mindset that causes 99.999999% of all security breaches. "Let's make it simple, just grant me DBA and be done with it" type of thoughts. You need to design your application to be secure, it is really sort of VERY important.
this is the worst security advice possible - Please think about it. When your application gets compromised (I said when, not if), it would be a very good thing to have many many layers to get through.
Think about an audit trail table. What privileges does the application need on this audit trail? Answer: INSERT, and maybe - just maybe SELECT - but probably only select on certain columns, certain rows, maybe even filtered by the authenticated application users identity.
So, schema 1 would own the audit trail. and put fine grained access control on it.
Schema 2 would have INSERT on the audit trail and MIGHT be able to select from the audit trail protected by the fine grained access control.
Schema 3 would be able to execute code that populates the audit trail and MAYBE have select on a view set up by schema 2 in order to ad-hoc query it.
Now, when someone finds the sql injection bug in your codebase, since they are connected as schema 3, they cannot
a) drop that audit table
b) modify it
c) insert into it
the most they can do it query it - but even then, only as themselves - if at all.
Your 1 star rating is fully appropriate here, but it applies to your advice in this case. Sorry for being so harsh, but this is a really bad suggestion.
My wrong "maybe"
G, March 19, 2012 - 12:17 pm UTC
that will not tell them if schema 2 has "least privileges" - the least amount of privileges it needs to access schema 1
I didn’t intend to suggest it would. Given the answers provided up-to that point, I felt it was relevant to suggest an immediate relief (while they do the line-by-line code analysis) to the deficiencies of the described 2 schemas approach for reasons you subsequently addressed. It did seem like an obvious kind of suggestion, but look how strong some people feel about doing the wrong thing.
To me the answers seemed too focused on the question rather than on the problem. But it’s all good now. Sorry about the confusion.
March 19, 2012 - 9:06 pm UTC
they already had 'relief', the problem is simply:
can i find out if I have "minimum privs".
they are over privileged, everything is working "as is", an extra schema would not help them answer the question "do i have the minimum set of privs needed"
seperating code and data
Carsten, March 20, 2012 - 5:31 am UTC
Hi Tom,
I am sorry that you have read out of my response what you wrote.
I never intended to suggest a single schema with overly broad grants that everyone connects to.
The original poster had data in schema1 and procedures in schema2. The application code connects using schema2. He wants to restrict access on schema1 to minimal priviledges.
Therefore I suggested to have schema1 hold data and code and grant only execute to schema2. I forgot to mention that of course the application would still connect using schema2.
Think about an audit trail table.
Actually that perfectly qualifies as requirement for a third schema. My point was: Add more layers for a reason. But then design the additional layer and keep it enforced.
I do respect that more layers will make any security breach harder to come over. But reality taught me that without need developers will not given enough time to properly implement the design. So either they just grant all access from schema1 and schema2 to schema3, in order to get rid off all the access errors, or procedures for dynamic sql turn up that intentionally implement a sql injection. In other words: It gets worse.
The OP case is kind of norm: They intended to add security by seperating data and code, but it was too time consuming to actually code the access as part of the table design and now have a grant all.
Code and data have additional references to cross the schema, therefore increased complexity without any security benefit.
Regarding the rating: Sorry but I did miss any constructive advise in your response. Although "it depends" is certainly always right, it is hardly helpful.
March 20, 2012 - 9:51 am UTC
The original poster had data in schema1 and procedures in schema2.
which is EXCELLENT and the right way to do it. Suggesting to put the code into schema1 is wrong.
The code would be over privileged. If the code was subject to sql injection - it would be able to do things *it should not be*. You want this separation, it is good, it is positive, it should be a rule.
But reality taught me that without need developers will not given enough time to properly implement the design.
I'm calling BS on that statement. Totally. That is not a reason to throw security, design, etc etc out the window. I have zero patience for that - especially after seeing it done time and time again. It is pure laziness - nothing else. And it exposes everyone to crap software - every time. I'm so tired of hearing about the poor poor developers who have to actually *design* something properly.
Give.
Me.
A.
Break.
but it was too time consuming
No, the "professional" software developers were too lazy to do it the way it has to be done, they cut corners, they did it wrong. Period. No other excuse exists.
There is no way to answer the original posters question except by the advice given - revoke, and then grant just what you need, documenting as you go along.
That is constructive advice.
Yours is destructive advice in my opinion and experience.
Never time to do it right, always time to do it over.
Carsten, March 20, 2012 - 5:58 am UTC
Before I forget it: I did like your response to my posting. It was quite elaborate on security design.
find operations on table
Nikhilesh, March 20, 2012 - 3:54 pm UTC
Hi Joyan,
You can query "user_source" and search for table names from schema1. You can get the operations coded for those tables e.g. SELECT query, INSERT, DELETE etc.
This is just a logical solution that came as a first thought in my mind. It may not be efficient but can get the results. I'm no expert.
March 20, 2012 - 7:32 pm UTC
it would require a ton of parsing. In my opinion, it is not practical.
Result Vs Performance
Nikhilesh, March 21, 2012 - 2:38 pm UTC
Dear Tom,
Thanks for your comment.
Yes its not practical but it doesn't require a production database either. He can setup a database on PC or laptop, import source code and tables without data and execute this single query.
Moreover we can do it outside database too. If we have source code files it would not be that difficult in UNIX to search all of them. Might be a small SHELL script can do the trick.
Again this is just result focussed suggestion and should be fllowed if the this requirement is the sole purpose of your life :)
March 21, 2012 - 10:54 pm UTC
I don't care where the database is or anything.
The practical bit was about the code you'd have to write and the probability that the outcome was anywhere close to accurate. It would be faster to revoke everything and add it back in bit by bit.
Function/Proc which creates tablespace/table etc
Krithika, May 21, 2012 - 9:42 pm UTC
Hi,
I have a function which is used to create table / tablespace and grant privileges.
This function will be run by other oracle user. what privileges do i need to give this oracle user in order for it to run the function successfully.
May 22, 2012 - 7:56 am UTC
if the procedure is a definer rights routine - you just need to grant execute on it to the other user.
If the procedure is an authid current_user (invoker rights), then they would need all of the base privileges - create tablespace, etc.
Beware of sql injection - if you want, post the code and we'll critique it for you. The procedure owner will have powerful privileges - you don't want to let loose a security hole (while trying to be more secure!)
Re: Function/Proc which creates tablespace/table etc
Krithika, May 22, 2012 - 12:41 pm UTC
Here is the function
CRECATE OR REPLACE FUNCTION create_product_schema
(v_ID VARCHAR2
,v_CODE VARCHAR2
)
RETURN VARCHAR2
IS
pragma autonomous_transaction;
incorrect_code EXCEPTION;
v_ID_5_DIG VARCHAR2(200) := return_ID(v_ID);
v_SCHEMA_OWNER VARCHAR2(200) := return_NAME(v_ID);
v_product_CODE VARCHAR2(200) := check_code(v_CODE);
v_INSERT_USER VARCHAR2(50) := 'Script';
v_SCHEMA_OWNER_PWD VARCHAR2(200) := generateRandom(v_SCHEMA_OWNER'); -- Schema Owner Password
v_DATA_TS_LOC VARCHAR2(200) :='/u01/oradata/'; -- Data File Location
v_INDEX_TS_LOC VARCHAR2(200) :='/u01/oradata/'; -- Index File Location
v_FILE_SIZE VARCHAR2(200) :='50M'; -- File Size (Example: 10M)
v_ROLE_ADMIN VARCHAR2(200) :='ROLE_ADMIN'; -- DB Admin role with permissions SELECT, INSERT, UPDATE, DELETE
v_ROLE_RW VARCHAR2(200) :='ROLE_RW'; -- DB Read-Write role with permisssions SELECT, INSERT, UPDATE
BEGIN
IF v_product_TYPE_CODE = '-1' THEN
RAISE incorrect_code;
END IF;
EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || v_SCHEMA_OWNER || '_DATA_00 DATAFILE '''|| v_DATA_TS_LOC || v_SCHEMA_OWNER || '_DATA_00.dbf'' SIZE ' || v_FILE_SIZE || ' REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || v_SCHEMA_OWNER || '_INDEX_00 DATAFILE ''' || v_INDEX_TS_LOC || v_SCHEMA_OWNER || '_INDEX_00.dbf'' SIZE '|| v_FILE_SIZE || ' REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL';
EXECUTE IMMEDIATE 'CREATE USER ' || v_SCHEMA_OWNER || ' IDENTIFIED BY ' || v_SCHEMA_OWNER_PWD || ' DEFAULT TABLESPACE ' || v_SCHEMA_OWNER || '_DATA_00 TEMPORARY TABLESPACE temp PROFILE PROFILE_PRDCTDEFAULT';
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO ' || v_SCHEMA_OWNER;
EXECUTE IMMEDIATE 'GRANT ' || v_ROLE_ADMIN || ' TO ' || v_SCHEMA_OWNER;
EXECUTE IMMEDIATE 'CREATE TABLE '|| v_SCHEMA_OWNER ||'.' || 'product
(
product_type VARCHAR2(4)
,product_name VARCHAR2(80) NOT NULL
,insert_dttm TIMESTAMP DEFAULT SYSDATE NOT NULL
,insert_user VARCHAR2(150) DEFAULT user NOT NULL
,update_dttm TIMESTAMP DEFAULT SYSDATE NOT NULL
,update_user VARCHAR2(150) DEFAULT user NOT NULL
,CONSTRAINT pk_product PRIMARY KEY (product_type) USING INDEX TABLESPACE ' || v_SCHEMA_OWNER || '_INDEX_00 ) TABLESPACE ' || v_SCHEMA_OWNER || '_DATA_00';
FOR c IN (select table_name from dba_tables where owner = v_SCHEMA_OWNER)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE ON ' || v_SCHEMA_OWNER || '.'|| c.TABLE_NAME || ' TO ' || v_ROLE_RW ||' , ' || v_ROLE_ADMIN;
END LOOP;
FOR c IN (SELECT VIEW_NAME FROM DBA_VIEWS where owner = v_SCHEMA_OWNER)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || v_SCHEMA_OWNER || '.' || c.VIEW_NAME || ' TO ' || v_ROLE_RW ||' , ' || v_ROLE_ADMIN;
END LOOP;
FOR c IN (SELECT SEQUENCE_NAME FROM DBA_SEQUENCES where SEQUENCE_OWNER = v_SCHEMA_OWNER)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || v_SCHEMA_OWNER || '.' || c.SEQUENCE_NAME || ' TO ' || v_ROLE_RW ||' , ' || v_ROLE_ADMIN;
END LOOP;
FOR c IN (SELECT TABLE_NAME FROM DBA_TABLES where owner = v_SCHEMA_OWNER)
LOOP
EXECUTE IMMEDIATE 'GRANT DELETE ON ' || v_SCHEMA_OWNER || '.' || c.TABLE_NAME || ' TO ' || v_ROLE_RW ||' , ' || v_ROLE_ADMIN;
END LOOP;
EXECUTE IMMEDIATE 'INSERT INTO ' || v_SCHEMA_OWNER || '.' || 'product (product_type, product_name) VALUES (''GE'',''General'')';
RETURN 0;
EXCEPTION
WHEN incorrect_code THEN
DBMS_OUTPUT.PUT_LINE('Incorrect Product type code');
RETURN -1;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error code: '|| SQLCODE );
DBMS_OUTPUT.PUT_LINE('Error message: '|| SQLERRM);
RETURN -1;
END;
/
May 22, 2012 - 2:39 pm UTC
<code>
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error code: '|| SQLCODE );
DBMS_OUTPUT.PUT_LINE('Error message: '|| SQLERRM);
RETURN -1;
</oode>
osh kosh bgosh - WHY, why on earth would you do that? why - seriously? WHY???
The invoker doesn't know where the error happened anymore, they stand no chance of recovering
Of all of the worst practices out there - this is perhaps the WORST. When others, not followed by raise or raise_application_error!
https://www.google.com/search?q=site%3Aasktom.oracle.com+%22i+hate+your+code%22 I really do hate your code.
be sure:
v_ID_5_DIG VARCHAR2(200) := return_ID(v_ID);
v_SCHEMA_OWNER VARCHAR2(200) := return_NAME(v_ID);
v_product_CODE VARCHAR2(200) := check_code(v_CODE);
those functions return SAFE values - very very very safe values.
for good measure you should use dbms_assert around the strings you concatenate into the SQL in order to validate/sanitize the inputs. To protect from sql injection.
Re: Function/Proc which creates tablespace/table etc
Krithika, May 22, 2012 - 3:58 pm UTC
Thank you for your response.
The function return_ID, return_Schema and check_code are doing the validation and returning the value.
I have removed.
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error code: '|| SQLCODE );
DBMS_OUTPUT.PUT_LINE('Error message: '|| SQLERRM);
RETURN -1;
for my original question. What permission do i need to give to the user that would be executing this function.
May 23, 2012 - 7:19 am UTC
the owner of the function would need all of the privileges necessary to run the create/grants.
the person RUNNING the function would just need execute on it.
security
A reader, May 22, 2012 - 10:38 pm UTC