103 lines
4.9 KiB
MySQL
103 lines
4.9 KiB
MySQL
|
WITH T AS
|
|||
|
(
|
|||
|
SELECT CONNEXION.name AS LOGIN_NAME,
|
|||
|
GRANTEE.default_schema_name AS DEFAULT_SCHEMA,
|
|||
|
PRIVILEGE.state_desc AS SQL_ORDER,
|
|||
|
GRANTOR.name AS GRANTOR,
|
|||
|
GRANTEE.name AS GRANTEE,
|
|||
|
PRIVILEGE."permission_name" AS PRIVILEGE,
|
|||
|
s.name AS OBJECT_SCHEMA,
|
|||
|
o.name AS OBJECT_NAME,
|
|||
|
LTRIM(STUFF((SELECT ', ' + name
|
|||
|
FROM sys.columns AS c
|
|||
|
WHERE PRIVILEGE.major_id = c.object_id
|
|||
|
AND PRIVILEGE.minor_id = c.column_id
|
|||
|
FOR XML PATH('')), 1, 1, '' )) AS COLUMN_LIST,
|
|||
|
PRIVILEGE.class_desc AS OBJECT_CLASS,
|
|||
|
CASE PRIVILEGE.class
|
|||
|
WHEN 0 THEN DB_NAME()
|
|||
|
WHEN 1 THEN o.type_desc
|
|||
|
WHEN 3 THEN ss.name COLLATE database_default
|
|||
|
WHEN 4 THEN dbp.name
|
|||
|
WHEN 5 THEN asb.name
|
|||
|
WHEN 6 THEN typ.name
|
|||
|
WHEN 10 THEN xsc.name
|
|||
|
END AS OBJECT_TYPE_OR_NAME
|
|||
|
FROM sys.database_principals AS GRANTEE
|
|||
|
LEFT OUTER JOIN sys.server_principals AS CONNEXION
|
|||
|
ON GRANTEE.sid = CONNEXION.sid
|
|||
|
LEFT OUTER JOIN sys.database_permissions AS PRIVILEGE
|
|||
|
ON GRANTEE.principal_id = PRIVILEGE.grantee_principal_id
|
|||
|
LEFT OUTER JOIN sys.database_principals AS GRANTOR
|
|||
|
ON PRIVILEGE.grantor_principal_id = GRANTOR.principal_id
|
|||
|
-- lien avec les objets primaires
|
|||
|
LEFT OUTER JOIN sys.objects AS o
|
|||
|
ON PRIVILEGE.major_id = o.object_id AND PRIVILEGE.class = 1
|
|||
|
LEFT OUTER JOIN sys.schemas AS s
|
|||
|
ON o.schema_id = s.schema_id
|
|||
|
-- lien avec les sch<63>mas
|
|||
|
LEFT OUTER JOIN sys.schemas AS ss
|
|||
|
ON PRIVILEGE.major_id = ss.schema_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 3
|
|||
|
-- lien avec les "principals" de la base de donn<6E>es
|
|||
|
LEFT OUTER JOIN sys.database_principals AS dbp
|
|||
|
ON PRIVILEGE.major_id = dbp.principal_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 4
|
|||
|
-- lien avec les "assembly"
|
|||
|
LEFT OUTER JOIN sys.assemblies AS asb
|
|||
|
ON PRIVILEGE.major_id = asb.assembly_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 5
|
|||
|
-- lien avec les "type" 6 =
|
|||
|
LEFT OUTER JOIN sys.types AS typ
|
|||
|
ON PRIVILEGE.major_id = typ.user_type_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 6
|
|||
|
-- lien avec les collections de sch<63>mas XML
|
|||
|
LEFT OUTER JOIN sys.xml_schema_collections AS xsc
|
|||
|
ON PRIVILEGE.major_id = xsc.xml_collection_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 10
|
|||
|
-- lien avec les types de message
|
|||
|
LEFT OUTER JOIN sys.service_message_types AS smt
|
|||
|
ON PRIVILEGE.major_id = smt.message_type_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 15
|
|||
|
-- lien avec les contrats de service
|
|||
|
LEFT OUTER JOIN sys.service_contracts AS sc
|
|||
|
ON PRIVILEGE.major_id = sc.service_contract_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 16
|
|||
|
-- lien avec les services
|
|||
|
LEFT OUTER JOIN sys.services AS srv
|
|||
|
ON PRIVILEGE.major_id = srv.service_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 17
|
|||
|
-- lien avec les liaisons de service distant
|
|||
|
LEFT OUTER JOIN sys.remote_service_bindings AS rsb
|
|||
|
ON PRIVILEGE.major_id = rsb.remote_service_binding_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 18
|
|||
|
-- lien avec les 19 = Itin<69>raire
|
|||
|
LEFT OUTER JOIN sys.routes AS r
|
|||
|
ON PRIVILEGE.major_id = r.route_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 19
|
|||
|
-- lien avec les cataloguec de texte int<6E>gral
|
|||
|
LEFT OUTER JOIN sys.fulltext_catalogs AS ftc
|
|||
|
ON PRIVILEGE.major_id = ftc.fulltext_catalog_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 23
|
|||
|
-- lien avec les cl<63>s sym<79>triques
|
|||
|
LEFT OUTER JOIN sys.symmetric_keys AS sk
|
|||
|
ON PRIVILEGE.major_id = sk.symmetric_key_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 24
|
|||
|
-- lien avec les certificats
|
|||
|
LEFT OUTER JOIN sys.certificates AS ctf
|
|||
|
ON PRIVILEGE.major_id = ctf.certificate_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 25
|
|||
|
-- lien avec les cl<63>s asym<79>triques
|
|||
|
LEFT OUTER JOIN sys.asymmetric_keys AS ask
|
|||
|
ON PRIVILEGE.major_id = ask.asymmetric_key_id
|
|||
|
AND minor_id = 0 AND PRIVILEGE.class = 26
|
|||
|
WHERE GRANTEE.type = 'S' --> SQL_USER
|
|||
|
)
|
|||
|
SELECT COALESCE (N'EXECUTE AS USER = '''+ GRANTOR + N'''; ' +
|
|||
|
SQL_ORDER + N' ' + PRIVILEGE + N' ON ' +
|
|||
|
COALESCE('[' + OBJECT_SCHEMA + N'].[' + OBJECT_NAME +'] ' COLLATE French_CI_AI +
|
|||
|
COALESCE(N'(' + COLUMN_LIST + N')' COLLATE French_CI_AI, ''),
|
|||
|
OBJECT_CLASS + N'::' + OBJECT_TYPE_OR_NAME COLLATE French_CI_AI) +
|
|||
|
N' TO ' + GRANTEE +'; REVERT;' COLLATE French_CI_AI, '') AS SQL_COMMAND,
|
|||
|
*
|
|||
|
FROM T;
|