Users, roles, privileges, sessions

Users, roles, privileges, sessions mk Thu, 03/28/2013 - 20:37

The user access grants are organized by authorization groups.

sys.auths
name type references description
"id" INTEGER   The unique authorization identifier.
"name" VARCHAR   The SQL authorization name of the user or role.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.users   and   sys.db_user_info
name type references description
"name" VARCHAR sys.auths.name The unique user login name.
"fullname" VARCHAR   Full name of user.
"default_schema" INTEGER sys.schemas.id The default schema for this user.

 

sys.roles
name type references description
"id" INTEGER   The unique role identifier.
"name" VARCHAR   The role name.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.user_role
name type references description
"login_id" INTEGER sys.auths.id The corresponding user identifier.
"role_id" INTEGER sys.roles.id The corresponding role identifier.

 

sys.privileges
name type references description
"obj_id" INTEGER sys.ids.id The identifier of the object for which privilege(s) are given to a certain user or role.
"auth_id" INTEGER sys.auths.id The id of the user or role.
"privileges" INTEGER sys.privilege_codes.privilege_code_id Privileges classification code, see table sys.privilege_codes.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding grantor authorization identifier.
"grantable" INTEGER   Permission to pass privilege rights to other users or roles.
sys.privilege_codes
name type references description
"privilege_code_id" INTEGER   The unique internal code of a privilege or combination of privileges.
"privilege_code_name" VARCHAR   A description of the privilege(s) associated with this code.

 

sys.sessions
name type references description
"sessionid" INTEGER   The internal session identifier.
"username" VARCHAR sys.users.name The naem of the user who created the session.
"login" TIMESTAMP   The login start timestamp of the session.
"idle" TIMESTAMP   The timestamp of the session.
"optimizer" VARCHAR sys.optimizers.name The optimizer pipeline name.
"sessiontimeout" INTEGER   The session timeout time in milliseconds, 0 means timeout is disabled.
"querytimeout" INTEGER   The query execution timeout time in milliseconds, 0 means timeout is disabled.
"workerlimit" INTEGER   The maximum number of worker threads allowed, 0 means limit is disabled.
"memorylimit" INTEGER   The memory limit in MB for this session, 0 means limit is disabled.

Note: the sys.sessions structure has been enhanced from release Jun2020 (11.37.7) onwards. Older versions will show less and some different columns.