Tag Info

Hot answers tagged

8

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


6

GRANTing ALL permissions for public to the database is mostly redundant (as public has connect, temporary by default, so you'd only be adding CREATE which you probably don't want to do). You probably expected a GRANT ALL on the database to result in a recursive GRANT ALL to contained schemas and tables. GRANT is not recursive, so this doesn't happen; a GRANT ...


5

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining". So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked. If the owners are different (and note that it's the owner of the schema that matters) then rights ...


5

From the CREATE ROLE documentation: Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only ...


5

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a ...


5

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


4

Granting permissions on the schema (e.g. dbo) will cascade to all the objects in that schema. For individual exceptions you can just list those explicitly: GRANT SELECT ON SCHEMA::dbo TO [role]; GO GRANT INSERT, UPDATE --, DELETE ON dbo.table_they_can_write_to TO [role]; DENY SELECT ON dbo.table_they_cannot_read TO [role];


4

It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup. http://www.sqlbackuprestore.com/backupandrestorerights.htm


4

You want to manage your users, roles and user to role mappings in AD? I think Enterprise User Security is what you want, see http://docs.oracle.com/cd/E11882_01/network.112/e10744/concepts.htm#autoId10 Basically you'll need Oracle Enterprise Edition, and, after you've configured your database to use your directory (see ...


4

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the ALTER SESSION SET_CURRENT_SCHEMA=vwowner Roles are transient. You can do a SET ROLE NONE to ...


4

The simple explanation is that the software was not designed to allow that. As Gary pointed out, since roles were designed to be able to be turned on and off the view could be valid for some sessions while it isn’t for others. However, the system could be designed to allow roles to work. What we need is a persistent role or a device similar to a role ...


4

It looks like you need something like this: GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE contacts TO "user"; By the way, I hope "user" is not an actual user name you chose. Not only does it fail to convey much about the semantics of the role, but you will need to quote it everywhere, which can be something of a bother and lead to confusion when there is ...


3

If you need to prevent to drop the table by some user, try this: DENY DELETE ON OBJECT::dbo.table_to_deny TO restricted_user; http://msdn.microsoft.com/en-us/library/ms173724.aspx


3

Never a simple answer... For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining" Personally, I don't ...


3

From the manual: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510 The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather ...


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and ...


3

to do it correctly is not very simple, although once setup, your done. Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation. Any ...


3

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install. You need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser. There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit ...


2

Justin Cave is correct.+1 If you would like to learn more about roles you can get a good overview from the Concepts Guide. The Security Guide has more in depth information including limitations such as roles not being enabled in Definer rights methods.


2

The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even granting an additional role, unless specifically told to by Oracle. The first message you got says that import isn't supported when logged in with the SYSDBA role. ...


2

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want http://sqlblog.com/blogs/adam_machanic/archive/2006/10/19/replacing-xp-execresultset-in-sql-server-2005.aspx


2

According to Microsoft (and you'd hope they'd know), as long as you've granted permissions to the SP then the underlying permissions will be sorted via permission chaining - http://msdn.microsoft.com/en-us/library/bb669058.aspx Should mean that while your users can read from any table in the DB, they'd only be able to write to them via stored procs they'd ...


2

When you execute PL/SQL in a package/procedure/trigger, Oracle disables roles (this is not the case for anonymous blocks). As a result, you have to grant permissions directly, rather than through roles. Jonathan Lewis explains this well here, plus it's covered in the documentation here. Invoker/definers rights are documented here.


2

You can get the role configuration by querying pg_catalog.pg_shadow or pg_catalog.pg_user, as the following: --Query pg_shadow francs=# select usename,useconfig from pg_shadow ; usename | useconfig --------------+------------------------------- postgres | user_a | user_b | skytf_select | ...


2

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the ...


2

The roles in Oracle Database have no owner as well as directories have no owner. But whenever the new role is created (by user with SYSDBA role or CREATE ANY ROLE prvilege) it is automatically assigned to the user who created it. You can see what roles are assigned to which users querying DBA_ROLE_PRIVS view, or USER_ROLE_PRIVS if you want to know what roles ...


2

You can query the system catalog with a recursive query, in particular pg_auth_members: WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = 'maxwell' UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte; BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to ...


2

Short version: SELECT a.oid FROM pg_authid a WHERE pg_has_role('maxwell', a.oid, 'member'); Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships. The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role ...


2

If you have common attributes across non-exclusive tables, you should consider normalizing your tables. It appears that Client, Employee, Manager, and Developer are all sub-types of the Person entity. As such any attributes common to all types should be in the person entity, and attributes for the other entities should depend only on that sub-type. I ...


1

You need to use the fully-qualified name of the table, as Alice does not own the table. For example, if the table is owned by TABLE_1_OWNER: SELECT * FROM TABLE_1_OWNER.TABLE_1; `Alice can also create a synonym in her own schema that points to the table, thus removing the need to fully qualify the name: CREATE SYNONYM TABLE_1 FOR TABLE_1_OWNER.TABLE_1; ...



Only top voted, non community-wiki answers of a minimum length are eligible