unlimited tablespace

is the only one.

There are performance implications

Most importantly there are namespace clashes. I'm a big fan of "stick as many apps in my database as you can". If they all want a public synonym "S", we are in a heap of trouble.

Yah, you won't be able to do that. 

truncate will not work on a synonym.
truncate will not work on a view.

it is that way by design.  you must be explicit with ddl.

ops$tkyte@ORA920.US.ORACLE.COM> create synonym x for scott.emp;

Synonym created.

ops$tkyte@ORA920.US.ORACLE.COM> select * from x;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

ops$tkyte@ORA920.US.ORACLE.COM> drop table x;
drop table x
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ops$tkyte@ORA920.US.ORACLE.COM> drop table scott.emp;

Table dropped.




it would be the cause of much heartburn otherwise. "whoops, I didn't know it was THAT table".

stored procedure will work. 

If you were to use database user accounts and roles, yes, you would create 700+ users and grant to them.

Then, you have the ability to know exactly who did what, when and where.

Using a ON LOGON schema trigger would work. See the appllication developers guide for details on that.

Nothing is a performance issue if it is a requirement of your system.  I always love it when people say "auditing is a performance issue".  No, no it is not.  Auditing is either something you need to do (like putting an order in an order table is something you NEED to do in an order entry system -- it is NOT a performance issue that you have to insert) or something you don't need to do.  It is not a performance issue.

Auditing takes more resources then not auditing.  However, if auditing is something you have to do -- then you have to do it.


metalink has 6 figures worth of database users (i remember when we had to upgrade it from 7.3 which only supported 64k users to 8.0 with 2billion max users -- there was a while we could not add new metalink accounts).

My own internal database at Oracle that I run has 

ops$tkyte@ORA9I.WORLD> select count(*) from all_users;

  COUNT(*)
----------
     49800


runs just dandy. 

1) not sure what I'm comparing this to? Having a single schema -- if so, they wouldn't have any of their own tables. Having public synonyms for this other data? if so, confusion abounds. If they (the user) creates a table as the same name as the public synonym -- what then?

Me, personally, I've never had an issue with using SCHEMA names. Tools that generate queries have NO PROBLEMS using the schema name.

To me, this is much ado about nothing. Users don't log into sqlplus and start querying - -they use tools, they use stored queries (views). this is mostly hidden from them.

2) web tools like jsp's, servlets, etc are just as capable as client server things as far as security goes. The same things d2k would do -- you would do in asp. I see no differences there.

3) More flexible to you, maybe.

Heinous, terrible, "hate it" to me.

So, do you think this web thing is the last application development paradigm??? It is the end right -- there will be nothing else.

Tell me -- how is is more flexible? Lets see, database has "create user" command - you can write code to do a "create user" in your applicaiton. Database has create role and grant, application has to code create role and grant. Database does auditing, application has to code auditing. (you see where I am going -- you are emulating the database functionality you already bought).

I guess, if by flexible you mean "It lets me write more code", you are right. If by flexible you mean "I get more functionality" -- i have yet to see it. You are developing YET ANOTHER authentication/authorization method. Do you do single sign on? Database can. Do you do advanced authentication methods like x509 PKI? Database can. Do you have so many spare cycles that writing identification and authorization and auditing and access control is something you can do? Great -- I don't.

Flexible? 3 years from now the next great programming paradigm comes along. Oh sorry you say -- cannot do it cause all of the security is tied up in the application and none of it is by the data. You can only access the data via my old fashioned application - -you cannot build new apps on top of the data using this new paradigm. All of a sudden, your cool web app is as appealing as a 3278 Green screen application from a mainframe. You know, the green screen apps

o did identification
o authorization
o access controls
o auditing

all tied up in the code.... Hmmm, guess what was really hard to migrate to client server and then web and then <next really cool programming method here>


Can I use an ad-hoc query tool against your data? What -- I cannot??? Bummer, seems to me that any time I put up a database application -- the end users say "cool, but what I really need to see is X". Well, if I have I&A, access control -- everything tied up in the application -- i cannot let them have at the data now can i? Guess it is really good for job security -- need more data, new reports? I'll be the only one that can do it for you.

Web applications are not special, they are not different, it is the same old thing on three tiers. You can make the same mistakes that were made with host based and client server systems all over again.

4) And it is easier in your application HOW??? If you have thousands of tables/functions -- it is easier in your "flexible" environment HOW??

5) Read about n-tier proxy authentication, read about fine grained access control. That does exactly that. Secure application roles plus security policies in place by application.

6) 2 billion.

search the documentation for

proxy authentication


there is a "set user" capability in the database. Also, mod_plsql and many other things work very hard to reuse a physical circut for you.

It would be normal to create synonyms on the remote site -- never tried set current_schema over a dblink.

