Sorry, your browser does not support JavaScript or JavaScript is disabled. Please enable JavaScript or use another browser to have a better experience.

%Library.SQLCatalogPriv

class %Library.SQLCatalogPriv

SQL Catalog Queries - Privileged

Parameters

parameter SQLPUBLICSECURITY = {"SQLCatalogPriv_SQLUsers":"E"};

Queries

query SQLRolePrivileges(role As %String)

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLRoleUser(role As %String)

Selects NAME As %Library.String, USER As %Library.String

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLRoles()

Selects ROLE_NAME As %Library.String, DESCRIPTION As %Library.String, CREATED_BY As %Library.String, ESCALATION_ONLY As %Library.Boolean

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?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLUserExists(user As %String)

Selects USER_NAME As %String

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLUserPrivs(user As %String)

Selects TYPE As %Library.String, NAME As %Library.String, PRIVILEGE As %Library.String(MAXLEN=10), GRANTED_BY As %Library.String, GRANT_OPTION As %Library.String(MAXLEN=3), GRANTED_VIA As %Library.String

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLUserRole(user As %String)

Selects ROLE_NAME As %Library.String, ADMIN_OPTION As %Library.Boolean

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?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLUserSysPrivs(user As %String)

Selects PRIVILEGE As %String, ADMIN_OPTION As %String, GRANTED_VIA As %Library.String

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

query SQLUsers()

Selects USERNAME As %Library.String, DESCRIPTION As %Library.String, ENABLED As %Library.String, NAMESPACE As %Library.String, ROUTINE As %Library.String

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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Feedback