0

Our application needs two types of users (every app users need their own credentials for database access)

  1. Admin
  2. General user

We have created a user General User with the following permissions:

  1. Can browse stored procedures/function, but not allowed to to view code inside
  2. Can only select table, but not allowed to insert/update/delete outside of stored procedure execution

I have created a role and added General User to it:

CREATE ROLE proc_executor

GRANT EXECUTE TO proc_executor
GRANT SELECT  TO proc_executor

Now I need role for Admin with the following permissions:

  1. Holds all roles of General User, which is already available
  2. Create new users, both db and server
  3. Alter roles to add new users

There are some restriction for Admin. They can only execute stored procedures and select from tables.

I researched, people have suggested to add role like db_accessadmin, db_securityadmin, sp_addrolemember (not found) which is not sufficient to create new user and in same time breaks the restrictions.

It seems to be tricky to allow to create user permission and same time restrict to stroll stored procedure codes. Permission of SA holds with us, we can't allow client to play with database.

Can anyone help me out?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aadhikari
  • 1
  • 3
  • What do you mead saying "We have created user 'General User' from 'sa' user"? User can be created from login, but you cannot create any user from "sa" login – sepupic Aug 29 '17 at 06:25
  • We logged in through sa user(default user) and created another user, both db and server. – aadhikari Aug 29 '17 at 06:28
  • It doesn't matter how you was logged in when created users/logins. Can you precise WHAT have you done, not under which login did you do something. What login/user and with what permissions did you create or you created nothing and your question is what users and with what permissions are to be created? – sepupic Aug 29 '17 at 06:33
  • Now I cannot understand why did you give individual permissions (EXECUTE) to General user if he inherits same permission from proc_executor role. Why don't you create 2 roles, 1 for "admins" and 1 for "general users" and give permissions to these roles? – sepupic Aug 29 '17 at 07:41
  • We created a server login 'User01' and a database user 'User01'. We have created new role having permission to execute and select. and added 'User01' in this role. We were able to restrict user from seeing stored procedure code and insert/alter/delete in table outside of procedure execution. Its done. But we need another user(Admin) who will create these types of user in future with similar roles. How to create this user where it can create new users and grant permissions and at the same time not allowed to stroll Stored procedure codes as 'User01'. – aadhikari Aug 29 '17 at 07:46
  • Grant to your admin EXEC on stored procedure with EXECUTE AS user = 'dbo' clause that will create requested user while deny EXEC on this proc to Generel user Role – sepupic Aug 29 '17 at 07:49
  • I mean, encapsulate all the logic that admins can do in some stored procedures and grant EXECUTE on these procedures to admins only, and DENY EXEC on these procedures to General user Role – sepupic Aug 29 '17 at 07:51

1 Answers1

0

Why not creating the security schema of your application alone. You can have the following tables:

-- storing users details
CREATE [dbo].[SecurityUsers] TABLE
(
    [SecurityUserID] BIGINT
   ,...
   ,...
);

-- storing groups details
CREATE [dbo].[SecurityGroups] TABLE
(
    [SecurityGroupID] INT
   ,[SecurityGroupName] NVARCHAR(128)
   ,[SecurityGroupDescription] NVARCHAR(1024)
);

-- storing group-users mapping
CREATE [dbo].[SecurityGroupsMembership]
(
    [SecurityGroupID] INT
   ,[SecurityUserID] BIGINT
);

The you can write a security check at the begging of your function or stored procedure like this:

if user is not member of `update data`
begin;
    return;
end;

In SQL Server 2016 SP1 you can even mapped such security function on table SELECT using row-level security.

Your structure can be more complex - you can table for security policies or/and security roles. You need to add a interface (maybe) for setting user rights/membership.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • we want to restrict user to browse through Object Explorer. with this restriction user can't stroll stored procedures(codes). Bit they should be allowed to execute, which done through front application. We have done this. But addition to this, we need permission to create user along with above mentioned restrictions. – aadhikari Aug 29 '17 at 07:05