well, I would just let y log in as x, whats the difference here - not much you will be stopping Y from doing.

what do you mean by "modify" - do you mean "recompile" or "supply brand new code for it"

create is easy, just revoke the privileges from them. just give them CREATE SESSION and nothing more. they can modify their existing tables (you might have to adjust their quotas to allow these tables to grow) but they will not be able to create more.

ALTER is trickier, you can use a "before alter" "on schema" trigger (you need to do a before alter or drop probably)

sort of like this example:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4111470834766 <code>

depends on the release and I don't see any info here for that :(

<b>someone issued:

alter user sim default roles connect, resource;

at some time.  So, sim has dba, but DBA is not one of the default roles, so sim would have to enable it.

you can alter user sim default role all; to reset this.</b>


ops$tkyte@ORA9IR2> conn / as sysdba;
Connected.
ops$tkyte@ORA9IR2> create user sim identified by sim;
 
User created.
 
ops$tkyte@ORA9IR2> grant connect, resource to sim;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> alter user sim default roles connect, resource;
 
User altered.
 
ops$tkyte@ORA9IR2> grant dba to sim;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect sim/sim;
sim@ORA9IR2> select * from session_roles;
 
ROLE
------------------------------
CONNECT
RESOURCE
 
sim@ORA9IR2> set role ALL;
 
Role set.
 
sim@ORA9IR2> select * from session_roles;
 
ROLE
------------------------------
PLUSTRACE
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
OLAP_DBA
 
16 rows selected.
 

nope.

you would use fine grained access control to limit access to specific rows within a table

or create a view that only selects that partition.

fine grained access control was invented to do exactly this:

</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

not sure what I'm looking at -- whats "jobid". a cursor for loop with a return inside of it?

you need to use the context in the predicate itself -- you want to BIND, not hard code values. suggest you take a look at:

</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

appropriate use of grants?

there is no "select any table in this schema" privilege. No.

you could easily script this, if you have a list of tables to grant on in a table somewhere

begin
for x in ( select .... from t )
loop
execute immediate 'grant all on ' || x.tname || ' to y';
end loop;
end;
/

but the object grants are on an object, a grant per object to be granted.

there is either the ability to

a) create tables for yourself (grant create table to YOU)
b) create tables for ANYONE (grant create ANY table to YOU)

there is no "grant create tables for THEM to YOU"


You can use a stored procedure, as X you would:

create procedure create_table( p_str in varchar2 )
as
begin
execute immediate 'create table ' || p_str;
end;
/

grant execute on create_table to Y;


and y can

sql> execute x.create_table( 't ( x int, y int )' );

but be aware of sql injection, anytime you do dynamic sql like that.
</code> https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html <code>

Be a question for the makers of "informatica"?

grant connect, resource - I would NOT use those.

grant select any table - OH MY - revoke that IMMEDIATELY, RIGHT NOW, RIGHT AWAY - not a change.

grant create public synonym - SAME THING, revoke it, right now, not a chance

grant create any context - SAME THING, revoke it, right now, immediately


To create a developer account:

alter user devadmin quota unlimited on userdata;
grant create session to devadmin;
grant plustrace to devadmin;

and then if they need to create tables, grant them create table.
if they need to create a materialized view, grant them that


avoid the ANY privileges and grant AS LITTLE AS YOU CAN - and no more. And question every grant.

create your own view then of DBA_SOURCE and grant them select on it

or, if they should see ALL source code, grant them select on DBA_SOURCE.

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

if they use proxy users - sure, when you granted connect through to the proxy user - you listed the roles that would be in effect.


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4003.htm#sthref5729


also make sure the roles were "defaulted", on for that user by default (dba_role_privs).

you'd see two sessions for a single process - one is the proxy user and the other would be the "application user"

but, you could just ask the developers.

In order to believe you checked the defaults, I'd need to see "evidence" of that. I'd want a java program that when run would show a different set of rows in session_roles than a query in sqlplus would

java is just a language, like C (just not as good). This will be something the programmer themselves did - we don't recognize java as a client - we just recognize clients.

I can only think that the roles were non-default, without an actual cut and paste example, I cannot diagnose it from where I sit.

... Compiling in our
production systems is not allowed for developers, ...

and I read that and said "LOGGING IN OUR production system...." that should be the words you used. Why are they allowed IN at all??

do not allow developers to log into the production schema, that is madness.

That means you cannot get there from here.



You could create a ddl trigger, but you know, if the developers can log in, they can probably defeat that as well (since they probably have access to the account that would hold the ddl/system event trigger as well)

ops$tkyte%ORA10GR2> create or replace trigger ddl_trigger
  2  before create or alter or drop on schema
  3  begin
  4          raise_application_error( -20001, 'no, you cannot' );
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA10GR2> drop table t;
drop table t
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: no, you cannot
ORA-06512: at line 2


