Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Getting Priviledges of user in Oracle/SQL Server (http://www.go4expert.com/forums/getting-priviledges-user-oracle-sql-t132/)

go4expert 20Aug2004 15:21

Getting Priviledges of user in Oracle/SQL Server
I would like to get the priviledges I [or any user] have for the specified database or object. I know the stored procedures like sp_helpuser and sp_helplogins exist for SQL Server but I would like to know the general sql for oracle and/or sql server.

Amit Ray 28Aug2004 13:31

Re: Getting Privileges of user in Oracle/SQL Server
1 Attachment(s)
Hi ..

Oracle privileges can be largely classified as : System privileges and Object privileges. The following tables contain all the privilege information in Oracle :

DBA_SYS_PRIVS : The System Previleges.

DESC DBA_TAB_PRIVS : The Table Privileges.

DESC DBA_ROLE_PRIVS : Roles consist of a set of privileges which can be assigned to users instead of granting privileges one by one.

DESC DBA_COL_PRIVS : The Table Column Privileges.

The text attachment contains a sample session in a Oracle DB which will make a lot things clear.

Amit Ray. :cool:

shabbir 8Nov2004 14:28

Re: Getting Priviledges of user in Oracle/SQL Server
You can also use sp_helpuser Stored Procedure in SQL Server

Here is the complete description of it


Originally Posted by Microsoft MSDN
sp_helpuser [ [ @name_in_db = ] 'security_account' ]

[@name_in_db =] 'security_account'

Is the name of a SQL Server user, Windows NT user, or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If security_account is not specified, the system procedure reports on all users, Windows NT users, and roles in the current database. When specifying a Windows NT user, specify the name that the Windows NT user is known by in the database (added using sp_grantdbaccess).

Return Code Values
0 (success) or 1 (failure)

Use sp_helpsrvrole and sp_helpsrvrolemember to return information about fixed server roles.

Executing sp_helpuser for a database role is equivalent to executing sp_helpgroup for that database role.


Execute permissions default to the public role.

All times are GMT +5.5. The time now is 02:28.