Forum Stats

  • 3,782,431 Users
  • 2,254,644 Discussions
  • 7,880,078 Comments

Discussions

Ability to create stored procedure in different schema

Hi-

I recently joined the community and have question regarding create stored procedure and permissions.

Scenario:

We have a situation where some of our users have permissions to create procedures in production environment and have access to many of the key production tables. These users create and test procedures in production environment. Please don't ask why, as i am new to this company.

They login to the core business schema and let's call this schema as "core_app" and perform all their testing and validation here. 

The users create number of stored procedures in a day as per business requirement.

It's not simple to just take away the permissions from these users as they are numerous stored procedures and functions created within this core_app schema and these procedures are interdependent on one another.

The end users create new procedure based on different logic as per business or customer requirement. For e.g. they will created new procedure called new_proc1, for a customer requirement. This new proc calls other set of functions and procedures (and there are almost set of 50+ func and procs on which these new procs rely on)

Possible solution:

In order for the end user to not login to the core schemas, one solution we can think of is to create a bridge schema, which would have access to these 50+ func and procs and the set of base tables from the "core_app" schema.

Now, the challenge is how can we allow the end users to not login to the bridge schema and also create new procedures in the bridge schema. 

I was thinking about creating a wrapper stored procedure called sp_bridge_proc in the bridge schema, which the end users would have execute permissions. 

In the simplest form, this sp_bridge_proc looks this:

create or replace procedure (v_sql clob)

as

begin

execute immediate v_sql;

end;

When executed, this stored proc will create new stored procedure in the bridge schema.

Keeping security and simplicity in mind, are there any alternative approaches, which we can use, to not allow the end users to login to the core schema and still create stored procedures either in the main schema or bridge schema?


Thanks in advance