This content has been marked as final. Show 5 replies
What's wrong? You are opening the proverbial barn doors ito security. That is not how security should be done - allowing a schema to create any procedural code anywhere in the database. Or select data from any table.
Just how do you think Sony's PS network was hacked and millions of users's credit card data stolen?
By this type of insecure stuff-security approach to security.
A schema needs the minimum privs in order to achieve its goals and requirements. Nothing more.
In addition to this, you may want to decide on assign a resource profile and specific roles and so on. In some cases you also may want to allow the schema to create types, private database links and synonyms, materialised views, etc.
// standard logical database schema, 10Gb space allocation create user HRDB identified by <secret password here> default tablespace USERS quota 10G on USERS; // configure the basic security layer for the schema grant create session, --// allow client-server connections to schema create table, create trigger, --// allow to create standard db objects create sequence, create view, --// allow access to defining extended objects create procedure --// allow creating stored proc code to HRDB;
No grant any access. No access to SYS code and objects. By default. Anything else is a security exception that needs sound justification.
But when I try to create a procedure with that user I get, insufficient proveleges error.
Is there some reason you don't want to post the procedure DDL and the actual error you are getting?
That error can be caused if your procedure code tries to reference ANY object that it doesn't have privileges to.
So without knowing what the code is doing how can we tell what the problem is?