oracle数据库用户怎么查用户权限

select from user_role_privs;

select from user_tab_privs;

select from user_sys_privs;

1 查看 SQL 2005 用户所属数据库角色

use yourdb

go

select DbRole = gname, MemberName = uname, MemberSID = usid

from sysdatabase_principals u, sysdatabase_principals g, sysdatabase_role_members m

where gprincipal_id = mrole_principal_id

and uprincipal_id = mmember_principal_id

order by 1, 2

go

2 查看 SQL 2005 登陆帐户所属服务器角色

use master

go

select SrvRole = gname, MemberName = uname, MemberSID = usid

from sysserver_principals u, sysserver_principals g, sysserver_role_members m

where gprincipal_id = mrole_principal_id

and uprincipal_id = mmember_principal_id

order by 1, 2

go

3 查看 SQL 2005 用户被赋予的权限

use yourdb

go

exec sp_helprotect @username = 'user name'

go

SQL Server中查询用户的对象权限和角色的方法 \x0d\\x0d\-- 查询用户的object权限\x0d\exec sp_helprotect NULL, 'UserName'\x0d\-- 查询用户拥有的role\x0d\exec sp_helpuser 'UserName'\x0d\-- 查询哪些用户拥有指定的系统role\x0d\exec sp_helpsrvrolemember 'sysadmin'\x0d\-- 可查询嵌套role\x0d\WITH tree_roles as\x0d\(\x0d\SELECT role_principal_id, member_principal_id\x0d\FROM sysdatabase_role_members\x0d\WHERE member_principal_id = USER_ID('UserName')\x0d\UNION ALL\x0d\SELECT crole_principal_id,cmember_principal_id\x0d\FROM sysdatabase_role_members as c\x0d\inner join tree_roles\x0d\on tree_rolesmember_principal_id = crole_principal_id\x0d\)\x0d\SELECT distinct USER_NAME(role_principal_id) RoleName\x0d\FROM tree_roles\x0d\\x0d\-- 其他权限相关基本表\x0d\select from sysusers\x0d\select from syspermissions\x0d\\x0d\-- Who has access to my SQL Server instance\x0d\SELECT\x0d\name as UserName, type_desc as UserType, is_disabled as IsDisabled\x0d\FROM sysserver_principals\x0d\where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')\x0d\order by UserType, name, IsDisabled\x0d\\x0d\-- Who has access to my Databases\x0d\SELECT\x0d\dpname as UserName, dptype_desc as UserType, spname as LoginName, sptype_desc as LoginType\x0d\FROM sysdatabase_principals dp\x0d\JOIN sysserver_principals sp ON dpprincipal_id = spprincipal_id\x0d\order by UserType\x0d\\x0d\select from sysdatabase_principals\x0d\\x0d\-- Server Roles\x0d\select\x0d\pname as UserName, ptype_desc as UserType, ppname as ServerRoleName, pptype_desc as ServerRoleType\x0d\from sysserver_role_members roles\x0d\join sysserver_principals p on rolesmember_principal_id = pprincipal_id\x0d\join sysserver_principals pp on rolesrole_principal_id = ppprincipal_id\x0d\where ppname in('sysadmin')\x0d\order by ServerRoleName, UserName\x0d\\x0d\-- Database Roles\x0d\SELECT\x0d\pname as UserName, ptype_desc as UserType, ppname as DBRoleName, pptype_desc as DBRoleType, ppis_fixed_role as IfFixedRole\x0d\FROM sysdatabase_role_members roles\x0d\JOIN sysdatabase_principals p ON rolesmember_principal_id = pprincipal_id\x0d\JOIN sysdatabase_principals pp ON rolesrole_principal_id = ppprincipal_id\x0d\where ppname in('db_owner', 'db_datawriter')\x0d\\x0d\-- What can these users do\x0d\SELECT\x0d\grantorname as GrantorName, dpstate_desc as StateDesc, dpclass_desc as ClassDesc, dppermission_name as PermissionName ,\x0d\OBJECT_NAME(major_id) as ObjectName, GranteeName = granteename\x0d\FROM sysdatabase_permissions dp\x0d\JOIN sysdatabase_principals grantee on dpgrantee_principal_id = granteeprincipal_id\x0d\JOIN sysdatabase_principals grantor on dpgrantor_principal_id = grantorprincipal_id\x0d\where permission_name like '%UPDATE%'

以上就是关于oracle数据库用户怎么查用户权限全部的内容，包括:oracle数据库用户怎么查用户权限、SQLserver怎样为数据库用户指定查询表的权限、sql语句server中怎么查询用户的角色和权限等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！