Home » RDBMS Server » Security » View the priviledges of a user/account
View the priviledges of a user/account [message #16582] Fri, 03 December 2004 07:01 Go to next message
pirya
Messages: 1
Registered: December 2004
Junior Member
I want to view what priviledges a certain account has... How do I do that ? Like if the account can create tables, access tables, create stored procedures, etc..

Is this possible?

 
Re: View the priviledges of a user/account [message #16585 is a reply to message #16582] Fri, 03 December 2004 11:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can query the various %PRIV% data dictionary views. For example, you can query USER_SYS_PRIVS to find the system privileges. You can also query the USER_ROLE_PRIVS to find the roles granted to a user. Then you can query the ROLE_SYS_PRIVS to find the system privileges granted those roles, which the user also inherits. You can also query ROLE_ROLE_PRIVS to find the roles granted to other roles and so on. Please see the example below.

scott@ORA92> select * from user_sys_privs where username = 'SCOTT'
  2  /

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          CREATE ANY TABLE                         NO
SCOTT                          UNLIMITED TABLESPACE                     NO

scott@ORA92> select * from user_role_privs where username = 'SCOTT'
  2  /

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT                          CONNECT                        NO  YES NO
SCOTT                          DBA                            NO  YES NO
SCOTT                          JAVAUSERPRIV                   NO  YES NO
SCOTT                          RESOURCE                       NO  YES NO
SCOTT                          SEVOD_ROLE                     NO  YES NO

scott@ORA92> select * from role_sys_privs where role in
  2  (select role from user_role_privs where username = 'SCOTT')
  3  /

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            AUDIT ANY                                YES
DBA                            DROP USER                                YES
DBA                            RESUMABLE                                YES
DBA                            ALTER USER                               YES
DBA                            ANALYZE ANY                              YES
DBA                            BECOME USER                              YES
DBA                            CREATE ROLE                              YES
DBA                            CREATE RULE                              YES
DBA                            CREATE TYPE                              YES
DBA                            CREATE USER                              YES
DBA                            CREATE VIEW                              YES
DBA                            ALTER SYSTEM                             YES
DBA                            AUDIT SYSTEM                             YES
DBA                            CREATE TABLE                             YES
DBA                            DROP PROFILE                             YES
DBA                            ALTER PROFILE                            YES
DBA                            ALTER SESSION                            YES
DBA                            DROP ANY ROLE                            YES
DBA                            DROP ANY RULE                            YES
DBA                            DROP ANY TYPE                            YES
DBA                            DROP ANY VIEW                            YES
DBA                            QUERY REWRITE                            YES
DBA                            ALTER ANY ROLE                           YES
DBA                            ALTER ANY RULE                           YES
DBA                            ALTER ANY TYPE                           YES
DBA                            ALTER DATABASE                           YES
DBA                            CREATE CLUSTER                           YES
DBA                            CREATE LIBRARY                           YES
DBA                            CREATE PROFILE                           YES
DBA                            CREATE SESSION                           YES
DBA                            CREATE SYNONYM                           YES
DBA                            CREATE TRIGGER                           YES
DBA                            DROP ANY INDEX                           YES
DBA                            DROP ANY TABLE                           YES
DBA                            GRANT ANY ROLE                           YES
DBA                            LOCK ANY TABLE                           YES
DBA                            UNDER ANY TYPE                           YES
DBA                            UNDER ANY VIEW                           YES
DBA                            ALTER ANY INDEX                          YES
DBA                            ALTER ANY TABLE                          YES
DBA                            CREATE ANY RULE                          YES
DBA                            CREATE ANY TYPE                          YES
DBA                            CREATE ANY VIEW                          YES
DBA                            CREATE OPERATOR                          YES
DBA                            CREATE RULE SET                          YES
DBA                            CREATE SEQUENCE                          YES
DBA                            CREATE SNAPSHOT                          YES
DBA                            DROP TABLESPACE                          YES
DBA                            UNDER ANY TABLE                          YES
DBA                            ALTER TABLESPACE                         YES
DBA                            BACKUP ANY TABLE                         YES
DBA                            CREATE ANY INDEX                         YES
DBA                            CREATE ANY TABLE                         YES
DBA                            CREATE DIMENSION                         YES
DBA                            CREATE INDEXTYPE                         YES
DBA                            CREATE PROCEDURE                         YES
DBA                            DELETE ANY TABLE                         YES
DBA                            DROP ANY CLUSTER                         YES
DBA                            DROP ANY CONTEXT                         YES
DBA                            DROP ANY LIBRARY                         YES
DBA                            DROP ANY OUTLINE                         YES
DBA                            DROP ANY SYNONYM                         YES
DBA                            DROP ANY TRIGGER                         YES
DBA                            EXECUTE ANY RULE                         YES
DBA                            EXECUTE ANY TYPE                         YES
DBA                            INSERT ANY TABLE                         YES
DBA                            MANAGE ANY QUEUE                         YES
DBA                            SELECT ANY TABLE                         YES
DBA                            UPDATE ANY TABLE                         YES
DBA                            ALTER ANY CLUSTER                        YES
DBA                            ALTER ANY LIBRARY                        YES
DBA                            ALTER ANY OUTLINE                        YES
DBA                            ALTER ANY TRIGGER                        YES
DBA                            COMMENT ANY TABLE                        YES
DBA                            CREATE TABLESPACE                        YES
DBA                            DEQUEUE ANY QUEUE                        YES
DBA                            DROP ANY OPERATOR                        YES
DBA                            DROP ANY RULE SET                        YES
DBA                            DROP ANY SEQUENCE                        YES
DBA                            DROP ANY SNAPSHOT                        YES
DBA                            ENQUEUE ANY QUEUE                        YES
DBA                            FORCE TRANSACTION                        YES
DBA                            MANAGE TABLESPACE                        YES
DBA                            ON COMMIT REFRESH                        YES
DBA                            ALTER ANY RULE SET                       YES
DBA                            ALTER ANY SEQUENCE                       YES
DBA                            ALTER ANY SNAPSHOT                       YES
DBA                            CREATE ANY CLUSTER                       YES
DBA                            CREATE ANY CONTEXT                       YES
DBA                            CREATE ANY LIBRARY                       YES
DBA                            CREATE ANY OUTLINE                       YES
DBA                            CREATE ANY SYNONYM                       YES
DBA                            CREATE ANY TRIGGER                       YES
DBA                            DROP ANY DIMENSION                       YES
DBA                            DROP ANY DIRECTORY                       YES
DBA                            DROP ANY INDEXTYPE                       YES
DBA                            DROP ANY PROCEDURE                       YES
DBA                            RESTRICTED SESSION                       YES
DBA                            ALTER ANY DIMENSION                      YES
DBA                            ALTER ANY INDEXTYPE                      YES
DBA                            ALTER ANY PROCEDURE                      YES
DBA                            ALTER RESOURCE COST                      YES
DBA                            CREATE ANY OPERATOR                      YES
DBA                            CREATE ANY RULE SET                      YES
DBA                            CREATE ANY SEQUENCE                      YES
DBA                            CREATE ANY SNAPSHOT                      YES
DBA                            DEBUG ANY PROCEDURE                      YES
DBA                            DROP PUBLIC SYNONYM                      YES
DBA                            EXECUTE ANY LIBRARY                      YES
DBA                            FLASHBACK ANY TABLE                      YES
DBA                            GRANT ANY PRIVILEGE                      YES
DBA                            SELECT ANY SEQUENCE                      YES
DBA                            CREATE ANY DIMENSION                     YES
DBA                            CREATE ANY DIRECTORY                     YES
DBA                            CREATE ANY INDEXTYPE                     YES
DBA                            CREATE ANY PROCEDURE                     YES
DBA                            CREATE DATABASE LINK                     YES
DBA                            EXECUTE ANY OPERATOR                     YES
DBA                            EXECUTE ANY RULE SET                     YES
DBA                            GLOBAL QUERY REWRITE                     YES
DBA                            CREATE PUBLIC SYNONYM                    YES
DBA                            DEBUG CONNECT SESSION                    YES
DBA                            DROP ROLLBACK SEGMENT                    YES
DBA                            EXECUTE ANY INDEXTYPE                    YES
DBA                            EXECUTE ANY PROCEDURE                    YES
DBA                            FORCE ANY TRANSACTION                    YES
DBA                            SELECT ANY DICTIONARY                    YES
DBA                            ALTER ROLLBACK SEGMENT                   YES
DBA                            CREATE ROLLBACK SEGMENT                  YES
DBA                            CREATE EVALUATION CONTEXT                YES
DBA                            DROP PUBLIC DATABASE LINK                YES
DBA                            GRANT ANY OBJECT PRIVILEGE               YES
DBA                            ADMINISTER DATABASE TRIGGER              YES
DBA                            ADMINISTER RESOURCE MANAGER              YES
DBA                            CREATE PUBLIC DATABASE LINK              YES
DBA                            DROP ANY EVALUATION CONTEXT              YES
DBA                            ALTER ANY EVALUATION CONTEXT             YES
DBA                            CREATE ANY EVALUATION CONTEXT            YES
DBA                            EXECUTE ANY EVALUATION CONTEXT           YES
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
OLAP_DBA                       ANALYZE ANY                              NO
OLAP_DBA                       DROP ANY VIEW                            NO
OLAP_DBA                       DROP ANY TABLE                           NO
OLAP_DBA                       LOCK ANY TABLE                           NO
OLAP_DBA                       ALTER ANY TABLE                          NO
OLAP_DBA                       CREATE ANY VIEW                          NO
OLAP_DBA                       CREATE ANY INDEX                         NO
OLAP_DBA                       CREATE ANY TABLE                         NO
OLAP_DBA                       SELECT ANY TABLE                         NO
OLAP_DBA                       DROP ANY DIMENSION                       NO
OLAP_DBA                       ALTER ANY DIMENSION                      NO
OLAP_DBA                       CREATE ANY DIMENSION                     NO
OLAP_DBA                       SELECT ANY DICTIONARY                    NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO
EXP_FULL_DATABASE              BACKUP ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY TYPE                         NO
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
EXP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER              NO
IMP_FULL_DATABASE              AUDIT ANY                                NO
IMP_FULL_DATABASE              DROP USER                                NO
IMP_FULL_DATABASE              ANALYZE ANY                              NO
IMP_FULL_DATABASE              BECOME USER                              NO
IMP_FULL_DATABASE              CREATE ROLE                              NO
IMP_FULL_DATABASE              CREATE USER                              NO
IMP_FULL_DATABASE              DROP PROFILE                             NO
IMP_FULL_DATABASE              DROP ANY ROLE                            NO
IMP_FULL_DATABASE              DROP ANY TYPE                            NO
IMP_FULL_DATABASE              DROP ANY VIEW                            NO
IMP_FULL_DATABASE              ALTER ANY TYPE                           NO
IMP_FULL_DATABASE              CREATE PROFILE                           NO
IMP_FULL_DATABASE              DROP ANY INDEX                           NO
IMP_FULL_DATABASE              DROP ANY TABLE                           NO
IMP_FULL_DATABASE              ALTER ANY TABLE                          NO
IMP_FULL_DATABASE              CREATE ANY TYPE                          NO
IMP_FULL_DATABASE              CREATE ANY VIEW                          NO
IMP_FULL_DATABASE              DROP TABLESPACE                          NO
IMP_FULL_DATABASE              CREATE ANY INDEX                         NO
IMP_FULL_DATABASE              CREATE ANY TABLE                         NO
IMP_FULL_DATABASE              DROP ANY CLUSTER                         NO
IMP_FULL_DATABASE              DROP ANY CONTEXT                         NO
IMP_FULL_DATABASE              DROP ANY LIBRARY                         NO
IMP_FULL_DATABASE              DROP ANY OUTLINE                         NO
IMP_FULL_DATABASE              DROP ANY SYNONYM                         NO
IMP_FULL_DATABASE              DROP ANY TRIGGER                         NO
IMP_FULL_DATABASE              EXECUTE ANY TYPE                         NO
IMP_FULL_DATABASE              INSERT ANY TABLE                         NO
IMP_FULL_DATABASE              MANAGE ANY QUEUE                         NO
IMP_FULL_DATABASE              SELECT ANY TABLE                         NO
IMP_FULL_DATABASE              UPDATE ANY TABLE                         NO
IMP_FULL_DATABASE              ALTER ANY TRIGGER                        NO
IMP_FULL_DATABASE              COMMENT ANY TABLE                        NO
IMP_FULL_DATABASE              CREATE TABLESPACE                        NO
IMP_FULL_DATABASE              DROP ANY OPERATOR                        NO
IMP_FULL_DATABASE              DROP ANY SEQUENCE                        NO
IMP_FULL_DATABASE              DROP ANY SNAPSHOT                        NO
IMP_FULL_DATABASE              CREATE ANY CLUSTER                       NO
IMP_FULL_DATABASE              CREATE ANY CONTEXT                       NO
IMP_FULL_DATABASE              CREATE ANY LIBRARY                       NO
IMP_FULL_DATABASE              CREATE ANY SYNONYM                       NO
IMP_FULL_DATABASE              CREATE ANY TRIGGER                       NO
IMP_FULL_DATABASE              DROP ANY DIMENSION                       NO
IMP_FULL_DATABASE              DROP ANY DIRECTORY                       NO
IMP_FULL_DATABASE              DROP ANY INDEXTYPE                       NO
IMP_FULL_DATABASE              DROP ANY PROCEDURE                       NO
IMP_FULL_DATABASE              ALTER ANY PROCEDURE                      NO
IMP_FULL_DATABASE              CREATE ANY OPERATOR                      NO
IMP_FULL_DATABASE              CREATE ANY SEQUENCE                      NO
IMP_FULL_DATABASE              CREATE ANY SNAPSHOT                      NO
IMP_FULL_DATABASE              DROP PUBLIC SYNONYM                      NO
IMP_FULL_DATABASE              CREATE ANY DIMENSION                     NO
IMP_FULL_DATABASE              CREATE ANY DIRECTORY                     NO
IMP_FULL_DATABASE              CREATE ANY INDEXTYPE                     NO
IMP_FULL_DATABASE              CREATE ANY PROCEDURE                     NO
IMP_FULL_DATABASE              CREATE DATABASE LINK                     NO
IMP_FULL_DATABASE              GLOBAL QUERY REWRITE                     NO
IMP_FULL_DATABASE              CREATE PUBLIC SYNONYM                    NO
IMP_FULL_DATABASE              DROP ROLLBACK SEGMENT                    NO
IMP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
IMP_FULL_DATABASE              CREATE ROLLBACK SEGMENT                  NO
IMP_FULL_DATABASE              DROP PUBLIC DATABASE LINK                NO
IMP_FULL_DATABASE              ADMINISTER DATABASE TRIGGER              NO
IMP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER              NO
IMP_FULL_DATABASE              CREATE PUBLIC DATABASE LINK              NO

238 rows selected.

scott@ORA92> select * from role_role_privs where role in
  2  (select role from user_role_privs where username = 'SCOTT')
  3  /

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
DBA                            OLAP_DBA                       NO
DBA                            XDBADMIN                       NO
DBA                            PLUSTRACE                      YES
DBA                            JAVA_ADMIN                     NO
DBA                            JAVA_DEPLOY                    NO
DBA                            WM_ADMIN_ROLE                  NO
DBA                            EXP_FULL_DATABASE              NO
DBA                            IMP_FULL_DATABASE              NO
DBA                            DELETE_CATALOG_ROLE            YES
DBA                            SELECT_CATALOG_ROLE            YES
DBA                            EXECUTE_CATALOG_ROLE           YES
DBA                            GATHER_SYSTEM_STATISTICS       NO
OLAP_DBA                       SELECT_CATALOG_ROLE            NO
EXP_FULL_DATABASE              SELECT_CATALOG_ROLE            NO
EXP_FULL_DATABASE              EXECUTE_CATALOG_ROLE           NO
IMP_FULL_DATABASE              SELECT_CATALOG_ROLE            NO
IMP_FULL_DATABASE              EXECUTE_CATALOG_ROLE           NO
SELECT_CATALOG_ROLE            HS_ADMIN_ROLE                  NO
EXECUTE_CATALOG_ROLE           HS_ADMIN_ROLE                  NO

19 rows selected.
Re: View the priviledges of a user/account [message #172116 is a reply to message #16582] Mon, 15 May 2006 00:19 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
is there a view also for me to know if i have execute priviledges on a package? or stored procedures?
Re: View the priviledges of a user/account [message #172363 is a reply to message #172116] Tue, 16 May 2006 03:43 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
I think the below example will help you.

SQL>CONN / AS SYSDBA

SQL>GRANT EXECUTE ON DBMS_METADATA TO SCOTT;

SQL>CONN SCOTT/TIGER

SQL>SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;


The above sql will list all the object grants . Also includes packages procedures ).

Regards
Salih Km

Re: View the priviledges of a user/account [message #172373 is a reply to message #16582] Tue, 16 May 2006 04:20 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, i'm using a dba user on a test db, but im getting an error sir,


frgt_uat@foscopy> /
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','FRGT_UAT') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
LPX-00210: expected '<' instead of 'n'
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3296
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1339
ORA-06512: at line 1


frgt_uat@foscopy> 


thank you so much =)
Re: View the priviledges of a user/account [message #172376 is a reply to message #172363] Tue, 16 May 2006 04:25 Go to previous messageGo to next message
harman_deep
Messages: 8
Registered: August 2005
Location: Amritsar
Junior Member

I think more easy way be like this:

For ur own session to see privileges>>
Select * from session_privs

For ur own session to see roles>>
Select * from session_roles

ALso u can refer for roles
select * from dba_roles

For SYSTEM privileges like UNLIMITED TABLESPACE
select * from dba_sys_privs where grantee=<grantee_name>

For TABLE(User objects) privileges like UNLIMITED TABLESPACE
select * from dba_tab_privs where grantee=<grantee_name>


Re: View the priviledges of a user/account [message #172399 is a reply to message #172373] Tue, 16 May 2006 05:49 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
which version of db are you using?

Also check the DBMS_METADATA.GET_GRANTED_DDL is working in Scott Schema.


[Updated on: Tue, 16 May 2006 05:51]

Report message to a moderator

Re: View the priviledges of a user/account [message #172401 is a reply to message #172373] Tue, 16 May 2006 05:56 Go to previous messageGo to next message
kmsalih
Messages: 197
Registered: March 2006
Senior Member
Run the Script $ORACLE_HOME/rdbms/admin/dbmsmeta.sql once more
Re: View the priviledges of a user/account [message #172496 is a reply to message #16582] Tue, 16 May 2006 20:00 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
hmmm i dont have access to the database, im just connecting from sqlplus, i'll try it at home, whewww...i'm overwhelmed by so many packages to learn, plsql is already a big language, then i also need to learn about database...wheewww will i ever get to learn all of that.
Previous Topic: Can a Schema Owner's id and objects be audited?
Next Topic: Patch and version numbers
Goto Forum:
  


Current Time: Thu Mar 28 13:00:34 CDT 2024