Tag Info

Hot answers tagged

17

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


12

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


9

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


8

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


8

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];


7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


7

Yes, a role is a collection of system and/or object privileges. It simplifies privilege management by allowing you to manage bundles of privileges.


7

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately. Table GroupMemberRoles GroupID FK UserID FK RoleID FK (PK on all three, with perhaps other constraints) Your queries (say, to find the admins of a certain group) should be of the ...


6

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


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


5

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


5

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


5

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


5

Try using the DDL trigger FOR DROP_TABLE: CREATE TRIGGER NO_DROP_TABLE ON DATABASE FOR DROP_TABLE AS PRINT 'Dropping tables are not allowed' ROLLBACK


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

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


5

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


4

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


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

Yes it would be a security risk. db_datawriter gives the account access to all tables in the database. This means that anyone getting control of the web user can delete all content. A better solution would be to create an new role in the database. Assign the entity framework user to this role and only grant the bare minimum privileges needed to run the app ...


4

The column 'is_fixed_role' on sys.database_principals is your answer, check the MS document: If 1, this row represents an entry for one of the fixed database roles: db_owner, db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter. Just use SELECT * FROM ...


3

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault. Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that ...


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

Use a database role. Database roles are database specific (obviously) so you can't create a role that grant's permissions to multiple databases at once. However within the database you create a role either through the GUI or using the command CREATE ROLE <rolename>. Once it is created you can then grant the role permissions just like you would a ...


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

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

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

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.


3

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



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