Getting Priviledges of user in Oracle/SQL Server

go4expert's Avatar, Join Date: Aug 2004
Team Leader
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.
0
Amit Ray's Avatar, Join Date: Jul 2004
Contributor
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.
Attached Files
File Type: txt Oracle Privileges.txt (4.0 KB, 147 views)

Last edited by Amit Ray; 28Aug2004 at 13:36..
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
You can also use sp_helpuser Stored Procedure in SQL Server

Here is the complete description of it

Quote:
Originally Posted by Microsoft MSDN
Syntax
sp_helpuser [ [ @name_in_db = ] 'security_account' ]
Arguments

[@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)

Remarks
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.

Permissions

Execute permissions default to the public role.