ops$tkyte%ORA10GR2> create or replace function f return number as begin return 0; end;
  2  /
create or replace function f return number as begin return 0; end;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: no, you cannot
ORA-06512: at line 2


ops$tkyte%ORA10GR2> desc f;
FUNCTION f RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             VARCHAR2                IN
 P2                             VARCHAR2                IN
 P3                             VARCHAR2                IN

however...

ops$tkyte%ORA10GR2> drop trigger ddl_trigger;

Trigger dropped.

so, you'd have to create that trigger in another schema, using "on database" instead of "on schema" and put the appropriate schema checks in there.

lock it down

and make sure the developers do not have any of the "ANY" privileges...

you own it? anything you own, of course you can drop.

you revoked the ability going forward to CREATE new tables - why do you think that would or should prevent you from dropping that which you already own?

he *OWNS* it. You don't need "drop your own table" privs - there isn't even such a concept. Given there is no "revoke drop that_table", there is no concept of not being able to drop stuff you own.

And I don't see the correlation between this and the update. In the update scenario - you don't OWN the rows you update. You do however OWN the table in this case, you *own it*. You cannot *create* anymore, but you can still drop, delete, truncate, insert, update, cause it to grow, etc.

Actually, if someone else creates the table for you - you can still drop it, you are the OWNER of the table.

ops$tkyte%ORA10GR2> drop user a cascade;

User dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create user a identified by a quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA10GR2> create table a.t ( x int );

Table created.

ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> drop table t;

Table dropped.

a%ORA10GR2> create table t ( x int );
create table t ( x int )
*
ERROR at line 1:
ORA-01031: insufficient privileges

You'll have to change your feeling - because, well, it doesn't even make sense (in my opinion).


You can prevent an owner from dropping a table in 11g - by placing it in the flashback data archive.

ops$tkyte%ORA11GR1> create user a identified by a quota unlimited on users;

User created.

ops$tkyte%ORA11GR1> grant create session to a;

Grant succeeded.

ops$tkyte%ORA11GR1> create table a.t ( x int );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create flashback archive demo tablespace users retention 1 month;

Flashback archive created.

ops$tkyte%ORA11GR1> alter table a.t flashback archive demo;

Table altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect a/a
Connected.
a%ORA11GR1> drop table t;
drop table t
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


a%ORA11GR1> alter table t no flashback archive;
alter table t no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


a%ORA11GR1>
a%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> alter table a.t no flashback archive;

Table altered.

ops$tkyte%ORA11GR1> drop table a.t;

Table dropped.

if you can remember the other place you posted this, i responded over there.

this seems strange, tell us more. Why would you need to grant on a role every time the database starts up?

Can I tell you how to do this - yes.

I want to know what you are doing first because it sounds very very very fishy to me.

ops$tkyte%ORA10GR2> connect / as sysdba;
Connected.
sys%ORA10GR2> grant dba to ops$tkyte with admin option;

Grant succeeded.

sys%ORA10GR2>
sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger after_startup
  2  after startup on database
  3  declare
  4          l_job number;
  5  begin
  6          if ( 1=1 )
  7          then
  8                  dbms_job.submit( l_job,
  9                  'begin execute immediate ''grant dba to a''; end;' );
 10          end if;
 11  end;
 12  /

Trigger created.

ops$tkyte%ORA10GR2> select * from dba_role_privs where grantee = 'A';

no rows selected

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect / as sysdba;
Connected.
sys%ORA10GR2> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA10GR2> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1268484 bytes
Variable Size             155190524 bytes
Database Buffers          373293056 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
sys%ORA10GR2> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

sys%ORA10GR2> select * from dba_role_privs where grantee = 'A';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
A                              DBA                            NO  YES

sys%ORA10GR2>
sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> drop trigger after_startup;

Trigger dropped.

use the job queues to run the grant as a job outside of the trigger. I slept for 10 seconds only for the demo - you would not do this in real life.

Yes, job queues/scheduler would have to be enabled for this.

Well, I would start by saying to the "business" - you mind your business and we'll mind ours. Ours is database and yours is "business" (whatever that means).

They need to be introduced to the concept of LEAST PRIVILEGES - there is no reason under the sun that

a) the testing account needs Carte Blanche access to all of the schemas, no way

b) that the set of objects that it would need access to would not already be fully documented as part of the "system"

Why bother with security at all?


You want to document that which you will be granting - and you would only grant on some of the objects in that list - indexes, no. functions - maybe (maybe not, there isn't any reason they need access to everything)

You will grant on those things that NEED to be granted on.

A test user should not have insert/update/delete on MOST tables - maybe a few, but certainly not MOST and definitely not ALL.

create an emp table and then it'll work....

????

you do not have the create view privilege.


get create view granted to you.