%SYSTEM.SQL.Security

Check if user has SQL privilege for a particular action. This does not check grant privileges.

Parameters:

Username

Name of the user to check. Required.

ObjectType

Required. Specifies the type to check the privilege of. ObjectTypes are 1 (table), 3 (view), 5 (schema), 6 (ml configuration), 7 (foreign server), 9 (procedure).

Object

Required. The name the object to check the privilege of.

For example, ObjectType and Object could be "1" and "Sample.Person", or "9" and "SQLUser.My_Procedure".

Action

Comma delimited string of actions letters to check privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e,l" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE,USE (USE for ML Configurations/Foreign Server). Privilege "e" is only allowed for Procedures. Privilege "l" is only allowed for ML Configurations and Foreign Servers. CheckPrivilege will only return 1 if the user has privileges on all Actions specified. Required.

Namespace

Namespace object resides in (optional) default is current namespace

Returns:

1 - if the Username does have the privilege

0 - if the Username does not have the privilege

%Status - if CheckPrivilege call is reporting an error

Notes:

If Username is a user with the %All role, CheckPrivilege will return 1 even if the Object does not exist.

If the user calling CheckPrivilege is not the same as Username, the calling user must hold the %Admin_Secure:"U" privilege.

Example:

• Do $SYSTEM.SQL.Security.CheckPrivilege("Miranda",3,"SQLUser.Person","s","PRODUCT")

Check if user has SQL grant privilege for a particular action.

Parameters:

Username

Name of the user to check. Required.

ObjectType

Required. Specifies the type to check the grant privilege of. ObjectTypes are 1 (table), 3 (view), 5 (schema), 6 (ml configuration), 7 (foreign server), 9 (procedure).

Object

Required. The name the object to check the grant privilege of.

For example, ObjectType and Object could be "1" and "Sample.Person", or "9" and "SQLUser.My_Procedure".

Action

Comma delimited string of actions letters to check grant privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e,l" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE,USE (USE for ML Configurations/ForeignServers). Privilege "e" is only allowed for Procedures. Privilege "l" is only allowed for ML Configurations and Foreign Servers. CheckPrivilegeWithGrant will only return 1 if the user has grant privileges on all Actions specified. Required.

Namespace

Namespace object resides in (optional) default is current namespace

Returns:

1 - if the Username does have the privilege

0 - if the Username does not have the privilege

%Status - if CheckPrivilegeWithGrant call is reporting an error

Notes:

If Username is a user with the %All role, CheckPrivilegeWithGrant will return 1 even if the Object does not exist.

If the user calling CheckPrivilegeWithGrant is not the same as Username, the calling user must hold the %Admin_Secure:"U" privilege.

Example:

• Do $SYSTEM.SQL.Security.CheckPrivilegeWithGrant($username,1,"HHR.ProductionValues","s,i,u,d","USER")

GrantPrivilege lets you grant an ObjPriv to a User via this call instead of using the SQL GRANT statement. This does not include grant privileges.

$SYSTEM.SQL.Security.GrantPrivilege(ObjPriv,ObjList,Type,User)

Parameters:

ObjPriv

Comma delimited string of actions to grant. * for all actions:

• Alter
• Select
• Insert
• Update
• Delete
• References
• Execute
• Use
• or any combination

ObjList

* for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas). The specified schemas cannot be empty.

Type

Table, View, Schema, Stored Procedures, ML Configuration, or Foreign Server

User

Comma delimited list of users

GrantPrivilegeWithGrant lets you grant an ObjPriv, WITH GRANT OPTION, to a User

$SYSTEM.SQL.Security.GrantPrivilegeWithGrant(ObjPriv,ObjList,Type,User)

Parameters:

ObjPriv

Comma delimited string of actions to grant. * for all actions:

• Alter
• Select
• Insert
• Update
• Delete
• References
• Execute
• Use
• or any combination

ObjList

* for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas). The specified schemas cannot be empty.

Type

Table, View, Schema, Stored Procedure, ML Configuration, or Foreign Server

User

Comma delimited list of users

RevokePrivilege lets you revoke an ObjPriv from a User via this call instead of using the SQL REVOKE statement

$SYSTEM.SQL.Security.RevokePrivilege(ObjPriv,ObjList,Type,User,wGrant,Cascade,AsGrantor)

Parameters:

ObjPriv

Comma delimited string of actions to grant. * for all actions:

• Alter
• Select
• Insert
• Update
• Delete
• References
• Execute
• Use
• or any combination

ObjList

* for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)

Type

Table, View, Schema, ML Configuration, Foreign Server, or Stored Procedures

User

Comma delimited list of users

wGrant

0/1 for WITH GRANT OPTION

Cascade

0/1 cascade revoke?

AsGrantor

Alternate User to remove privileges for. AsGrantor can be a user name, a comma-separated list of user names, or "*".

This entry point can be used to determine if a role exists.

Parameters:

rolename

Name of the role to check.

Examples:

• Write $SYSTEM.SQL.Security.RoleExists("SalesManager") // Writes a 1 if role SalesManager exists

This method can also be called as a Stored Procedure named %SYSTEM_SQL.Security_RoleExists(rolename)

This entry point can be used to determine if a user exists.

Parameters:

username

Name of the user to check.

Examples:

• Write $SYSTEM.SQL.Security.UserExists("Robert") // Writes a 1 if user Robert exists

This method can also be called as a Stored Procedure named %SYSTEM_SQL.Security_UserExists(username)