-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
%Library.SQLAllPrivileges() Procedure
%Library.SQLCatalogPriv:SQLAllPrivileges(system)
Returns all SQL privileges granted to the current user OR to any roles the current user holds.
If the current user holds %All or %Admin_Secure:USE, returns information about SQL privileges assigned to
all users/roles.
This query takes the following parameters:
objectFilter - Optional, default=""; For object privileges, limit the objects returned. The default
value will return all objects for the users/roles whose information the calling user is privileged
to see. This parameter offers limited wildcard support, where the filter may contain a
suffix of '*' to imply every name sorted after (and including) the given prefix. This filter
can include a fully qualified name, and a wildcard may appear in the schema and/or the table
portion of the name. Note that delimited identifier names that match the filter term take
priority over wildcard expansion. For example, the schema portion of 'sch*.tab*' will not be
expanded if there is a schema called 'sch*'; the same is true for the table portion. Matches
are considered on a case-insensitive basis. Any suffix after a wildcard is ignored for expansion,
but is considered valid if it directly matches an object name. For example, 'sch*ma.ta*le'
will match an object called 'sch*ma.ta*le' if it exists, but if no such object exists, then
it will be treated equivalently to 'sch*.ta*'. Any filter that does not include a period separator
is interpreted as a schema wildcard. Any object which the user is not privileged to view
will be skipped.
privilegeType - Optional, default='ALL'. Either 'ADMIN', 'OBJECT', or 'ALL'. If 'ADMIN', returns only
ADMIN privileges (e.g. %CREATE_TABLE). If 'OBJECT', returns only OBJECT privileges
(e.g. SELECT on
). Any value other than 'ADMIN' or 'OBJECT' is treated as 'ALL'.
system - Optional, default=0; Whether to include system tables/views (e.g. INFORMATION_SCHEMA tables).
ROWSPEC =
SUBJECT_NAME:%Library.String - Name of the user or role that has the privilege
SUBJECT_TYPE:%Library.String - USER or ROLE, depending on the specific subject for this privilege
PRIVILEGE_NAME:%Library.String - Privilege granted. Could be an object privilege (e.g. SELECT) or an admin privilege (e.g. %CREATE_TABLE)
PRIVILEGE_TYPE:%Library.String - Type of privilege granted. "ADMIN" or "OBJECT".
OBJECT_TYPE:%Library.String - TABLE, VIEW, STORED PROCEDURE, ML CONFIGURATION, FOREIGN SERVER, or NULL if this is an admin privilege
OBJECT_NAME:%Library.String - Name of the item for which the subject was granted privs on. NULL if this is an admin privilege
GRANTED_BY:%List - The user(s) that granted this privilege to the subject
HAS_GRANT_OPTION:%Library.String - "Yes" or "No" whether the user has GRANT option (or ADMIN option for admin privileges), allowing them to grant this privilege to other users
GRANTED_VIA:%Library.String - "Direct", "SuperUser", "Role: ", "Owner Privilege", "Schema Privilege", or "_PUBLIC" - The means through which the user was granted the priv
HAS_COLUMN_PRIV:%Library.Boolean - 1 if this table or view has any privileges defined at the column level, otherwise 0.
Note this query returns a full cross product of users/roles and their privileges. If a user is granted the same privilege
through multiple channels (e.g. Through a schema privilege, and directly to their user) then all granting channels will appear.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects TYPE As %String(MAXLEN=9) As TYPE, NAME As %String(MAXLEN=128), PRIVILEGE As %String(MAXLEN=10), GRANTED_BY As %String, GRANT_OPTION As %String(MAXLEN=3)
Get a list of Privileges granted to a Role
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLRolePrivleges Procedure
%SQLCatalogPriv_SQLRolePrivileges('rolename')
Takes 1 %String/VARCHAR type parameter which is the name of a Role.
Returns all privileges granted to the role.
ROWSPEC = TYPE:%String - TABLE or VIEW or PROCEDURE
NAME:%String - Name of the object with privs granted to the role
PRIVILEGE:%String - Privilege granted
GRANTED_BY:%String - SQL user name who granted the privilege
GRANT_OPTION:%String - 'Yes' or 'No', privilege granted with grant option
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get a list of all Users/Roles granted a Role.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLRoleUser Procedure
%SQLCatalogPriv_SQLRoleUser('rolename')
Takes 1 %String/VARCHAR type parameter which is the name of a Role.
Returns all SQL Users granted the role
ROWSPEC = NAME:%Library.String - Name of the SQL user granted the role
TYPE:%Library.String - ROLE or USER, type of UID it is granted too
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get a list of all SQL Roles.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLRoles Procedure
%SQLCatalogPriv_SQLRoles()
Takes no parameters
Returns all SQL Roles defined
ROWSPEC = ROLE_NAME:%Library.String - Name of the role
DESCRIPTION:%Library.String - Role's Desription
CREATED_BY:%Library.String - SQL User who created the Role
ESCALATION_ONLY:%Library.Boolean - Can this role be assigned at login, or can it only be used for escalation?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
If the user exists, return the username; otherwise nothing
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLUserExists Procedure
%SQLCatalogPriv_SQLUserExists('username')
Takes 1 %String/VARCHAR type parameter which is the name of an SQL user.
Returns the SQL username if the user exists, otherwise nothing:
ROWSPEC = USER_NAME:%String - Name of the SQL user
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get a list of Privileges granted to a SQL User
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLUserPrivs Procedure
%SQLCatalogPriv_SQLUserPrivs('username')
Takes 1 %String/VARCHAR type parameter which is the name of a SQL User.
Returns all privileges granted to the user.
ROWSPEC = TYPE:%Library.String - TABLE, VIEW, STORED PROCEDURE
NAME:%Library.String - Name of the item with privs granted to the user
PRIVILEGE:%Library.String - Privilege granted
GRANTED_BY:%Library.String - SQL user name who granted the privilege
GRANT_OPTION:%Library.String - 'Yes' or 'No', privilege granted with grant option
GRANTED_VIA:%Library.String - Direct, SuperUser, Role, _PUBLIC - How was the user granted the priv
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get a list of all Roles granted to an SQL User.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLUserRole Procedure
%SQLCatalogPriv_SQLUserRole('username')
Takes 1 %Library.String/VARCHAR type parameter which is the name of a SQL user.
Returns all Roles granted to the user
ROWSPEC = ROLE_NAME:%Library.String - Name of the role granted to the user
ADMIN_OPTION:%Library.Boolean - 1/0 Role granted with Admin Option?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get all System Privileges granted to the user/role 'user'
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLUserSysPriv Procedure
%SQLCatalogPriv_SQLUserSysPrivs('username')
Takes 1 %String/VARCHAR type parameter which is the name of an SQL User or Role.
Returns all system privileges granted to a user or role
ROWSPEC = PRIVILEGE:%Library.String - Name of the system privilege granted
ADMIN_OPTION:%Library.String - 'Yes' or 'No' whether the privilege was granted with admin option
GRANTED_VIA:%Library.String - Direct, SuperUser, Role, _PUBLIC - How was the user granted the priv
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Get a list of all Users.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLUsers Procedure
%SQLCatalogPriv_SQLUsers()
Takes no parameters
Returns a list of SQL users:
ROWSPEC = USERNAME:%Library.String - Name of the SQL user
DESCRIPTION:%Library.String - SQL User description
ENABLED:%Library.String - Yes/No: is user definition enabled?
NAMESPACE:%Library.String - User's Namespace
ROUTINE:%Library.String - Routine
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
