Accorder l'accès à tous les objets (avec quelques exceptions) à un rôle


1

Dans SQL Server 2014, je souhaite créer un rôle qui aura accès (sélection, suppression, mise à jour, insertion, exécution) à toutes les tables, vues, procédures stockées, à l'exception d'une certaine liste de tables contenant des données sensibles auxquelles seul l'utilisateur «sa» devrait avoir accès.

J'ai essayé les éléments suivants:

Utilisation de Studio de gestion SQL Server, dans la base de données (Testcompany) -> Sécurité -> Rôles -> Rôles de base de données, créer le rôle de base de données "CompanyAdmin".

Exécutez la requête suivante:

GRANT SELECT,INSERT, UPDATE, DELETE, EXEC ON DATABASE::TESTCOMPANY TO CompanyAdmin 

Puis:

DENY SELECT, INSERT, UPDATE, DELETE ON TestTable1 TO CompanyAdmin; 
--Repeat for all tables to secure 

Dans Management Studio, serveur -> Sécurité -> Logins, créez une nouvelle connexion "testuser". Server Roles = "public" uniquement. Ensuite, allez à Testcompany -> Sécurité -> Utilisateurs -> Nouvel utilisateur:

  • type Utilisateur: SQL avec connexion
  • Nom d'utilisateur: testuser
  • Nom d'utilisateur: testuser
  • schéma par défaut: dbo
  • adhésion: CompanyAdmin

Une fois connecté en tant que testuser, tout semble bien, je vois toutes les tables sauf ceux dans les DENY Permissi ons. MAIS:

  • Si, à l'avenir, l'utilisateur « sa » crée une vue sur les tables sécurisées, l'utilisateur testuser a accès et peut visualiser les données qu'il retourne

  • Dans SQL Server Management studio, je peux aller au serveur -> sécurité -> Logins et je vois « sa » propriétés de connexion

ce que je prends la bonne approche ou est-il une meilleure façon d'accomplir ce que je veux faire?

C'est la base de données pour l'application de comptabilité de notre société et le logiciel est conçu pour avoir tout dans la même base de données.

2

La triste vérité est que vous ne pouvez rien faire en matière d'ordinateur sur les activités "sa".En effet, le 'sa' (ou un membre du rôle serveur sysadmin) est défini pour pouvoir faire à peu près n'importe quoi.

Donc, bien sûr, vous ne devriez avoir que sysadmin's que vous pouvez faire confiance pour faire de leur mieux pour respecter les normes de la base de données.Mais même le meilleur sysadminest imparfait .Vous pouvez donc configurer des audits pour rendre compte des modifications apportées à la base de données.

(Ou, plus low tech, vérifiez périodiquement vos vues pour un nom de table restreint dans une vue.)

Cela dépend donc de ce qui vous préoccupe et des efforts que vous devez déployer pour protéger les ressources sécurisées.

En termes de vues qui exposent des informations sécurisées, vous pouvez refuser des autorisations sur une vue.Même si un sysadmin créé la vue, le DENY de la vue pour les personnes sans approbation empêchera leur accès.

Peut-être que toutes les vues à haute sécurité pourraient être nommées quelque chose comme SecureViewXYZZY afin de souligner la raison de ces vues et de limiter le nombre de personnes pouvant les utiliser.