SAP – SQL Anywhere – Metadata – Security

Objective

Wanted to touch upon a couple of views and procedures one can utilize in SQL Anywhere to review registered principals and privileges assigned to them.

 

Outline

  1. Version
    • @@VERSION
  2. Session Variables
    • user_name()
  3. List Principals
    • SYS.SYSUSER
  4. List All Roles
    • SYS.SYSUSER
      • Filter
        • user_type in
          • 13 — Mutable and removable role
          • 14 — Mutable and removable user extended as role
  5. List Roles for Specific Principal
    • sp_displayroles
  6. List Object Level Permissions
    • sp_objectpermission

Tasks

Get Engine Version Number

@@VERSION

SQL


select [@@VERSION]= @@VERSION

Output

@@version.20190723.0303AM

Explanation

  1. The Version Number is 16.0.0.1324

Session Variables

user_name

Outline

  1. List the username for the currently connected session

SQL


select user_name()

List Principals

SYS.SYSUSER

SQL


 select
          [principal] = tblSSU.user_name

        , [userID] = tblSSU.user_id

        , [objectID] = tblSSU.object_id

        , [tblSSU].[user_dn]

        , [userType]
           = tblSSU.user_type

        , [userTypeDescription]
           = case tblSSU.user_type

                when 1 then 'System Role - Immutable'
                when 5 then 'System Role - Mutable'
                when 9 then 'System Role - Immuutable and Removable'
                when 12 then 'User - Mutable and Removable User'
                when 13 then 'Role - Mutable and Removable Role'
                when 14 then 'User - Extended as Role'

             end

        , tblSSU.[login_policy_id]

        , tblSSU.[failed_login_attempts]

        , [tsPasswordCreation]
              = convert
                     (
                           varchar(30)
                         , tblSSU.[password_creation_time]
                         , 100
                     )

        , [tsLogin]
              = convert
                     (
                           varchar(30)
                         , tblSSU.[last_login_time]
                         , 100
                     )

       -- , tblSSU.*

from   SYS.SYSUSER tblSSU

order by
      tblSSU.[user_name]

Output

SYS.SYSUSER.01.20190722.0919PM

List Roles

SYS.SYSUSER

SQL


 select
          [principal] = tblSSU.user_name

        , [userID] = tblSSU.user_id

        , [objectID] = tblSSU.object_id

        , [tblSSU].[user_dn]

        , [userType]
           = tblSSU.user_type

        , [userTypeDescription]
           = case tblSSU.user_type

                when 1 then 'System Role - Immutable'
                when 5 then 'System Role - Mutable'
                when 9 then 'System Role - Immuutable and Removable'
                when 12 then 'User - Mutable and Removable User'
                when 13 then 'Role - Mutable and Removable Role'
                when 14 then 'User - Extended as Role'

             end

        , tblSSU.[login_policy_id]

        , tblSSU.[failed_login_attempts]

        , [tsPasswordCreation]
              = convert
                     (
                           varchar(30)
                         , tblSSU.[password_creation_time]
                         , 100
                     )

        , [tsLogin]
              = convert
                     (
                           varchar(30)
                         , tblSSU.[last_login_time]
                         , 100
                     )

       -- , tblSSU.*

from   SYS.SYSUSER tblSSU

where tblSSU.[user_type] in
        (
              13 -- 13   Mutable and removable role.
            , 14 -- 14   Mutable and removable user extended as role.
        )
order by
      tblSSU.[user_name]

Output

SYS.SYSUSER.roles.01.20190722.0928PM.PNG

List Roles for Specific Principal

sp_displayroles

Self

Outline

Invoke sp_displayroles.

Pass along null parameter.

SQL – Syntax
SELECT *

FROM sp_displayroles( ) tblDR

SQL – Sample
SELECT *

FROM sp_displayroles( ) tblDR

Output

sp_displayroles.self.01.20190722.0946PM.PNG

Someone Else

Outline

Invoke sp_displayroles.

Pass along principal.

SQL – Syntax
SELECT *

FROM sp_displayroles( {principal}) tblDR

SQL – Sample
SELECT *

FROM sp_displayroles( 'sammie' ) tblDR

Output

sp_displayroles.principal.jd.01.20190722.0952PM

List Object Permissions

sp_objectpermission

Self

Outline

Invoke sp_objectpermission

Pass along null parameter.

SQL – Syntax
SELECT *

FROM sp_objectpermission( ) tblOP

SQL – Sample
SELECT *

FROM sp_objectpermission( ) tblOP

Output

sp_objectpermission.self.01.20190722.1008PM

Someone Else

Outline

Invoke sp_objectpermission.

Pass along principal.

SQL – Syntax
SELECT *

FROM sp_objectpermission( {principal}) tblDR

SQL – Sample
SELECT *

FROM sp_objectpermission( 'sammie' ) tblDR

Output

sp_objectpermission.self.01.20190722.1008PM

References

  1. SAP
    • SQL Anywhere
      • SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server – SQL Reference » Views
        • System views
          • SYS.SYSUSER System View
            Link
        • Procedures and Functions
          • sp_displayroles
            Link
          • sp_objectpermission System Procedure
            Link
      • Variables
        • Global Variables
          Link
      • Functions

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s